Archive

Archive for August, 2011

Convering & Filter Index

August 24, 2011 Leave a comment

Covering Indexes

Key Lookups can be detrimental to performance during query resolution for large result sets, the natural question is: how can we avoid them? To answer that question, let’s consider a query that does not require a Key Lookup.

Let’s begin by modifying our query so that it no longer selects the Email_Address column.

Including Non-Key columns

SQL Server 2005 provided a new feature for nonclustered indexes, the ability to include additional, non-key columns in the leaf level of the nonclustered indexes. These columns are technically not part of the index, however they are included in the leaf node of the index. SQL Server 2005 and SQL Server 2008 allow up to 1023 columns to be included in the leaf node.

To create a nonclustered index with included columns, use the following Transact-SQL syntax.

CREATE NONCLUSTERED INDEX [ix_Customer_Email] ON [dbo].[Customers]

(

            [Last_Name] ASC,

            [First_Name] ASC

)

INCLUDE ( [Email_Address]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Rerunning our query yields an execution plan that make use of our new index to rapidly return the result set. 

 Filtered Index

SQL Server 2008 introduces a new type of index called “Filtered Index” which is basically a covered indexed designed to retrieve a smaller set of qualified data from a table. This can be a very hand feature particularly in working with larger data tables.

A Filter index allows us to apply filter criteria on the index definition so that a particular sub set of rows in a table alone can be indexed. Filter indexes can be only created as non clustered index

Syntax

CREATE INDEX Index Name ON Table Name (Columns..) Filter Criteria

Example

CREATE INDEX IX_RegistrationDate ON Employee (RegistrationDate) WHERE RegistrationDate IS NOT NULL

Advantages of Filtered Index

•Improved Performance: The performance of the query is improved especially with larger tables as it has to scan through as lesser number of records

•Lesser Maintenance Cost: Since the size of the index is smaller compared to full table index the index maitntenance cost will be much lesser. Also index maintenance jobs like update statics could be faster.

•Lesser Storage: The amount of space required for index storage will also be very less since the size of the index is smaller compared to the full table index

Advertisements
Categories: SQL