Trailing space in primary key

Question

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.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Paul Mu SSCertifiable Points: 5547 More actions · Answer 1

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 · Answer 2

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: 323545 More actions · Answer 3

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!