c# - 将 int 视为字符串的 sql 查询 - 问题？

Question

如果我这样查询

SELECT * from Foo where Bar = '42'

并且 Bar 是一个 int 列。该字符串值会在数据库引擎中优化为 42 吗？如果我保持原样而不是将其更改为:

Select * from Foo where Bar = 42  

如果有所不同，这是在 SQL Compact 数据库上完成的。
我知道这不是正确的方法，但是通过查看每个查询和数据库模式的所有代码来查看该列是否为 int 类型是一件很痛苦的事情。

Answer 1

SQL Server 自动将其转换为 INT，因为 INT 有 higher precedence比 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.

您可以阅读更多关于 Data Conversion in SQL Server 的信息.