Avoid Unnecessary Data Type Conversions

SQL Server adds implicit data type conversions when types don’t match. This can have unintended results both on query results, but also on the query plan. It is important to use literal constants that match in type column they are being compared with wherever possible. A common mistake in writing T-SQL is to always use string literals for constants even when those constants are numbers, for example.

SELECT * FROM Sales

WHERE Quantity = ‘100’

In this example, ‘100’ is a character string while the column Quantity is an integer. The implicit conversion used follows strict precedence rules. Here the conversion will be on Quantity to character string, and not on the ‘100’ to an integer.

Comments

• Anonymous
  October 03, 2006
  PingBack from http://www.hedgate.net/articles/2006/01/12/stored-procedure-guidelines-avoid-magic-numbers

• Anonymous
  November 17, 2006
  Ian José, I think it is even worse when you do the oposite: SELECT Quantity FROM Sales WHERE Status = 1 Status being a string column. Unfortunatelly SQL Server will let you do this until some day someone adds a status code in a row that cannot be converted to a number! Add to this the fact that SqlCommand.ExecuteScalar sometimes fails to raise an error on this condition, and you have a bug that is very difficult to chase (I know because this have just happened in my company). My wish: that I could disable implicit conversions by using some phrase in the connection string. Kind of "Option Strict = True;" :)