How should the non-clustered index be created?
Hey. I've a table with about 18 columns Many of the queries which are made on this table include 4 or 5 columns in the where clause and thesame query has 5 or 6 of the other columns in the select clause. Somebody has created about 11 indexes, 1 for each column...In essence the query would look like this
select col1,col2,col3,col4,col5
from table1
where col6=100
and col7='test.exe'
and col8 = 'helloWorld'
I am thinking the index should be built on all 8 of this columns in SQL 2000. Also, in SQL 2005, I know I can use included columns for this but can someone tell me what columns should the non-clustered built on and which cols should be included? Thank you.
[720 byte] By [
Tej] at [2007-12-24]
? You are correct. Those single-column indexes will probably not be very useful, based on what you describe. When a query uses a nonclustered index in SQL Server, it can use the index to efficiently find the rows, but then it has to go back to the clustered index for any additional columns not present in the nonclustered index (that is called a "lookup"). Lookups can be expensive, especially if SQL Server needs to do them for a lot of rows -- so extremely narrow nonclustered indexes tend to be limited use except when you're only working with a single column (or the indexed column and the clustered index columns -- keep in mind that nonclustered indexes include the table's clustering key at the leaf level). Based on the sample query you've posted, I would put Col6, Col7, and Col8 first in the index (ordered based on how selective each is -- put the one with the most unique values first), then col1-col5. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Tej@discussions.microsoft.com> wrote in message news:14bc2659-cfdc-4d0f-b8a7-c312426e8be1@discussions.microsoft.com... Hey. I've a table with about 18 columns Many of the queries which are made on this table include 4 or 5 columns in the where clause and thesame query has 5 or 6 of the other columns in the select clause. Somebody has created about 11 indexes, 1 for each column...In essence the query would look like this select col1,col2,col3,col4,col5 from table1 where col6=100 and col7='test.exe' and col8 = 'helloWorld' I am thinking the index should be built on all 8 of this columns in SQL 2000. Also, in SQL 2005, I know I can use included columns for this but can someone tell me what columns should the non-clustered built on and which cols should be included? Thank you.
if in the above example if col6 is a clustred index column , should we still create this non clustred index with cols 1 to 8 ?
inother words is it a good practise to include a clustered index column in a nonclustered index ?
thanks a lot in advance
Index intersection ?
Index intersection allows the query processor to use multiple indexes to solve a query. Most database query processors use only one index when attempting to resolve a query. SQL Server can combine multiple indexes from a given table or view, build a hash table based on those multiple indexes, and utilize the hash table to reduce I/O for a given query. The hash table that results from the index intersection becomes, in essence, a covering index and provides the same I/O performance benefits that covering indexes do. Index intersection provides greater flexibility for database user environments in which it is difficult to predetermine all of the queries that will be run against the database. A good strategy in this case is to define single-column, nonclustered indexes on all the columns that will be frequently queried and let index intersection handle situations where a covered index is needed.
The following example makes use of index intersection:
Create index Indexname1 on Table1(col2) Create index Indexname2 on Table1(col3) Select col3 from table1 where col2 = 'value' When the previous query is performed, the indexes can be combined to quickly and efficiently resolve the query.
So when there are individual non clustered indexes on single columns , doesnt index intersection come into play ? Please clarify if covering index is better than index intersection
Thanks
 Tej62007 wrote: |
| Hey. I've a table with about 18 columns Many of the queries which are made on this table include 4 or 5 columns in the where clause and thesame query has 5 or 6 of the other columns in the select clause. Somebody has created about 11 indexes, 1 for each column...In essence the query would look like this select col1,col2,col3,col4,col5 from table1 where col6=100 and col7='test.exe' and col8 = 'helloWorld' I am thinking the index should be built on all 8 of this columns in SQL 2000. Also, in SQL 2005, I know I can use included columns for this but can someone tell me what columns should the non-clustered built on and which cols should be included? Thank you. | |
