当具有常量的相同查询返回122K时，为什么使用参数的查询不返回任何行

I'm in SQL Server 2008 and I have been working on making some older code use parameters instead of building the queries using string concatenation. And I'm also working on making things work faster by doing things like adding indexes to tables.

我在SQL Server 2008中，我一直在努力使一些旧的代码使用参数，而不是使用字符串连接来构建查询。而且我还在通过为表添加索引等工作来提高工作效率。

The table I am working with has many columns (it's the heart of a star schema reporting database) and has more than 4M rows. The create for the table looks like:

我正在使用的表有很多列（它是星型模式报告数据库的核心），并且有超过4M行。表的创建如下：

CREATE TABLE [dbo].[rptTransaction](
    ...
    [Date] [nvarchar](10) NULL,
...
) ON [PRIMARY]

Yes, the date column is poorly named because it collides with a keyword. They are using string dates [remember it's a reporting database].

是的，日期列的命名很差，因为它与关键字冲突。他们正在使用字符串日期[记住它是一个报告数据库]。

When I execute the following code in MS SQL Server Management Studio:

当我在MS SQL Server Management Studio中执行以下代码时：

DECLARE @testDate NVARCHAR  = N'2012/03/01';

SELECT COUNT(*)
  FROM rptTransaction AS t 
 WHERE t.Date >= @testDate
       AND t.Date <= @testDate
OPTION (RECOMPILE);
GO

The results are as follows:

结果如下：

(No column name)
0

On the other hand when I execute the following code:

另一方面，当我执行以下代码时：

DECLARE @testDate NVARCHAR  = N'2012/03/01';

SELECT COUNT(*)
  FROM rptTransaction AS t 
 WHERE t.Date >= N'2012/03/01'
       AND t.Date <= N'2012/03/01'
OPTION (RECOMPILE);
GO

The result is the following:

结果如下：

(No column name)
124888

(I am using the OPTION (RECOMPILE) because otherwise the parametrized version does a full table scan, which takes a long time.)

（我正在使用OPTION（RECOMPILE），因为参数化版本会进行全表扫描，这需要很长时间。）

2 个解决方案

#1

Run this code:

运行此代码：

declare @testDate nvarchar = N'2012/03/01';
select @testDate ;

One my system, the output was just 2.

一个我的系统，输出只有2。

Try this instead:

试试这个：

declare @testDate nvarchar(10) = N'2012/03/01';
select @testDate ;

Much better:

好多了：

2012/03/01

2012/03/01

That's one of two unexpected parts of your query. The other is that you required your value be both >= and <=. In other words, the only way to satisfy the query is if the parameter matched your stored data exactly. 2 is never going to do that if the column is all valid dates.

这是查询的两个意外部分之一。另一个是你需要你的值> =和<=。换句话说，满足查询的唯一方法是参数是否与您存储的数据完全匹配。如果列是所有有效日期，则永远不会这样做。

#2

nvarchar by default uses a length 1.specify the length say 10 in this case

nvarchar默认使用长度1.在这种情况下，指定长度为10

#1