Few things about indexes - 2

Restrictions on creating indexed views

The most important things that cannot be done are as follows:

• Use the SELECT * syntax—columns must be explicitly named.
• Use a CLR user defined aggregate.
• Use UNION, EXCEPT, or INTERSECT in the view.
• Use any sub queries.
• Use any outer joins or recursively join back to the same table.
• Specify TOP in the SELECT clause.
• Use DISTINCT.
• Include a SUM () function if it references more than one column.
• Use COUNT (*), though COUNT_BIG (*) is allowed.
• Use almost any aggregate function against a nullable expression.
• Reference any other views, or use CTEs or derived table.
• Reference any nondeterministic functions.
• Reference data outside the database.
• Reference tables owned by a different owner.

Index Design Recommendations

The main recommendations for index design are as follows:

•          Examine the WHERE clause and JOIN criteria columns.
•          Use narrow indexes.
•          Examine column uniqueness.
•          Examine the column data type.
•          Consider column order.
•          Consider the type of index (clustered vs. nonclustered)

When the amount of data inside a table is so small that it fits onto a single page (8KB), a table scan may work better than an index seek. If you have a good index in place but you’re still getting a scan, consider this issue.

Another way to force a different behavior on SQL Server 2012 is the FORCESEEK query hint. FORCESEEK makes it so the optimizer will choose only Index Seek operations. If the query were rewritten like this:

SELECT e.*

FROM HumanResources.Employee AS e WITH (FORCESEEK)

WHERE e.SickLeaveHours = 59

Defining the Bookmark Lookup

When a query requests columns that are not part of the nonclustered index chosen by the optimizer, a lookup is required. This may be a key lookup when going against a clustered index or an RID lookup when performed against a heap. The common term for these lookups comes from the old definition name, bookmark lookup. The lookup fetches the corresponding data row from the table by following the row locator value from the index row, requiring a logical read on the data page besides the logical read on the index page. However, if all the columns required by the query are available in the index itself, then access to the data page is not required. This is known as a covering index.

These lookups are the reason that large result sets are better served with a clustered index. A clustered index doesn’t require a bookmark lookup, since the leaf pages and data pages for a clustered index are the same.