To cluster or Not to cluster
-
Please help settle a dispute I have with a fellow employee.
In our SQL 2000 server, we have a table called customer.
Customer contains several columns, custid(Primary Key) and is foreign key in another table, Fname(varchar), Lname(varchar), Login(varchar), password(varchar), email(varchar),lastmodified(datetime)
When the table was created a few years ago, there was a clustered index on the custid column because it is the primary key.
Now the table has been modified in the following manner:
The clustered index has been removed on the custid column but still remains the primary key.
A unique constraint has been created on the login and password columns to enforce uniquness.
A no clustered index has been created on the login column.
I have no problem with the unique constraint, but my issue is the removal of the clustered index on the custid. I feel it needs to be clustered, my co-worker disagrees.
I ran a select * from customer to show him the execution plan and SQL does a table scan. I feel it should be using an index scan.
If I am wrong, then it will be a learning experience for me, if he is wrong then He will have to listen to me.
Thanks for the help.
Gary
-
There are a couple of different ways to look at this,
- how many rows are in the table?
- How busy is your server?
- How often is your table referenced?
If there's over 10K rows and/or your table is being referenced a great deal then I'd suggest that it should have a clustered index (to be honest I'd probably put it back regardless but wanted to take you through some of the reasoning behind my decision first).
I'm sure that Frank will have some more of his excellent and seemingly never ending links
that will expain things much better than I ever could 
-
If your table does not have a clustered index it becomes a heap and any table level fragmentation will not be able to be removed, you can defrag the indexes but not the physical table. I've usually found that i/o's for secondary indexes may be less if the table has a clustered index.
I was once told that the optimiser expects tables to have a clustered index, except where they fit in a page.
Depends upon your reason to worry - I'd suggest you look for the worst queries against the table and try changing the indexes and see if it makes a diference.
Note that a clustered index scan = a table scan
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply