--
--20.1.5节示例
--
创建数据分布不均的表和数据
USE tempdb;
GO
CREATE TABLE t1 --创建测试表
(
id INT IDENTITY PRIMARY KEY,
NAME NVARCHAR(10) NOT NULL,
CreateTime DATETIME DEFAULT(GETDATE())
)
GO
--接下来插入测试数据
SET NOCOUNT ON
INSERT INTO t1(NAME) VALUES(N'Hello')
DECLARE @i INT=0
WHILE @i<1000
BEGIN
INSERT INTO t1(NAME) VALUES(N'Same')
SET @i+=1
END
INSERT INTO t1(NAME) VALUES(N'TheEND')
CREATE INDEX IX_Name ON t1([Name]) --创建索引
创建存储过程
CREATE PROC sp1 --测试用存储过程
@name NVARCHAR(10)
AS
SELECT *
FROM t1
WHERE [NAME]=@name
存储过程与SQL语句分别执行
SET STATISTICS IO ON --打开IO统计
exec sp1 'Same'
SELECT *
FROM t1
WHERE [NAME]='Same'
每次重新编译存储过程
ALTER PROC sp1
@name NVARCHAR(10) WITH RECOMPILE --每次执行都重新编译
AS
SELECT *
FROM t1
WHERE [NAME]=@name
GO
exec sp1 'TheEND'
exec sp1 'Same'
指定重新编译执行计划
EXEC sp1 'TheEND'
EXEC sp1 'Same' WITH RECOMPILE
