December 10, 2008 at 11:05 am
Which index i should use if max query on my table is like
Where columnname equals i.e. exact match
also if on some other table if i have queries like between , in etc then which index would be best.
pls suggest and also why. If you can give me detail information about indexes alongwith answer to my specific question that would be great.
December 10, 2008 at 11:15 am
If you have an exact match query, you use a nonclustered index that includes that column. If it makes sense, which is dependent on percentage of queries, space, etc., you could include columns to cover the query and store the fields needed in the index.
For BETWEEN queries, if there are enough, a clustered index can make sense here.
December 10, 2008 at 3:09 pm
Indexing is a pretty big topic that can't really be done justice in a single post.
You need to think about the fact that the SELECT statement can also affect index usage. For example, you have a query, as defined above
WHERE columnA = 'EXECT MATCH'
On this table you have a clustered index (because with only a few exceptions, you should have a clustered index) and a non-clustered index that contains the column used in the query. But, in the select list is this:
SELECT columnA, columnB, columnC
FROM TableName...
Now, your query will use the index that matches columnA, but it will also have to use the clustered index to get the rest of the columns. This is called a key lookup (it used to be called a bookmark lookup). It adds overhead to the query. So then you need to start worrying about whether or not you should create a covering index, an index that contains all columns used by the query.
It's a pretty thick topic. Best to read up on it and ask one question at a time. You'll get more details that way.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 11, 2008 at 2:45 am
thanks grant for additional information.
So what is the approach for creating covering index.
As you said in Select query, we may have more then one column. How shd i approach for rest of the column
December 11, 2008 at 3:16 am
So what is the approach for creating covering index.
A simple example:
SELECT Column1 FROM Table1 WHERE Colmn2 = @Value
CREATE NONCLUSTERED INDEX ixTable1
ON Table1 (Column2)
INCLUDE (Column1)
December 11, 2008 at 5:55 am
Suresh has a good example there. Perfect.
You just have to decide if the size of the index and the added cost of maintaining it are worth the benefits you'll derive from having the covering index. Two, three, even six or seven columns, depending on the data types and sizes, could work fine in an included index, but at some point, one that is really dependent on your system, number of rows, data size, the index gets to be too big and the costs outweigh the benefits. Testing and evaluation of the tests are the only way to know for sure.
The only rule of thumb is to keep it as small as you can. But note the phrase "as you can." It's not "as small as posssible." This is important because you don't want to assume that you can only ever have one column indexes of INT data type (as I've heard ill-informed people suggest).
Like I said in the first post, this is a huge topic.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 11, 2008 at 6:03 am
thanks grant once again for useful information
April 28, 2009 at 1:22 pm
Grant,
could you please give me some links. Want to go in depth of this topic.
many thanks
April 28, 2009 at 1:28 pm
There are several articles on Gail Shaw's blog[/url]. There are several good articles here on SQL Server Central, just search in the window in the upper right. For real depth you should take a class or read a couple of books. I do know of one that covers this topic in a bit of detail (look down).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply