Trailing space in primary key

• unknown

  SSC Veteran

  Points: 296

  More actions

  August 8, 2006 at 5:41 pm

  #92353

  How can you get trailing spaces to count in SQL Server 2000 when applying a unique index key to a varchar field.  I am trying to apply a primary key (unique index) to imported data so I can't change the values and trim the trailing spaces.  In this case the data is coming from Oracle which gives standing to trailing spaces when considering uniqueness.

  When I apply the unique index in SQL Server 2000 it gives a duplicate value error.  However, actually one of the values has a space behind it.  SQL Server is trimming or compressing the space when it looks at the value for uniqueness.  I need to turn that off somehow and get trailing spaces to count in determining uniqueness when creating primary key or unique indexes.

  Thanks.

   

• Paul Mu

  SSCertifiable

  Points: 5547

  More actions

  August 8, 2006 at 6:33 pm

  #653962

  Have a look at this article (http://support.microsoft.com/kb/231830/EN-US/), which seems to imply that there is no solution to the problem.

  In short, SQL Server is able to store data preserving any trailing blanks (with SET ANSI_PADDING ON), but it does not behave as it should with respect to trailing blanks when comparing data!

• unknown

  SSC Veteran

  Points: 296

  More actions

  August 9, 2006 at 9:19 am

  #654112

  Got it.  Thanks much.  I think the only solution is to enforce better at the point of entry into the Oracle database.

• Lowell

  SSC Guru

  Points: 323522

  More actions

  August 9, 2006 at 3:14 pm

  #654175

  wouldn't it be true that if you are using a CHAR field instead of a VARCHAR field, the string would be padded with spaces?

  create table #test(charid char(20) primary key, notes varchar(30) )

  insert into #test(charid,notes) values('1','no spaces')

  insert into #test(charid,notes) values(' 1','1 spaces')

  insert into #test(charid,notes) values('  1','2 spaces')

  insert into #test(charid,notes) values('   1','3 spaces')

  note that the results have training spaces that prevent a PK violation:

  insert into #test(charid,notes) values('1','no spaces')

  insert into #test(charid,notes) values('1 ','1 spaces') --violates PK

   

  1	3 spaces
  1	2 spaces
  1	1 spaces
  1	no spaces

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!