execution plan in sqlserver

一直对执行计划中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'