-------------------------------------------------------------------------------- -- ② Integers table -------------------------------------------------------------------------------- with p0(i) as (select 1 union all select 1 union all select 1 union all select 1) , p1(i) as (select 1 from p0 as a, p0 as b, p0 as c, p0 as d, p0 as e)--1K rows , p2(i) as (select 1 from p1 as a, p1 as b)--1M rows select row_number() over(order by i) as val into integers from p2

CREATE TABLE dbo.TestIndexSample ( Code char(4) NOT NULL, Name nvarchar(200) NOT NULL, ModifiedDate datetime NOT NULL CONSTRAINT [DF_TestIndexSample_ModifiedDate] DEFAULT GETDATE(), CONSTRAINT [PK_TestIndexSample_Code] PRIMARY KEY CLUSTERED(Code) ); CREATE NONCLUSTERED INDEX IX_TestIndexSample_Name ON dbo.TestIndexSample(Name); INSERT INTO dbo.TestIndexSample(Code, Name) SELECT CAST(val as CHAR(4)), 'NAME' + CAST(val as VARCHAR(10)) FROM integers WHERE val <= 321 ORDER BY val;

set statistics xml on; SELECT Code,Name,ModifiedDate FROM dbo.TestIndexSample WITH(INDEX (IX_TestIndexSample_Name)) WHERE Name = 'NAME10';

SELECT Name FROM dbo.TestIndexSample WHERE Name = 'NAME10';