sql query treating a int as a string - issues?

Question

If i do a query like this

SELECT * from Foo where Bar = '42'

and Bar is a int column. Will that string value be optimized to 42 in the db engine? Will it have some kind of impact if i leave it as it is instead of changing it to:

Select * from Foo where Bar = 42  

This is done on a SQL Compact database if that makes a difference.
I know its not the correct way to do it but it's a big pain going though all code looking at every query and DB schema to see if the column is a int type or not.

Answer 1

SQL Server automatically convert it to INT that because INT has higher precedence than VARCHAR.

You should also be aware of the impact that implicit conversions can have on a query’s performance. To demonstrate what I mean, I’ve created and populated the following table in the AdventureWorks2008 database:

USE AdventureWorks2008;

IF OBJECT_ID ('ProductInfo', 'U') IS NOT NULL
DROP TABLE ProductInfo;

CREATE TABLE ProductInfo
(
  ProductID NVARCHAR(10) NOT NULL PRIMARY KEY,
  ProductName NVARCHAR(50) NOT NULL
);

INSERT INTO ProductInfo
SELECT ProductID, Name
FROM Production.Product;

As you can see, the table includes a primary key configured with the NVARCHAR data type. Because the ProductID column is the primary key, it will automatically be configured with a clustered index. Next, I set the statistics IO to on so I can view information about disk activity:

SET STATISTICS IO ON;

Then I run the following SELECT statement to retrieve product information for product 350:

SELECT ProductID, ProductName
FROM ProductInfo
WHERE ProductID = 350;

Because statistics IO is turned on, my results include the following information:

Table 'ProductInfo'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Two important items to notice is that the query performed a scan and that it took six logical reads to retrieve the data. Because my WHERE clause specified a value in the primary key column as part of the search condition, I would have expected an index seek to be performed, rather than I scan. As the figure below confirms, the database engine performed a scan, rather than a seek. Figure below shows the details of that scan (accessed by hovering the mouse over the scan icon).

Notice that in the Predicate section, the CONVERT_IMPLICIT function is being used to convert the values in the ProductID column in order to compare them to the value of 350 (represented by @1) I passed into the WHERE clause. The reason that the data is being implicitly converted is because I passed the 350 in as an integer value, not a string value, so SQL Server is converting all the ProductID values to integers in order to perform the comparisons.

Because there are relatively few rows in the ProductInfo table, performance is not much of a consideration in this instance. But if your table contains millions of rows, you’re talking about a serious hit on performance. The way to get around this, of course, is to pass in the 350 argument as a string, as I’ve done in the following example:

SELECT ProductID, ProductName
FROM ProductInfo
WHERE ProductID = '350';

Once again, the statement returns the product information and the statistics IO data, as shown in the following results:

Now the index is being properly used to locate the record. And if you refer to Figure below, you’ll see that the values in the ProductID column are no longer being implicitly converted before being compared to the 350 specified in the search condition.

As this example demonstrates, you need to be aware of how performance can be affected by implicit conversions, just like you need to be aware of any types of implicit conversions being conducted by the database engine. For that reason, you’ll often want to explicitly convert your data so you can control the impact of that conversion.

You can read more about Data Conversion in SQL Server.

Answer 2

If you look into the MSDN chart which tells about the implicit conversion you will find that string is implicitly converted into int.