Databases
StackOverflow2013
Postsdbo
POBehavior of unique index, varchar column and (blank) spaces
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POBehavior of unique index, varchar column and (blank) spaces
    primarykey
    data
    text
    <p>I'm using Microsoft SQL Server 2008 R2 (with latest service pack/patches) and the database collation is SQL_Latin1_General_CP1_CI_AS.</p> <p>The following code:</p> <pre><code>SET ANSI_PADDING ON; GO CREATE TABLE Test ( Code VARCHAR(16) NULL ); CREATE UNIQUE INDEX UniqueIndex ON Test(Code); INSERT INTO Test VALUES ('sample'); INSERT INTO Test VALUES ('sample '); SELECT '&gt;' + Code + '&lt;' FROM Test WHERE Code = 'sample '; GO </code></pre> <p>produces the following results:</p> <blockquote> <p>(1 row(s) affected)</p> <p>Msg 2601, Level 14, State 1, Line 8</p> <p>Cannot insert duplicate key row in object 'dbo.Test' with unique index 'UniqueIndex'. The duplicate key value is (sample ).</p> <p>The statement has been terminated.</p> <p>&#8208;&#8208;&#8208;&#8208;&#8208;&#8208;&#8208;&#8208;&#8208;&#8208;&#8208;&#8208;</p> <p>&gt;sample&lt;</p> <p>(1 row(s) affected)</p> </blockquote> <p>My questions are:</p> <ol> <li>I assume the index cannot store trailing spaces. Can anyone point me to official documentation that specifies/defines this behavior?</li> <li>Is there a setting to change this behavior, that is, make it recognize 'sample' and 'sample ' as two different values (which they are, by the way) so both can be in the index.</li> <li>Why on Earth is the SELECT returning a row? SQL Server must be doing something really funny/clever with the spaces in the WHERE clause because if I remove the uniqueness in the index, both INSERTs will run OK and the SELECT will return two rows!</li> </ol> <p>Any help/pointer in the right direction would be appreciated. Thanks.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USEric
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POBehavior of unique index, varchar column and (blank) spaces
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POBehavior of unique index, varchar column and (blank) spaces
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload