December 19, 2002
Oracle v SQL Server, Part 4
My colleague has just found a trap for young players. By default SQL Server stores string values (in CHAR and VARCHAR columns) in a case preserving but insensitive format. See this weblog post for a more coherent description of that term.
What this means is that when you compare two string values in SQL Server they will always be the same regardless of capitalisation or any functions you apply to them. This is actually as a result of the default 'collation' that SQL Server is installed with (unless you custom install and pick another one).
What this means is that if your WHERE clause contains;
charcol1 = charcol2
It will return true for any of the following possible values of the two columns;
"Andy Todd", "andy todd", "ANDY TODD" "ANdy TOdd", etc.
This is even if you use the available string functions lower or upper. For more information I dug up references at experts exchange and IT World.
Posted by Andy Todd at December 19, 2002 05:54 PM