SQL Server Performance Forum – Threads Archive
create index in SQL 2005
Dear All, Need informaton about, what is the difference of the result when i implemented these two conditions:1. i create an clustered index for column A, B, C, D in a table, and
2. i create an clustreded index with column A, B and create a non clustered index with column C, D where of these two conditions is more appropriate? and then when i create an unique index for column A and B, should the data for A and B within the table unique too?
so what is the different if i made column A and B as a primary keys? thx in advance for the knowledge sharing guys…
http://www.sql-server-performance.com/gv_clustered_indexes.asp
http://www.sql-server-performance.com/optimizing_indexes.asp
http://www.sql-server-performance.com/nonclustered_indexes.asp
MohammedU.
Moderator
SQL-Server-Performance.com
One more clarification on this issue Can a clustered index be created on non-PK colums? If yes, is it a good practise? Thanks
Satya
Yes, clustered index can be on non-PK and non-unique columns…
It is always better to create the clustered on unique columns(high selectivity columns)…
If you create a clustered index on non-unique columns, sql server will uniquify the column interanally…
MohammedU.
Moderator
SQL-Server-Performance.com
so, if we back to my question, where is the appropriate method? then the answer is depend on whether column A – D is a an unique column or not, is it right?
Originally posted by MohammedU
Yes, clustered index can be on non-PK and non-unique columns…
It is always better to create the clustered on unique columns(high selectivity columns)…
If you create a clustered index on non-unique columns, sql server will uniquify the column interanally…
It is always better to create the clustered on unique columns(high selectivity columns)…
If you create a clustered index on non-unique columns, sql server will uniquify the column interanally…
It depends…polital answer<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ /><br />It depends on selectivity and the queries/procedures…<br /><br />I will not go with 4 column clustered index…<br /><br /><br /><br />MohammedU.<br />Moderator<br />SQL-Server-Performance.com
http://www.sql-server-performance.com/mr_indexing.asp Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
]]>