UGTS Document #50 - Last Modified: 8/29/2015 3:23 PM
SQL Server Unexpected Behaviors

SQL Server has a number of odd behaviors that can jump up and bite you if you are not aware of them.  Here they are, listed in order of decreasing severity and frequency by which people have been known to stumble over them:
  • Trailing spaces on strings are ignored in string comparisons - 'a' and 'a ' compare to be equal in SQL server.  This has a number of far-reaching consequences.  If you try to add a unique index on a column that has values that are equal except for trailing space, you won't be able to do it, because the SQL server considers the values to be equal.  If you already have a unique index, you won't be able to add a duplicate value which is only different by trailing space.  LIKE comparisons between two strings that are different only in trailing space will match if the trailing space is on the left side of the LIKE expression, but not on the right side.

    SQL server does this to conform to the ANSI/ISO SQL-92 requirements for comparing strings with different lengths.  The behavior with the LIKE operator is an exception to this rule because LIKE is considered to be a pattern matching operation rather than a simple string comparison as mandated by SQL-92
  • The datetime data type is not truly accurate to the millisecond - even though datatime uses 8 bytes, the storage mechanism for date type is very odd, and datetime only stores time to the nearest 0/3/7/10 millisecond boundary.  Microsoft recommends using datetime2 for all new definitions since it uses the same amount of space (8 bytes), but has a greater date range (year 0 - 9999) and accuracy to 100 nanoseconds.
  • Sql_variant values of differing types and collations have odd sorting rules - with an sql_variant column or value, it is possible to compare two different data types and get odd results.  This unique behavior for sql_variant is because the sql_variant data type cannot be implicitly converted to any other data type by SQL server before comparison.  When you compare simple data types such as int and float, this is not an issue, because with simple types, implicit conversion is allowed.  The rules for comparing two sql_variant types of differing base data types or collations are highly complex and unintuitive.  Avoid comparison operations altogether for the sql_variant type if you can.
  • Stored procedures starting with 'sp_' in the master database always override any user defined procedure of the same name - for example, if you create a stored procedure with the name 'sp_testproc' in master, and a local procedure sp_testproc in the current database, the copy in master will be invoked even if you fully qualify the name of the procedure to use with the database name and schema.  NOTE - THIS IS DOCUMENTED TO BE THE BEHAVIOR FOR SQL 2000 and 2005, BUT TESTS REVEALED THAT THIS WAS NOT THE CASE ON SQL 2008 R2.  It is recommended by Microsoft that you do not create your own stored procedures starting with 'sp_' for this reason.