一直对执行计划中Index的'Table Scan','Index Seek','Index Scan','Clustered Index Scan','Clustered Index Seek'理解的不是很清楚.
于是建立了个实际的表,插入了数据.做做实验。
CREATE TABLE [dbo].[PerformanceIssue]( [PRID] [uniqueidentifier] NOT NULL, [PRCode] [int] NOT NULL, [PRDesc] [varchar](100) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ) ON [PRIMARY] GO Declare @Loop Int Declare @PRID UniqueIdentifier Declare @PRDesc varchar(100) Set @Loop = 1 Set @PRDesc = '' WHILE @Loop <= 100000 BEGIN Set @PRID = NewID() Set @PRDesc = ' PerformanceIssue - ' + Convert( Varchar(10),@Loop ) Insert Into PerformanceIssue Values (@PRID, @Loop, @PRDesc) Set @Loop = @Loop + 1 END
1. 执行语句
Select PRID, PRCode, PRDesc From PerformanceIssue 执行完后看执行计划是走‘Table Scan’
2.看来要建立Index了,于是建立NONCLUSTERED index试试
CREATE UNIQUE NONCLUSTERED INDEX UNC_PRID
ON PerformanceIssue (PRID)
GO
Case 1: Select PRID, PRCode, PRDesc From PerformanceIssu还是‘Table Scan’
Case 2: Select PRID From PerformanceIssue这样就‘Index Scan’Case 3: Select PRID, PRCode, PRDesc From PerformanceIssue Where PRCode = 8 还是'Table Scan'
Case 4: Select PRID, PRCode, PRDesc From PerformanceIssue Where PRID = 'D386C151-5F74-4C2A-B527-86FEF9712955'
这样就用到了'Index Seek', 'RID Lookup'
Case 5: Select PRID From PerformanceIssue Where PRID = 'D386C151-5F74-4C2A-B527-86FEF9712955'
这样的话‘RID Lookup’就会Remove变成'Index Seek'.
Remark:For the 'RID Lookup'解释
A RID Lookup is a lookup into a heap table using a Row ID. The Row ID is included in a non-clustered index in order to find the rest of a table's data in the heap table. Since a heap table is a table without a clustered index and is sorted unordered a Row ID is required for the correlation.
3.好了,现在删除unclustered index,加一个clustered index
Drop Index PerformanceIssue.UNC_PRID
GO
CREATE UNIQUE CLUSTERED INDEX UC_PRCode
ON PerformanceIssue( PRCode)
GO
Case 1:Select PRID, PRCode, PRDesc From PerformanceIssue走‘Clustered Index Scan’.
Case 2:Select PRID, PRCode, PRDesc From PerformanceIssue Where PRCode = 8走'Clustered Index Seek'