March 6, 2009 at 8:34 am
Hi,
wanted to confirm some basic concepts about indexes.
my understanding of clustered index and nonclustered index is like this.
if I have a clustered index on column "a" and I have a non unique/nonclustered index on column "b" then the covered columns for this non clustered index is "a" and "b". I wanted to confirm whether this is correct or not?
My second question is this. Lets say I have a multi column clustered index (I understand that this is not the best option) on a general ledger table where there is large amounts of data I would consider that it would make sense to cluster such columns like financial year, client id, and month period billing. My idea is that all my reports use these fields for reporting and most of the reports are financial year based so it would make sense to have the data physically sorted according by financial year and client id.
What I would like to know is based on the above statements lets say I have the following query which have in its where clause columns "financial year", "client id" and "month" followed by "general ledger type code". If I create a non-clustered index based solely on "general ledger type code" then this index would be used since all nonclustered/non unique indexes include a clustered index columns.
Lets say I have a new query which has a where clause for "financial year", "Client id", "general ledger type code" and "account number" and I have a nonclustered/non unique index based on general ledger type code and account number. Does this mean that this non clustered index does not get used ?
I am sorry if this post sounds a bit confusing and thanks for all the help out there.
March 6, 2009 at 8:42 am
The main thing to know about clustered indexes is the sort order: data is phisically sorted according to the clustered index order.
A table can have only one clustered index.
Every nonclustered index internally refers to the clustered index (if any), so, when you are querying with search arguments in the clustered index and in a (covering) nonclustered index, you can be sure the engine will seek/scan the nonclustered index only.
Regards
Gianluca
-- Gianluca Sartori
March 6, 2009 at 12:58 pm
chrisau168 (3/6/2009)
Hi,wanted to confirm some basic concepts about indexes.
my understanding of clustered index and nonclustered index is like this.
if I have a clustered index on column "a" and I have a non unique/nonclustered index on column "b" then the covered columns for this non clustered index is "a" and "b". I wanted to confirm whether this is correct or not?
Yes it is correct
My second question is this. Lets say I have a multi column clustered index (I understand that this is not the best option)
That's not necessarily true. I've worked on plenty of systems where a compound clustered index not only worked well, but was the best way to make the system work. It depends on the system. The most common approach is a single value, frenquently the primary key, as the cluster, but that doesn't make it the single best option
on a general ledger table where there is large amounts of data I would consider that it would make sense to cluster such columns like financial year, client id, and month period billing. My idea is that all my reports use these fields for reporting and most of the reports are financial year based so it would make sense to have the data physically sorted according by financial year and client id.
What I would like to know is based on the above statements lets say I have the following query which have in its where clause columns "financial year", "client id" and "month" followed by "general ledger type code". If I create a non-clustered index based solely on "general ledger type code" then this index would be used since all nonclustered/non unique indexes include a clustered index columns.
Depending on the selectivity of the values in that column, yes. But let's say you have 3 general ledger type code values... probably not
Lets say I have a new query which has a where clause for "financial year", "Client id", "general ledger type code" and "account number" and I have a nonclustered/non unique index based on general ledger type code and account number. Does this mean that this non clustered index does not get used ?
I am sorry if this post sounds a bit confusing and thanks for all the help out there.
Possibly, yes.
The one thing you left out of your questions are the columns in the SELECT list. They affect this too. Let's take your last example and assume that the columns listed in the SELECT are the same as in the WHERE clause plus one other column, say Client Name. If you look at the query plan, instead of a single nonclustered index seek, you also see a key lookup seek on the clustered index. This is because the nonclustered index you defined is not a covering index, meaning it doesn't have all the columns necessary to meet all the requirements of the query. So the query had to go back to the clustered index to get that last column.
Now, let's assume it's only the one column, then that column could be added to your nonclustered index as an INCLUDE column which would make the nonclustered index covering. But let's assume it's 16 more columns. You probably don't want to use that many INCLUDE columns, so you're back to the key lookup again.
Ain't indexing fun?
"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
March 6, 2009 at 1:52 pm
Thanks for the replies.
One thing I would like to clarify is if in an execution plan the only items that have percentages are clustered index seeks and index seeks and everything has a percentage of zero does this mean that the indexes being used are good ?
Even though the query is slow.
Thanks
March 6, 2009 at 2:28 pm
chrisau168 (3/6/2009)
One thing I would like to clarify is if in an execution plan the only items that have percentages are clustered index seeks and index seeks and everything has a percentage of zero does this mean that the indexes being used are good ?
It's not possible for all the operators to have a percentage of 0. The percentages within a plan have to add to 100%. Somewhere there's an operator with cost.
If you want, post the plan (saved as a .sqlplan file, zipped and attached) an we'll take a look.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply