SQL Server Index

Here is an example to create index and how it make sustainable performance variation with and without index

USE tempdb;
GO

CREATE TABLE Table1
(
 [id] int identity,
 [name] varchar(20),
 [date] datetime
);
GO

SET NOCOUNT ON;
DECLARE @i int = 0;
WHILE @i < 1500000
 BEGIN
  INSERT INTO Table1 ([name], [date]) VALUES ('name ' + CAST(@i AS varchar), DATEADD(ss, -@i, GETDATE()));
  SET @i += 1;
 END

--check the execution. you will see table scan in the execution plan, which is bad.
--logical reads 7063

SET STATISTICS IO ON;
SELECT
 [id],
 [name]
FROM
 [Table1]
WHERE
 [date] = '2011-05-25 19:28:27.037';
SET STATISTICS IO OFF;

--now indexing the table with a covering index
CREATE NONCLUSTERED INDEX [NCI_Date]
ON [dbo].[Table1] ([date]) INCLUDE ([id], [name]);
GO

--check the execution again + execution plan. You may see Index seek only, which is great! ;)
--logical reads 3
SET STATISTICS IO ON;
SELECT
 [id],
 [name]
FROM
 [Table1]
WHERE
 [date] = '2011-05-25 19:28:27.037'
SET STATISTICS IO OFF;