title: Allow trailing white space differences in SQL Server unique constraint
body: <p>I have a table with a column that has a unique constraint on it. My data has meaningful trailing white space, so <code>'foo'</code> and <code>'foo '</code> are meaningfully different. However, if I try to add a row with each value it violates the unique constraint on the column.</p>
  
  <p>If the column is a primary key, I get this error:</p>
  
  <blockquote>
    <p>Violation of PRIMARY KEY constraint 'PK_Foo'. Cannot insert duplicate key in object 'dbo.Foo'. The duplicate key value is (foo ).</p>
  </blockquote>
  
  <p>If the column has a unique constraint, I get this error:</p>
  
  <blockquote>
    <p>Cannot insert duplicate key row in object 'dbo.Foo' with unique index 'IX_Foo'. The duplicate key value is (foo ).</p>
  </blockquote>
  
  <p>Below is the script that I would like to pass.</p>
  
  <pre><code>SET ANSI_PADDING ON  -- this has no effect, I've included it to preempt the suggestion
  GO
  
  CREATE TABLE [dbo].[Foo](
      [Bar] [varchar](50) NOT NULL,
      [Baz] [varchar](50) NOT NULL,
   CONSTRAINT [PK_Foo] PRIMARY KEY CLUSTERED 
  (
      [Bar] ASC
  )
  ) ON [PRIMARY]
  
  GO
  
  CREATE UNIQUE NONCLUSTERED INDEX [IX_Foo] ON [dbo].[Foo]
  (
      [Baz] ASC
  )
  GO
  
  INSERT INTO [dbo].[Foo] (Bar, Baz) VALUES ('foo', '1')
  INSERT INTO [dbo].[Foo] (Bar, Baz) VALUES ('foo ', '2')
  
  INSERT INTO [dbo].[Foo] (Bar, Baz) VALUES ('1', 'foo')
  INSERT INTO [dbo].[Foo] (Bar, Baz) VALUES ('2', 'foo ')
  
  SET ANSI_PADDING OFF
  </code></pre>