Just How Does SQL Server Define A Unique Index

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

  • 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)

  • 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

  • Thanks Jeff

    i decided against changing it anywhere

    Jim

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

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

  • 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

  • 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

  • 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

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

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