When you build a database in SQL Server, you need to pick a data type for each field in each table. Hopefully, a great database designer took the time to carefully understand business needs and what the field would be used for, and assigned the correct data type. When that doesn’t happen, you may run into problems down the road.
What is Implicit Conversion, and Why is it Bad For Me?
When SQL Server tries to join on or compare fields of different data types, if they are not the same data type, it will convert one to match the other. This is called implicit conversion.
Implicit conversion is not a desired action in SQL Server, and can lead to excessive CPU use. In some cases, converting from one type to another can cause a loss of precision. A chart showing all the data types and their conversions can be found at http://msdn.microsoft.com/en-us/library/ms187928.aspx.
The most common places you will see implicit conversions happening is in the FROM and WHERE clauses.
Implicit Conversion in the FROM Clause
Joining two tables on a field that has a different data type in each table will cause SQL Server to convert one type to the other.
Let’s look at an example, using two tables, ProductCategory and Product.
CREATE TABLE ProductCategory ( CategoryID INT Primary Key, CategoryName VARCHAR(50) ); CREATE TABLE Product ( ProductID INT Primary Key, ProductName VARCHAR(100), QtyInStock INT, CategoryID VARCHAR(10) ) ;
I’m going to insert data into the tables. Note that I am using the syntax to insert multiple rows into a table at one time, which is only available in SQL Server 2008 and higher.
INSERT INTO ProductCategory (CategoryID, CategoryName) VALUES (1, 'Ale'), (2, 'Lager'), (3, 'N/A'); INSERT INTO Product (ProductID, ProductName, QtyInStock, CategoryID) VALUES (1, 'Spotted Cow', 8966, '1'), (2, 'Fat Squirrel', 7643, '1'), (3, 'Moon Man', 339, '1'), (4, 'Two Women', 1224, '2'), (5, 'Home Town Blonde', 564, '2'), (6, 'Ginger Ale', 899, '3');
This query will join the two tables on the CategoryID field.
DECLARE @Category INT SELECT @Category = 2 SELECT PC.CategoryName, P.ProductID, P.ProductName FROM ProductCategory PC INNER JOIN Product P ON P.CategoryID = PC.CategoryID WHERE PC.CategoryID = @Category;
Reviewing the query execution plan shows CONVERT_IMPLICIT on Product.CategoryID.
Using a CAST in the FROM clause will remove this from the query plan.
SELECT PC.CategoryName, P.ProductID, P.ProductName FROM ProductCategory PC INNER JOIN Product P ON P.CategoryID = CAST(PC.CategoryID AS INT) WHERE PC.CategoryID = @Category;
Implicit Conversion in the WHERE Clause
In this query, my variable is an INT and I am comparing it to a VARCHAR(10).
DECLARE @Category INT SELECT @Category = 1 SELECT P.ProductName, P.QtyInStock FROM Product P WHERE P.CategoryID = @Category;
This also results in a CONVERT_IMPLICIT being performed.
Modifying the variable to be of the same data type as the field, a VARCHAR(10), resolves this problem.
DECLARE @Category VARCHAR(10) SELECT @Category = '1' SELECT P.ProductName, P.QtyInStock FROM Product P WHERE P.CategoryID = @Category;
Think Like the Optimizer
To get the best performance from your queries, you need to understand how the query optimizer works, and think like it. Preventing implicit conversion, and the resulting CPU usage, begins with proper database design. If you run into it after the database has been created, knowing how to spot it and work with it will result in a better-performing database.