April 15, 2004 at 3:02 am
I have read from various SQL Server articles on the issue of avoid adding the same index twice on a table. For example, you add a unique or primary key to an column, which of course creates an index to enforce what you want to happen. But without thinking about it when evaluating the need for indexes on a table, you decide to add a new index, and this new index happens to be on the same column as the unique or primary key.
As a result this will have impact on the performance, according to these articles.
To all experts out there - can anyone provide me with reason(s) as why this practice must be avoided? I have been trying to dig out more info from BOL but could not track down any recommendation to this effect.
Thanks to you all in advance.
Herb
April 15, 2004 at 4:27 am
1) You can only have one PrimaryKey on a table.
2) A PrimaryKey implies uniqueness.
3) Indexes have to be maintained, thus, the more you have, the longer it takes.
4) It makes no sense to have two identical indexes. It's a waste. Just like telefone books. You can't use two of them at the same time and you should ask yourself why have two at all.
While BOL might provide some information on this, a basic book on relational databases might be better suited.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
April 15, 2004 at 5:07 am
Thank you for your thoughts. I hope you can help me further on this as to be more precise...
I have a situation where a column in a table has been made a PK clustered and unique. On the other hand, the same column is also made as a nonclustered. My question is should this be allowed?
Cheers.
H
April 15, 2004 at 5:52 am
CREATE TABLE Double_T
(
col1 INT PRIMARY KEY
)
GO
CREATE INDEX sI ON Double_T(col1)
will run fine. It is allowed but makes no sense.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
April 16, 2004 at 8:11 am
It may be because the data needs to be sorted based on this column, which is why it is clustered. The additional non-clustered index may have been created to enable a covered query. The answer is, it depends on your queries.
April 16, 2004 at 12:32 pm
I'll bet SQL Server will almost always favor the clustered index, because the leaf level actually contains the data not a pointer to the data. If you query only for the primarykey both indexes will *cover* that query, so I don't really think it depends on the queries.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
April 16, 2004 at 1:20 pm
SQL server does favor clustered indexes since they are read 64k at a time instead of 8k at at time and the main table data is also sorted.
However, if the clustered and non-clustered are the same and the non-clustered can cover the query, the non-clustered will be used. You can test this by creating a clusterd and non-clustered on emp_id in the employee table in the pubs database and then run the following two queries.
select * from employee -- uses the clustered
select emp_id from employee -- uses the non-clustered
April 16, 2004 at 1:38 pm
I stand corrected!
You're right, still I think it's a waste.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
April 17, 2004 at 4:44 am
SQL Server will predominantly use what ever is the cheapest in terms of I/O.
Running 'SELECT emp_id FROM Employee' uses the non clustered index at a cost of 1 i/o.
Forcing it to use the clustered index 'select emp_id from employee (index = IX_employee_clustered)' results in 2 i/o.
SQL Server favours the cheapest i/o not clustered over nonclustered or nonclustered over clustered in this case.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply