Indexes - part 5

Let's see examples with Filtered and covering 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

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

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

--Create Covering Index
CREATE NONCLUSTERED INDEX NCI_SmallTable ON Employees(EmpId,Name) INCLUDE (BirthDate)

SELECT EmpId,Name,BirthDate,JobDetails
FROM Employees
WHERE  BirthDate = '2009-05-26 00:00:00.000'

--Create filtered index
CREATE NONCLUSTERED INDEX NCI_BirthDate ON Employees(BirthDate)
WHERE BirthDate >= '2009-05-26 00:00:00.000'

--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')

SELECT EmpId,Name,BirthDate,JobDetails
FROM Employees
WHERE  BirthDate = '2009-05-26 00:00:00.000'