December 8, 2009 at 1:00 pm
I want to know difference between a primary key and a clustered index?
December 8, 2009 at 1:09 pm
Have you Googled it?
Google should give tons of links to explain that.
In short, A PK is a constraint when enforced, would not allow any duplicate values and also does not allow null value(s). By default when a PK is created, a Clustered Index is automatically created on that table with the PK being the Clustered index Key. You can have only 1 PK on a table.
Where as a Clustered index is a type of Index (the other being Non Clustered Index) that would be created on a table causing the table to be reorganized as per the Clustered Index Key. You can have only 1 Clustered Index on a table.
Experts please correct me...
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 8, 2009 at 1:18 pm
A primary key is a constraint that uniquely identifies every row in a table. It forces a unique value for that field of every record. It also helps ensure referential integrity.
A clustered index is first and foremost an index, which helps greatly in sorting data in a table. A clustered index is the most efficient type of index, since the index is at the leaf level of the data pages, but you can only have one clustered index per table (http://blog.sqlauthority.com/2009/06/29/sql-server-maximum-number-of-index-per-table/).
A clustered index is created by default on a primary key field.
_________________________________
seth delconte
http://sqlkeys.com
December 8, 2009 at 1:21 pm
Bru Medishetty (12/8/2009)
By default when a PK is created, a Clustered Index is automatically created on that table with the PK being the Clustered index Key.
Providing there isn't already a clustered index. Providing the keyword NONCLUSTERED is not specified as part of the definition of the primary key.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 8, 2009 at 1:24 pm
Seth Delconte (12/8/2009)
A clustered index is the most efficient type of index, since the index is at the leaf level of the data pages
I'm going to disagree with you there. For queries that retrieve only a portion of the columns in the table, nonclustered indexes are often more efficient, because they are smaller.
If the query is retrieving all the columns then sure, the clustered index is going to be the most efficient.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 8, 2009 at 1:28 pm
Agree with Gail about the Clustered Index. In fact it a common misconception that Clustered Index will always be the best possible Index.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 8, 2009 at 1:29 pm
GilaMonster (12/8/2009)
Bru Medishetty (12/8/2009)
By default when a PK is created, a Clustered Index is automatically created on that table with the PK being the Clustered index Key.Providing there isn't already a clustered index. Providing the keyword NONCLUSTERED is not specified as part of the definition of the primary key.
Yes Gail, "providing there isn't already a clustered index."
I knew something is going to be missed and that's why I added Experts please correct me Keeping you in mind 🙂
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply