November 2, 2010 at 6:16 pm
Instead of doing a LEN on your data, try doing a DATALENGTH. You can get an accurate count of bytes used that way.
November 3, 2010 at 5:56 pm
JC-3113 (10/13/2010)
Hi luckus_git 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
Change is inevitable... Change for the better is not.
November 4, 2010 at 8:41 am
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
November 4, 2010 at 8:43 am
Thanks Jeff
i decided against changing it anywhere
Jim
November 4, 2010 at 8:45 am
Here's a link to a great article about what happens with blanks:
November 4, 2010 at 8:56 am
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
November 4, 2010 at 8:57 am
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
November 4, 2010 at 12:56 pm
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