34,588
社区成员
发帖
与我相关
我的任务
分享
--强制使用索引:
IF OBJECT_ID('TB')IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(ID INT ,[NAME] VARCHAR(10),MENO VARCHAR(10))
INSERT TB SELECT 1,'A','TEST1'
INSERT TB SELECT 2,'B','TEST2'
CREATE INDEX INDEX_TEST ON TB(ID,[NAME])
GO
SET SHOWPLAN_ALL ON
GO
--SELECT * FROM TB WHERE ID=1
SELECT * FROM TB WITH(INDEX(INDEX_TEST)) WHERE NAME='A'
GO
SET SHOWPLAN_ALL OFF
GO
DROP TABLE TB
--這樣又不走索引
IF OBJECT_ID('TB')IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(ID INT ,[NAME] VARCHAR(10),MENO VARCHAR(10))
INSERT TB SELECT 1,'A','TEST1'
INSERT TB SELECT 2,'B','TEST2'
CREATE INDEX INDEX_TEST ON TB(ID,[NAME])
GO
SET SHOWPLAN_ALL ON
GO
--SELECT * FROM TB WHERE ID=1
SELECT * FROM TB WHERE NAME='A'
GO
SET SHOWPLAN_ALL OFF
/*StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ -------------------------------------------------------- ------------------------------------ ------------------------ ------------------------ ------------------------ ----------- ------------------------ ------------------------------------ -------- ------------------------------ -------- ------------------------
--SELECT * FROM TB WHERE ID=1
SELECT * FROM TB WHERE NAME='A' 170 1 0 NULL NULL 1 NULL 1.0 NULL NULL NULL 3.7659202E-2 NULL NULL SELECT 0 NULL
|--Table Scan(OBJECT:([db_study].[dbo].[TB]), WHERE:([TB].[NAME]='A')) 170 3 1 Table Scan Table Scan OBJECT:([db_study].[dbo].[TB]), WHERE:([TB].[NAME]='A') [TB].[MENO], [TB].[NAME], [TB].[ID] 1.0 3.7578501E-2 8.0700003E-5 29 3.7659202E-2 [TB].[MENO], [TB].[NAME], [TB].[ID] NULL PLAN_ROW 0 1.0
(影響 2 個資料列)
*/
IF OBJECT_ID('TB')IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(ID INT ,[NAME] VARCHAR(10),MENO VARCHAR(10))
INSERT TB SELECT 1,'A','TEST1'
INSERT TB SELECT 2,'B','TEST2'
CREATE INDEX INDEX_TEST ON TB(ID,[NAME])
GO
SET SHOWPLAN_ALL ON
GO
SELECT * FROM TB WHERE ID=1
/*
StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
------------------------------------------------------------------------------------ ----------- ----------- ----------- ------------------------------ ------------------------------ ------------------------------------------------------------------- --------------------------------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- --------------------------------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------
SELECT * FROM TB WHERE ID=1 1 1 0 NULL NULL 1 NULL 1 NULL NULL NULL 0.0032842 NULL NULL SELECT 0 NULL
|--Table Scan(OBJECT:([testdb].[dbo].[TB]), WHERE:([testdb].[dbo].[TB].[ID]=(1))) 1 2 1 Table Scan Table Scan OBJECT:([testdb].[dbo].[TB]), WHERE:([testdb].[dbo].[TB].[ID]=(1)) [testdb].[dbo].[TB].[ID], [testdb].[dbo].[TB].[NAME], [testdb].[dbo].[TB].[MENO] 1 0.003125 0.0001592 23 0.0032842 [testdb].[dbo].[TB].[ID], [testdb].[dbo].[TB].[NAME], [testdb].[dbo].[TB].[MENO] NULL PLAN_ROW 0 1
(2 行受影响)
*/
IF OBJECT_ID('TB')IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(ID INT ,[NAME] VARCHAR(10),MENO VARCHAR(10))
INSERT TB SELECT 1,'A','TEST1'
INSERT TB SELECT 2,'B','TEST2'
CREATE INDEX INDEX_TEST ON TB(ID,[NAME])
GO
SET SHOWPLAN_ALL ON
GO
SELECT * FROM TB WHERE ID=1
SELECT * FROM TB WHERE MENO='TEST2'
/*StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
------------------------------------------------------------------------------------------------------------------ ----------- ----------- ----------- ------------------------------ ------------------------------ -------------------------------------------------------------------------------------------- ------------------------------------ ------------------------ ------------------------ ------------------------ ----------- ------------------------ ------------------------------------ -------- ------------------------------ -------- ------------------------
SELECT * FROM TB WHERE ID=1 9 1 0 NULL NULL 1 NULL 1.0 NULL NULL NULL 3.3329551E-3 NULL NULL SELECT 0 NULL
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([db_study].[dbo].[TB])) 9 3 1 Bookmark Lookup Bookmark Lookup BOOKMARK:([Bmk1000]), OBJECT:([db_study].[dbo].[TB]) [TB].[MENO], [TB].[NAME], [TB].[ID] 1.0 4.8826954E-5 0.0000011 29 3.3329551E-3 [TB].[MENO], [TB].[NAME], [TB].[ID] NULL PLAN_ROW 0 1.0
|--Index Seek(OBJECT:([db_study].[dbo].[TB].[INDEX_TEST]), SEEK:([TB].[ID]=Convert([@1])) ORDERED FORWARD) 9 4 3 Index Seek Index Seek OBJECT:([db_study].[dbo].[TB].[INDEX_TEST]), SEEK:([TB].[ID]=Convert([@1])) ORDERED FORWARD [Bmk1000] 1.0 3.2034251E-3 7.9603E-5 22 3.283028E-3 [Bmk1000] NULL PLAN_ROW 0 1.0
SELECT * FROM TB WHERE MENO='TEST2' 10 5 0 NULL NULL 2 NULL 1.0 NULL NULL NULL 3.7659202E-2 NULL NULL SELECT 0 NULL
|--Table Scan(OBJECT:([db_study].[dbo].[TB]), WHERE:([TB].[MENO]=[@1])) 10 7 5 Table Scan Table Scan OBJECT:([db_study].[dbo].[TB]), WHERE:([TB].[MENO]=[@1]) [TB].[MENO], [TB].[NAME], [TB].[ID] 1.0 3.7578501E-2 8.0700003E-5 27 3.7659202E-2 [TB].[MENO], [TB].[NAME], [TB].[ID] NULL PLAN_ROW 0 1.0
(影響 5 個資料列)*/