上一篇我们探讨了在静态语句中使用 WHERE Column=@Param OR @Param IS NULL的问题，有对OPTION(COMPILE)的评论，那这节我们来探讨OPTION(COMPILE)的问题。
探讨OPTION(COMPILE)问题
DECLARE
@AddressLine1 NVARCHAR(60) = NULL,
@AddressLine2 NVARCHAR(60) = NULL,
@City NVARCHAR(30) = NULL,
@PostalCode NVARCHAR(15) = NULL,
@StateProvinceID INT = NULL
SET @City = 'Bothell'
SET @PostalCode = '98011'
SET @StateProvinceID = 79
DECLARE @SQL NVARCHAR(MAX),@ColumnName VARCHAR(4000),@ParamDefinition NVARCHAR(500)
SET @ColumnName = 'a.AddressID, a.AddressLine1, a.AddressLine2, a.City, a.StateProvinceID, a.PostalCode, a.rowguid'
SET @SQL = 'SELECT ' + @ColumnName + ' FROM Person.Address AS a WHERE 1 = 1'
IF (@AddressLine1 IS NOT NULL)
SET @SQL = @SQL + ' AND a.AddressLine1 LIKE ''%'' + @AddressLine1 + ''%'''
IF (@AddressLine2 IS NOT NULL)
SET @SQL = @SQL + ' AND a.AddressLine2 LIKE ''%'' + @AddressLine2 + ''%'''
IF (@City IS NOT NULL)
SET @SQL = @SQL + ' AND a.City LIKE ''%'' + @City + ''%'''
IF (@PostalCode IS NOT NULL)
SET @SQL = @SQL + ' AND a.PostalCode LIKE ''%'' + @PostalCode + ''%'''
IF (@StateProvinceID IS NOT NULL)
SET @SQL = @SQL + ' AND a.StateProvinceID = @StateProvinceID'
SET @ParamDefinition = N'@AddressLine1 NVARCHAR(60),
@AddressLine2 NVARCHAR(60),
@City NVARCHAR(30),
@PostalCode NVARCHAR(15),
@StateProvinceID INT'
EXECUTE sp_executesql @SQL,@ParamDefinition,
@AddressLine1 = @AddressLine1,
@AddressLine2 = @AddressLine2,
@City = @City,
@PostalCode = @PostalCode,
@StateProvinceID = @StateProvinceID
GO
此时我们将外部变量StateProvinceID类型修改为SMALLINT，然后再来运行查询和缓存计划，此时会出现查询计划使用次数是为2，还是出现两条次数都为1呢？
此时我们再来将动态SQL中内部变量StateProvinceID类型修改为SMALLINT，此时会出现查询计划使用次数是为3，还是出现两条，次数分别为2和1呢？
由上可知，如果我们修改外部变量参数类型不会影响查询计划缓存即会达到重用目的，若修改动态SQL内部变量参数类型则不会重用查询计划缓存。
大多数情况下，我们可能不需要担心上述问题。但是，在某些情况下，假设从查询的执行到执行的参数变化很大，则会引起问题。
如果我们确定存储过程通常运行正常，但有时运行缓慢，则很可能会看到上述问题。在这种情况下，我们可以做的是改变存储过程，并添加WITH RECOMPILE选项。
讲完OPTION(COMPILE)，接下来我们讲讲如何创建高性能的存储过程。有些童鞋可能会创建如下存储过程。
CREATE PROC [dbo].[HighPerformanceExample]
(
@AddressLine1 NVARCHAR(60) = NULL,
@AddressLine2 NVARCHAR(60) = NULL,
@City NVARCHAR(30) = NULL,
@PostalCode NVARCHAR(15) = NULL,
@StateProvinceID SMALLINT = NULL
)
AS
SET NOCOUNT ON
SELECT a.AddressID, a.AddressLine1, a.AddressLine2, a.City, a.StateProvinceID, a.PostalCode, a.rowguid
FROM Person.Address AS a
WHERE (a.AddressLine1 = @AddressLine1 OR @AddressLine1 IS NULL) AND
(a.AddressLine2 = @AddressLine2 OR @AddressLine2 IS NULL) AND
(a.City = @City OR @City IS NULL) AND
(a.PostalCode = @PostalCode OR @PostalCode IS NULL) AND
(a.StateProvinceID = @StateProvinceID OR @StateProvinceID IS NULL)
--或者
SELECT a.AddressID, a.AddressLine1, a.AddressLine2, a.City, a.StateProvinceID, a.PostalCode, a.rowguid
FROM Person.Address AS a
WHERE a.AddressLine1 = COALESCE(@AddressLine1, a.AddressLine1) AND
a.AddressLine2 = COALESCE(@AddressLine2, a.AddressLine2) AND
a.City = COALESCE(@City, a.City) AND
a.PostalCode = COALESCE(@PostalCode, a.PostalCode) AND
a.StateProvinceID = COALESCE(@StateProvinceID, a.StateProvinceID)
--或者
SELECT a.AddressID, a.AddressLine1, a.AddressLine2, a.City, a.StateProvinceID, a.PostalCode, a.rowguid
FROM Person.Address AS a
WHERE a.AddressLine1 = CASE WHEN @AddressLine1 IS NULL THEN a.AddressLine1 ELSE @AddressLine1 END
AND a.AddressLine2 = CASE WHEN @AddressLine2 IS NULL THEN a.AddressLine1 ELSE @AddressLine2 END
AND a.City = CASE WHEN @City IS NULL THEN a.City ELSE @City END
AND a.PostalCode = CASE WHEN @PostalCode IS NULL THEN a.PostalCode ELSE @PostalCode END
AND a.StateProvinceID = CASE WHEN @StateProvinceID IS NULL THEN a.StateProvinceID ELSE @StateProvinceID END
GO
SET NOCOUNT OFF
SELECT ...
FROM ...
WHERE ...
OPTION (RECOMPILE);
如果我们要使用重新编译，那么我们是否需要考虑以下两个问题呢？
如果我们知道一个特定的语句总是返回相同数量的行并使用相同的计划（并且我们已测试过并知道这一点），那么我们会正常创建存储过程并让计划得到缓存。
CREATE PROC [dbo].[HighPerformanceExample]
(
@AddressLine1 NVARCHAR(60) = NULL,
@AddressLine2 NVARCHAR(60) = NULL,
@City NVARCHAR(30) = NULL,
@PostalCode NVARCHAR(15) = NULL,
@StateProvinceID SMALLINT = NULL
)
AS
SET NOCOUNT ON
DECLARE @SQL NVARCHAR(MAX),@ColumnName VARCHAR(4000),@ParamDefinition NVARCHAR(500),@Recompile BIT = 1;
SET @ColumnName = 'a.AddressID, a.AddressLine1, a.AddressLine2, a.City, a.StateProvinceID, a.PostalCode, a.rowguid'
SET @SQL = 'SELECT ' + @ColumnName + ' FROM Person.Address AS a WHERE 1 = 1'
IF (@StateProvinceID IS NOT NULL)
SET @SQL = @SQL + ' AND a.StateProvinceID = @StateProvinceID'
IF (@AddressLine1 IS NOT NULL)
SET @SQL = @SQL + ' AND a.AddressLine1 LIKE @AddressLine1'
IF (@AddressLine2 IS NOT NULL)
SET @SQL = @SQL + ' AND a.AddressLine2 LIKE @AddressLine2'
IF (@City IS NOT NULL)
SET @SQL = @SQL + ' AND a.City LIKE @City'
IF (@PostalCode IS NOT NULL)
SET @SQL = @SQL + ' AND a.PostalCode LIKE @PostalCode'
IF (@StateProvinceID IS NOT NULL)
SET @Recompile = 0
IF (PATINDEX('%[%_?]%',@AddressLine1) >= 4
OR PATINDEX('%[%_?]%', @AddressLine2) = 0)
AND (PATINDEX('%[%_?]%', @City) >= 4
OR PATINDEX('%[%_?]%', @PostalCode) = 0)
SET @Recompile = 0
IF @Recompile = 1
BEGIN
SET @SQL = @SQL + N' OPTION(RECOMPILE)';
END;
SET @ParamDefinition = N'@AddressLine1 NVARCHAR(60),
@AddressLine2 NVARCHAR(60),
@City NVARCHAR(30),
@PostalCode NVARCHAR(15),
@StateProvinceID SMALLINT'
EXECUTE sp_executesql @SQL,@ParamDefinition,
@AddressLine1 = @AddressLine1,
@AddressLine2 = @AddressLine2,
@City = @City,
@PostalCode = @PostalCode,
@StateProvinceID = @StateProvinceID
GO
SET NOCOUNT OFF
