Indexing
- From what I have learnt about clustered and non clustered indexes hereI needs to have a clustered index on a primary key / identity field of the table and depending on what fields I use in the where condition I add non clustered indexes (only for a couple of most used fields). Is this always the rule of thumb or is the answer "it depends"?The other question I have is if I have a table with 10 fields field1 being identity (with clustered index)If I use field2 in where clause of one query, field3 in where clause of another query, and fields2 and field3 in where clause of a third query, do I keep 1 non clustered index with both field2 and field3 part of it or do I create separate non clustered indexes for both fields.Thanks,Vaibhav
-
Vaibhav, these are difficult questions since there are many answers and different people will have different opinions. In any case though the answer is always "it depends".
As Kimberly Tripp points out it is (often) a good idea to put your clustered index on a column containing ever increasing values. The reason for that is that you avoid page splits. You should note though that the clustered index does not need to be created on the same column as the primary key. In fact if you are going to use identity() (many would argue you should not, see this great blog post for a good discussion) I would say you should definitiely not create the primary key on that column.
If I use field2 in where clause of one query, field3 in where clause of another query, and fields2 and field3 in where clause of a third query, do I keep 1 non clustered index with both field2 and field3 part of it or do I create separate non clustered indexes for both fields.
A single non-clustered index over both columns (column2, column3) would not be enough. It would not be able to be used to answer a query such as
WHERE column3 = xyz. You would need to have another index on column3 alone. SQL Server is pretty smart however and can use two non-clustered indexes on each column separately so you might be better off with two single-column indexes. But it depends on what your priorities are, and how the detailed scenario looks. As always, test different alternatives. -
Cool, so let me ask you this. We have multiple customers and have an identifier in each and every table that identifies the customer. For example if ABC corp is our customer, all records will have the identifier set to abc in all the tables. Since each and every query includes the identifier in the where clause is it a good idea to set this identifier field as Clustered Index even though it is not in increasing values?
Thanks,
Vaibhav
-
As I implied above, it is impossible to give advice without knowing the specifics, because 'it always depends'. We can only make guesses. But the way I understand your question then no, it would normally not be wise, since a non-clustered index can be used just as well to find some rows. The clustered index is something that really needs a lot of thought, and should be created where you think you will get the most benefits from it.
-
I'll add something to this also. I partially base the selection of my clustered indexes on my reindexing strategy. I've got some indexes that I can reindex every night, so as the clustered index gets fragmented it will be resorted that night. If I have a system that can't have the nightly down time, I will sometimes change my indexing strategy to avoid fragmentation.
Also if using a key (Identity) as my clustered index I will set my fill factor to 99% to get as many rows on a page of data since inserts will always come at the end of the file. Indexing strategy is an Art. It takes a lot of trial and error to determine the best way in the varying situations that arise.
Tom
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply