虽然我尚未通过使用存储过程解决此问题，但我们已决定跳过存储过程，直接执行普通的SQL语句。
请参阅下面的扩展表方案
编辑2：更新索引（不再使用actieGroep）
NB. SQL Server 2005 Enterprise 9.00.4035.00
NB2. 看起来与http://www.sqlservercentral.com/Forums/Topic781451-338-1.aspx有关
我在一个表上有两个索引：
- statistiekId上的聚集PK索引
- foreignId上的非聚集索引
我有以下代码：
DECLARE @fid BIGINT
SET @fid = 873926
SELECT foreignId
FROM STAT_Statistieken
WHERE foreignId = @fid
这段代码执行的很好，它指向了正确的索引，且它所做的只是扫描该索引。
现在我正在创建一个存储过程：
ALTER PROCEDURE MyProcedure (@fid BIGINT)
AS BEGIN
SELECT foreignId
FROM STAT_Statistieken
WHERE foreignId = @fid
END
运行该事物：
EXEC MyProcedure @fid = 873926
现在它正在对我的主键索引运行聚集索引扫描！到底发生了什么？
因此，我将SP更改为
SELECT foreignId
FROM STAT_Statistieken
WITH (INDEX(IX_STAT_Statistieken_2))
WHERE foreignId = @fid
现在它会给出以下错误信息：查询处理器无法生成查询计划，因为此查询中定义了提示。请重新提交查询，不要指定任何提示并且不要使用SET FORCEPLAN。当直接执行此函数时，相同的函数应该正常运行。
额外信息：可以复现此行为的完整方案（注释中的英文名称）
表
CREATE TABLE [dbo].[STAT_Statistieken](
[statistiekId] [bigint] IDENTITY(1,1) NOT NULL,
[foreignId] [bigint] NOT NULL,
[datum] [datetime] NOT NULL, --date
[websiteId] [int] NOT NULL,
[actieId] [int] NOT NULL, --actionId
[objectSoortId] [int] NOT NULL, --kindOfObjectId
[aantal] [bigint] NOT NULL, --count
[secondaryId] [int] NOT NULL DEFAULT ((0)),
[dagnummer] AS (datediff(day,CONVERT([datetime],'2009-01-01 00:00:00.000',(121)),[datum])) PERSISTED, --daynumber
[actieGroep] AS (substring(CONVERT([varchar](4),[actieId],0),(1),(1))) PERSISTED,
CONSTRAINT [STAT_Statistieken_PK] PRIMARY KEY CLUSTERED --actionGroup
(
[statistiekId] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
索引
CREATE NONCLUSTERED INDEX [IX_STAT_Statistieken_foreignId_dagnummer_actieId_secondaryId] ON [dbo].[STAT_Statistieken]
(
[foreignId] ASC,
[dagnummer] ASC,
[actieId] ASC,
[secondaryId] ASC
)WITH (PAD_INDEX = ON, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 80, ONLINE = OFF) ON [PRIMARY]
执行
SET NOCOUNT ON;
DECLARE @maand INT, @jaar INT, @foreignId BIGINT
SET @maand = 9
SET @jaar = 2009
SET @foreignId = 828319
DECLARE @startDate datetime, @endDate datetime
SET @startDate = DATEADD(month, -1, CONVERT(datetime,CAST(@maand AS varchar(3))+'-01-'+CAST(@jaar AS varchar(5))))
SET @endDate = DATEADD(month, 1, CONVERT(datetime,CAST(@maand AS varchar(3))+'-01-'+CAST(@jaar AS varchar(5))))
DECLARE @firstDayDezeMaand datetime
SET @firstDayDezeMaand = CONVERT(datetime, CAST(@jaar AS VARCHAR(4)) + '/' + CAST(@maand AS VARCHAR(2)) + '/1')
DECLARE @daynumberFirst int
set @daynumberFirst = DATEDIFF(day, '2009/01/01', @firstDayDezeMaand)
DECLARE @startDiff int
SET @startDiff = DATEDIFF(day, '2009/01/01', @startDate)
DECLARE @endDiff int
SET @endDiff = DATEDIFF(day, '2009/01/01', @endDate)
SELECT @foreignId AS foreignId,
SUM(CASE WHEN dagnummer >= @daynumberFirst THEN (CASE WHEN actieId BETWEEN 100 AND 199 THEN aantal ELSE 0 END) ELSE 0 END) as aantalGevonden,
SUM(CASE WHEN dagnummer >= @daynumberFirst THEN (CASE WHEN actieId BETWEEN 200 AND 299 THEN aantal ELSE 0 END) ELSE 0 END) as aantalBekeken,
SUM(CASE WHEN dagnummer >= @daynumberFirst THEN (CASE WHEN actieId BETWEEN 300 AND 399 THEN aantal ELSE 0 END) ELSE 0 END) as aantalContact,
SUM(CASE WHEN dagnummer < @daynumberFirst THEN (CASE WHEN actieId BETWEEN 100 AND 199 THEN aantal ELSE 0 END) ELSE 0 END) as aantalGevondenVorige,
SUM(CASE WHEN dagnummer < @daynumberFirst THEN (CASE WHEN actieId BETWEEN 200 AND 299 THEN aantal ELSE 0 END) ELSE 0 END) as aantalBekekenVorige,
SUM(CASE WHEN dagnummer < @daynumberFirst THEN (CASE WHEN actieId BETWEEN 300 AND 399 THEN aantal ELSE 0 END) ELSE 0 END) as aantalContactVorige
FROM STAT_Statistieken
WHERE
dagnummer >= @startDiff
AND dagnummer < @endDiff
AND foreignId = @foreignId
OPTION(OPTIMIZE FOR (@foreignId = 837334, @startDiff = 200, @endDiff = 300))
DBCC统计信息
Name | Updated | Rows | Rows smpl | Steps | Density | Avg. key | String index
IX_STAT_Statistieken_foreignId_dagnummer_actieId_secondaryId Oct 6 2009 3:46PM 1245058 1245058 92 0,2492834 28 NO
All Density | Avg. Length | Columns
3,227035E-06 8 foreignId
2,905271E-06 12 foreignId, dagnummer
2,623274E-06 16 foreignId, dagnummer, actieId
2,623205E-06 20 foreignId, dagnummer, actieId, secondaryId
8,031755E-07 28 foreignId, dagnummer, actieId, secondaryId, statistiekId
RANGE HI | RANGE_ROWS | EQ_ROWS | DISTINCT_RANGE_ROWS | AVG_RANGE ROWS
-1 0 2 0 1
1356 3563 38 1297 2,747109
8455 14300 29 6761 2,115072
并且索引的使用如执行计划所示。当我将其封装在一个带有这些参数的过程中：
@foreignId bigint,
@maand int, --month
@jaar int --year
使用_SP_TEMP @foreignId = 873924, @maand = 9, @jaar = 2009运行它。
它执行了聚集索引扫描！