Filtered Indexes
As you may or may not know there are two basic types of indexes available in SQL Server. Clustered and Non-Clustered.
A table (or view) having a clustered index stores the data within the clustered index leaf nodes. A Non-Clustered index stores pointers to the location of the data within the clustered index, if one exists, or the heap, if no clustered index exists.
Clearly, since the actual data of a table may be stored in only one location, a table may have only one clustered index, and have multiple non-clustered indexes.
There are a couple neat features released in SQL Server that enhance the use of indexes. You can create a filtered index on a table by creating the index with a Where clause, just like in a SQL Select statement. This may result in an index pointing to less records, and possibly faster performance.
A second technique is the use of covering indexes. A covering index contains all the columns necessary to support a SQL Select statement. If an index covers all columns required, then the query simply uses the covering index instead o f the table.
An index has a restriction in size and the number of columns allowed in the index. However, with SQL Server 2008 an additional clause exists when creating an index; INCLUDE;
Using the include clause, you may add additional columns so that data contained in them will also be included in the leaves of the non-clustered index, yet not be included in the KEY portion of the index. This allows for broader covering indexes.
Granted, using INCLUDE uses a lot more disk space due to redundancy, and requires more resources to maintain. However, if you are using covering indexes in the first place, you are probably more concerned with SELECT performance than Insert/Update/Delete performance.
Indexing is definitely an art. Too many indexes and data maintenance will suffer. Too few indexes, and data retrieval will suffer.
Feel free to share you index management experience with our readers. Send your comments to btaylor@sswug.org. It would be really cool to hear from our readers using Sybase, Oracle, DB2, MySQL, Informix, or any other data engine as well. I’m sure many vendors have interesting extensions optimizing performance and/or maintenance of indexes.
Database Trivia
Where did Sybase SQL Anywhere come from?
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Database Snapshots (Part 2 of 3)
Database Snapshots offer a read-only, static view of a source database at a specific point in time. Attend this session to learn how Database Snapshots can be used for reporting purposes, change management, and data recovery.
Featured White Paper(s)
Sharepoint Adoption
Written by AvePoint
Microsoft SharePoint is quickly becoming the platform of … (read more)
Featured Script
Rebuild Indexes On All Databases
Annoying how SQL Server uses the same cursor for both sp_Msforeach(table/db), isn’t it? Here’s a way around it…. (read more)