Just How Does SQL Server Define A Unique Index

Question

Just How Does SQL Server Define A Unique Index

Viewing 8 posts - 31 through 37 (of 37 total)

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

TheChrisMan Mr or Mrs. 500 Points: 511 More actions · Answer 1

Instead of doing a LEN on your data, try doing a DATALENGTH. You can get an accurate count of bytes used that way.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

JC-3113 (10/13/2010)
Hi luckus_g
it is not an issue to change it
i am in just testing creating tables and indexes and importing data right now
so i can start over after i make the change
Thanks
Jim

I haven't read this whole thread but my recommendation would be to NOT make either a Server or a whole DATABASE case sensitive. Only make the column that needs it case sensitive when you create the table. Even then, I wouldn't make anything case sensitive because you look for a name it might be firstname/lastname, Firstname/LastName, FIRSTNAME/LASTNAME or whatever. Look up the COLLATE clause in Books Online for how to do a case sensitive lookup without any of the disadvantages of having a case sensitive object in your database.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 3

Thanks

that was what i would expect and adds to my position that i do not understand why sql server strips out the trailing spaces when it tries to index. It should not be stripping out any characters, it seems to me

SELECT id, myText, '***' + mytext + '***' "myText actual", datalength ( myText) "myText datalength"

FROM TrailingSpace;

id myText myText actual myText datalength

----------- ------ ------------- -----------------

1 abc ***abc*** 3

2 abc ***abc *** 4

3 abc ***abc *** 5

4 abc ***abc *** 6

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 4

Thanks Jeff

i decided against changing it anywhere

Jim

TheChrisMan Mr or Mrs. 500 Points: 511 More actions · Answer 5

Here's a link to a great article about what happens with blanks:

http://www.sqlservercentral.com/articles/T-SQL/63953/

Rich Mechaber SSChampion Points: 11130 More actions · Answer 6

that was what i would expect and adds to my position that i do not understand why sql server strips out the trailing spaces when it tries to index. It should not be stripping out any characters, it seems to me

SQL Server is not stripping anything out.

Maybe this is semantics, but the ANSI standard -- which SQL Server follows -- is to pad strings with spaces to make the strings of identical length for comparison purposes.

From http://support.microsoft.com/kb/316626:

SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2, <Comparison Predicate>, General rules #3) on how to compare strings with spaces. The ANSI standard requires padding for the character strings used in comparisons so that their lengths match before comparing them.

Personally, I would TRIM() the incoming data from Oracle before dumping it into SQL Server. Perhaps a bigger question you have is: should user-entered data that could vary only by a trailing space be expected to be unique? Is a UNIQUE constraint on that column commensurate with the reality your DB models?

Good luck,

Rich

Mike John SSCertifiable Points: 7216 More actions · Answer 7

I think the critical point is that SQL Server is it is not stripping out anything.

The rules for string comparison in varchar are very precisely defined, if one is shorter than the other pad the shorter with blanks to match the longer.

Thus when comparing two values that are the same apart from a trailing blank the shorter one will get a space added and therefore be treated as the same value as the longer one.

When building the index the same comparison will be done, and if the index is defined as unique will then cause an error as duplicates will not be allowed.

Ditto on the previous comment - regarding the benefit of removing the trailing blanks.

Mike John

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 8

Thanks Everyone for your input

I read the article and it specifically says that when creating an index the value is trimmed

I used the word stripped. SQL Server may not be trimming the data but it is trimming it when trying to create the index, thus the dupicate key error

That is all I was trying to figure out

As far as the data coming from oracle. It is apparent that the data when entered was fatfingured

and is in the database. Oracle 's VARCHAR2 allows that type of data to be entreed unfortunately, so I will have to clean it up before I export it into SQL Server. The schema and column definitions come from the vendor so out of my control

Thanks

Jim