Indexes

Here’s a complete T-SQL example showing how to create and use a clustered and non-clustered index in SQL Server.

-- Create a sample table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,       -- Primary key creates a clustered index by default
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Email NVARCHAR(100),
    Department NVARCHAR(50)
);

-- Insert sample data
INSERT INTO Employees (EmployeeID, FirstName, LastName, Email, Department)
VALUES
(1, 'John', 'Doe', 'john.doe@example.com', 'IT'),
(2, 'Jane', 'Smith', 'jane.smith@example.com', 'HR'),
(3, 'Michael', 'Brown', 'michael.brown@example.com', 'Finance'),
(4, 'Emily', 'Davis', 'emily.davis@example.com', 'IT');

-- Create a NONCLUSTERED INDEX on the Email column
CREATE NONCLUSTERED INDEX IX_Employees_Email
ON Employees (Email);

-- Example query that can benefit from the non-clustered index
SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE Email = 'jane.smith@example.com';

Explanation

Clustered Index

Created automatically on the PRIMARY KEY (EmployeeID here). Determines the physical order of rows in the table.

Nonclustered Index

Created separately from the table data. Stores the indexed column(s) plus a pointer (row locator) to the actual data. Useful for speeding up searches on non-key columns like Email.

Naming Convention

IX_Employees_Email is a descriptive name (IX_ prefix for index).

Performance Benefit

The WHERE Email = ... query will use the non-clustered index to quickly locate the row without scanning the whole table.

Covered Queries

You can also create a non-clustered index with included columns to cover queries and avoid lookups:

CREATE NONCLUSTERED INDEX IX_Employees_Email_Includes
ON Employees (Email)
INCLUDE (FirstName, LastName);

This way, SQL Server can satisfy the query entirely from the index without going back to the base table.

---