Indexes - part 4

Let's see examples with clustered and nonclustered indexes:

USE TestDb
GO

--Create demo table called Employees Table
CREATE TABLE Employees
(EmpId INT IDENTITY(1,1)
 ,Name VARCHAR(256)
 ,BirthDate DATETIME
 ,JobDetails VARCHAR(100)
)
GO

--Insert dummy data into Employees Table
DECLARE @loop INT
SET @loop = 1000

WHILE @loop > 0
BEGIN
      INSERT Employees  VALUES ('Fadi Ahmad Abdulwahab',
      '1984-05-26 00:00:00.000','Job details or description...' )
      SET @loop = @loop - 1
END

--Run Select statement with Execution Plan
SELECT EmpId,Name,BirthDate,JobDetails
FROM Employees
WHERE EmpId = 700

--View Number of Rows in indexes
SELECT Name, i.index_id, [rows] 
FROM sys.partitions p
INNER JOIN sys.indexes i
ON p.[object_id] = i.[object_id]
AND p.index_id = i.index_id
WHERE p.[object_id] = OBJECT_ID('Employees')
GO

-- Create Clustered Index 
CREATE UNIQUE CLUSTERED INDEX CI_EmpId
ON Employees (EmpID)
GO

--View Number of Rows in indexes
SELECT Name, i.index_id, [rows] 
FROM sys.partitions p
INNER JOIN sys.indexes i
ON p.[object_id] = i.[object_id]
AND p.index_id = i.index_id
WHERE p.[object_id] = OBJECT_ID('Employees')

--Run Select statement with Execution Plan
SELECT EmpId,Name,BirthDate,JobDetails
FROM Employees
WHERE EmpId = 700

--Update this column for test
UPDATE dbo.Employees SET 
BirthDate = '2009-05-26 00:00:00.000'
WHERE EmpId=700

 
--Now let's exam this query before we create 
--NonClustered Index 
SELECT EmpId,Name,BirthDate,JobDetails
FROM Employees
WHERE  BirthDate = '2009-05-26 00:00:00.000'

--Let's Create Nonclustered Index on BirthDate column
CREATE NONCLUSTERED INDEX NCI_BirthDate
ON Employees (BirthDate)

--View Number of Rows in indexes
SELECT Name, i.index_id, [rows] 
FROM sys.partitions p
INNER JOIN sys.indexes i
ON p.[object_id] = i.[object_id]
AND p.index_id = i.index_id
WHERE p.[object_id] = OBJECT_ID('Employees')

--Run Select statement with Execution Plan
SELECT EmpId,Name,BirthDate,JobDetails
FROM Employees
WHERE  BirthDate = '2009-05-26 00:00:00.000'