SQL Server Indexing Tips

Page Density
Optimize Row Size: SQL Server 2005 (and later) supports 8K columns. This means a row can be, well, over 80K. Does this make sense? Ah, not usually. Managing row size is as important as ever to those interested in efficiency. The trick to good performance and good use of space is to make sure that when the 8K page is filled with rows, there is little wasted space. This means that if the row size is over (about) 4K, only one row will fit on a page and (about) 4K of space is wasted on the page. A secondary problem is that the number of index pages also has to increase to address the pages.

Filtered Indexes
New for SQL Server 2008, permit you to add a WHERE clause to an index, thus focusing the index on the most important rows

e.g. 
CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate ON Production.BillOfMaterials (ComponentID, StartDate) WHERE EndDate IS NOT NULL ;

The filtered index above is valid for the following query. You can display the query execution plan to determine if the query optimizer used the filtered index.

SELECT ProductAssemblyID, ComponentID, StartDate FROM Production.BillOfMaterials WHERE EndDate IS NOT NULL AND ComponentID = 5 AND StartDate > '01/01/2008' ;

Selectivity
When the query optimizer (QO) studies your SQL, the degree of selectivity determines if an index should be used to perform the operation. By processing the Statistics (or "stats") for an index (and an index can have from one to many (many) sets of stats), the QO can determine the selectivity. Basically, it's weighing the choice of using the index to walk through the selected rows or doing a table scan. The example Kimberly used made it pretty clear how it worked but we were surprised to learn: "When the number of rows (selected by the query) is around 1/4 of the number of data pages in the table, the index was not useful and it is more efficient to perform a table scan to fetch the rows selected. This often turns out to be less than 5% of the rows in the table..."