SQL 2008+

Creating Filtered Indexes using SQL Server Management Studio

by Richard Carr, published at http://www.blackwasp.co.uk/FilteredIndexesSSMS.aspx

SQL Server filtered indexes allow only the rows in a table that meet specified criteria to be indexed, without including the remaining data. They can be created using Transact-SQL or with the tools provided in Microsoft SQL Server Management Studio.

Filtered Indexes

In a previous article I described the use of filtered indexes in SQL Server databases. These allow you to index only a subset of the information in a table. When used correctly, they can improve the performance of some queries whilst incurring lower overheads than a full index.

In the earlier article we created indexes using Transact-SQL (T-SQL) scripts. Some developers prefer to use software such as SQL Server Management Studio (SSMS) to design their databases, as these tools can be more intuitive. You can create filtered indexes using SSMS dialog boxes.

Creating a Filtered Index

To demonstrate the creation of a filtered index we need a table to work with. Let's use the same table schema and sample data as in the earlier article. Create a new database and run the following script against it. This creates a table to store employee details. It includes a self-referencing foreign key that links an employee to his or her manager. Top-level managers have a NULL ManagerID.

CREATE TABLE Employees
(
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(50),
    ManagerID INT NULL
)
 
ALTER TABLE Employees
ADD CONSTRAINT FK_Employee_Manager
FOREIGN KEY (ManagerID)
REFERENCES Employees (EmployeeID)


INSERT INTO Employees
    (EmployeeID, Name, ManagerID)
VALUES
    (1, 'Bob Boss', NULL),
    (2, 'Mel Manager', NULL),
    (3, 'Sam Black', 1),
    (4, 'Jim Brown', 1),
    (5, 'Tim Green', 2),
    (6, 'Sue White', 2),
    (7, 'Dan Blue', 6)

In the previous article we created a filtered index for the Name column. The index was filtered so that only top-level managers were included. Let's create the same index again, this time using the SSMS tools.

To create a filtered index from SSMS, use the Object Browser to find and expand the table for which an index is required. Within the tree structure for the table is the section labelled, "Indexes".

If you are using SQL Server 2008, right-click "Indexes" and choose "New Index..." from the context-sensitive menu. For SQL Server 2012, right click "Indexes", select the "New Index" option and click "Non-Clustered Index" in the submenu. The New Index dialog box should now be displayed. The exact layout is dependent upon the version of the software in use. The image below shows a sample dialog box when using SQL Server 2012.

NB: If the "New Index" option is unavailable, make sure that the table designer for the table is not open.

You need to provide at least two pieces of information in the first page of the dialog box. Firstly, you must set a name for the index. Change the index name to "IX_Managers". Secondly, the columns that are to be indexed must be selected. To select the columns, click the "Add..." button that appears near the column list. A new dialog box will appear:

The dialog box shows a list of all of the columns within the table that are available for indexing. Tick the checkbox adjacent to the "Name" column to add it to the index. Click OK to accept the changes. The dialog box will disappear, returning you to the New Index window, which will be updated to show that the Name column was selected.

So far, we have configured the dialog box to create a standard, non-clustered index. To make a filtered index we need to specify the criteria for the WHERE clause. This is achieved using another page in the dialog box. To display the page, click the "Filter" option to the left of the window. This will reveal the Filter Expression text box.

You can set the filter using the syntax that would normally follow a WHERE clause. In this case we only want to include rows where the ManagerID column is NULL. To do so, change the filter condition to "ManagerID IS NULL", as pictured below:

You can now create the filtered index by clicking the OK button.