15.2.1 索引影响查询条件顺序的示例.sql

来自「sqlserver 数据库编程的绝好脚本」· SQL 代码 · 共 36 行

--测试表及数据
CREATE TABLE tb(Col varchar(10))
INSERT tb SELECT '12'
UNION ALL SELECT 'aa'
GO

--观察Transact-SQL语句的执行计划,而不是执行
SET SHOWPLAN_ALL ON
GO

SELECT * FROM tb 
WHERE ISNUMERIC(Col)=1
	AND Col BETWEEN 1 AND 100
GO

SET SHOWPLAN_ALL OFF
GO

--建立索引
CREATE INDEX IDX_tb_Col ON tb(Col)
GO

SET SHOWPLAN_ALL ON
GO

SELECT * FROM tb 
WHERE ISNUMERIC(Col)=1
	AND Col BETWEEN 1 AND 100
GO

SET SHOWPLAN_ALL OFF
GO

--删除测试
DROP TABLE tb