May 2, 2012 at 9:21 am
Hi
I have a table with 3 columns, say Col1,Col2 and Col3.
Values in these columns repeat, So I can't create a Primary Key here.
There are some queries which uses where Col1='somevalue' And Col2='somevalue'
And then there are some which use where Col2='somevalue'
select caluse always have Col3.
So I thought of creating 2 non clustered indexes on this heap.
idx1 => (col1,Col2) include Col3
idx2 => (Col2) include Col3
Now will it benefit me if I create an ID identity column in table and make it PK?
Basicall the questions boils down to is there any benefit of creating an extra identity column to make it clustered index and then create non clustered indexes?
We know that ID column will never be used anywhere.
Table has more than 100000 rows.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
May 2, 2012 at 9:25 am
This was removed by the editor as SPAM
May 2, 2012 at 9:26 am
No, there are no range searches
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
May 2, 2012 at 10:45 am
I would not create an ID column just so that you can add a PK and make it clustered.
However, I would probably make one of the other two indexes into a clustered index. Depending on the data types, probably the one with the compound key, but I'm just guessing since I don't know the data distribution or data types involved.
I'm inclined to make a cluster out of one of the indexes because SQL Server stores data a little more efficiently on a clustered index. You'll be able to defragment the data more easily with a cluster in place. You will have to deal with the addition of a uniqueifier behind the scenes which should be part of how you choose the index (hence my leaning towards the key with two columns). In general, testing suggests, there is a slight advantage to a table with a clustered index over a heap table. Obviously there are exceptions, but not knowing more than I know, I'd suggest making one of the indexes a cluster.
"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
May 2, 2012 at 10:54 am
If those are the only ways you access the table, then a clustered index on col2, col1 would cover all your lookups with no need for additional indexes.
However, I would want to know more about the distribution of values in the columns before saying that was the way to go. If col2 is low cardinality, then it may not be the best choice.
I always recommend a clustered index because a heap table can have problems with failing to deallocate empty pages when you delete data.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply