Data type mismatch in predicates

This is another typical mistake that is sometimes hard to catch. It is very easy to mismatch data types in predicates. It could be in a stored procedure where the parameter is passed as one data type and then used in a query to filter data on a column of different data type. Another example is joining tables on columns with different data types, or simply using a predicate where data types are mismatched.

For example, we may have a Customers table where the last_name column is of type VARCHAR:

CREATE TABLE Customers (
customer_nbr INT NOT NULL PRIMARY KEY,
first_name VARCHAR(35) NOT NULL,
last_name VARCHAR(35) NOT NULL);

Then the following stored procedure is used to retrieve the customer information by customer last name:

CREATE PROCEDURE GetCustomerByLastName
@last_name NVARCHAR(35)
AS
SELECT first_name, last_name
FROM Customers
WHERE last_name = @last_name;

Notice here the parameter @last_name is of data type NVARCHAR. Although the code "works", SQL Server will have to perform implicit conversion of the last name column to NVARCHAR, because NVARCHAR is of higher data precedence. This can result in a performance penalty. The implicit conversion is visible in the query plan as CONVERT_IMPLICIT. Based on collation, and other factors, a data type mismatch may also preclude the use of an index seek. Use of the correct data type resolves the problem:

CREATE PROCEDURE GetCustomerByLastName
@last_name VARCHAR(35)
AS
SELECT first_name, last_name
FROM Customers
WHERE last_name = @last_name;

In many cases, this mistake is the result of splitting responsibilities on the team, and having one team member design the tables and another implement stored procedures or code. Another reason could be using different data sources to join data where the join columns have different data types in the source systems. The same advice applies not only to character data type mismatches, but also to mismatches between numeric data types (like INT and FLOAT), or the mixing of numeric and character data types.