Database Reference
In-Depth Information
As you can see, it is better to add a column as the key column if you expect to use SARGable predicates
against that column. Otherwise, it is better to add a column as an included column and make the non-leaf index
levels smaller.
Filtered Indexes
Filtered indexes
, introduced in SQL Server 2008, allowed you to index only a subset of the data. That reduced the index
size and the maintenance overhead.
Consider a table with some data that needs to be processed as an example. This table can have a
Processed bit
column, which indicates the row status. Listing 4-7 shows a possible table structure.
Listing 4-7.
Filtered indexes: Table creation
create table dbo.Data
(
RecId int not null,
Processed bit not null,
/* Other Columns */
);
create unique clustered index IDX_Data_RecId on dbo.Data(RecId);
Let's assume that you have a backend process that loads unprocessed data based on the query shown in
Listing 4-8.
Listing 4-8.
Filtered indexes: Query that reads unprocessed data
select top 1000 RecId, /* Other Columns */
from dbo.Data
where Processed = 0
order by RecId;
This query can benefit from the following index:
CREATE NONCLUSTERED INDEX IDX_Data_Processed_RecId ON
dbo.Data(Processed, RecId)
. Even though SQL Server rarely uses indexes on
bit
columns due to their low selectivity,
such a scenario might be an exception if there are just a handful of unprocessed rows. SQL Server can use that index to
select them; however, the index will never be used for selection of processed rows if a
Key Lookup
is required.
As a result, all index rows with a key value of
Processed=1
would be useless. They will increase the index's size,
waste storage space, and introduce additional overhead during index maintenance.
Filtered indexes solve that problem by allowing you to index just unprocessed rows, making the index small and
efficient. Listing 4-9 illustrates this concept.
Listing 4-9.
Filtered indexes: Filtered index
create nonclustered index IDX_Data_Unprocessed_Filtered
on dbo.Data(RecId)
include(Processed)
where Processed = 0;
