Lesson 13	Indexing strategies
Objective	Find out how your queries will be indexed.

Indexing Query Strategies in SQL-Server

You should know by now that indexes are commonly used to speed up queries. At the same time, indexes do not automatically improve the performance of queries. In fact, the wrong combination of query and index type can negatively impact your system's performance. When writing an insert query, you should first investigate the indexing that will be used for that query. Once you have this information, you can decide whether you should adjust either your query or the index.

Table Scan

An alternative to an index is a table scan, which simply reads all the records from the table. A table scan is usually used with small tables, and when most of a table's rows will be accessed.

Role of Query Optimizer

The execution of a query is determined by Query Optimizer, a SQL utility that determines whether an index will be used to perform a query. The result of Query Optimizer's analysis is detailed in the query plan. You can view the query plan either through a Transact-SQL statement or graphically, using Query Analyzer.

View query plan with Transact-SQL

Non-graphical statistics can be displayed by issuing this Transact-SQL statement before issuing a query statement:

SET SHOWPLAN_TEXT ON

If you use the SET SHOWPLAN_TEXT ON Transact-SQL statement, SQL Server will not actually run your query.
It will only show the execution plan of the query.

Index Query Plan

The index plan below indicates that the Query Optimizer used the clustered index idx in the IndexTest table. This table is located in the DT database.

|--Clustered Index Insert(OBJECT:
    ([DT].[dbo].[IndexTest].[idx]), 
       SET:([IndexTest].[id]=[Expr1000], 
          [IndexTest].[description]=[Expr1001]), 
          DEFINE:([Expr1000]=[@COUNTER]+5, 
          [Expr1001]=Convert
          ('Description 
          for'+Convert([COUNTER]))))

Table scan Query Plan

The query plan below indicates that an index will not be used for this query.

|--Table Insert(OBJECT:([DT].[dbo].[IndexTest]), 
    SET:([IndexTest].[id]=[Expr1000], 
        [IndexTest].[description]=[Expr1001]), 
            DEFINE:([Expr1000]=[@COUNTER]+5, 
                [Expr1001]=Convert('Description 
                    for'+Convert([@COUNTER]))))

Using Query Analyzer

You can use the SQL Server Query Analyzer by selecting the Query->Show Execution Plan menu. The graphical plan displays the same information as the non-graphical plan, but in much more detail. The graphical plan also displays information about how much disk access SQL Server had to do (indicated by I/O Cost) to achieve the results, and much more. In the next lesson, we will look at how to use the information we have accumulated.