When one should go for non cluster index for primary key

we know cluster index is created for PK but i heard that we can go for non cluster index for primary key. so i am looking for example scenario when non cluster index will be right choice for PK.

thanks

June 26th, 2015 6:50am

A Primary key will always be created as a clustered index.

You can however have a Unique Index which you can create as a none clustered index.

https://msdn.microsoft.com/en-us/library/ms187019.aspx

Free Windows Admin Tool Kit Click here and download it now
June 26th, 2015 6:53am

please see this link

http://blog.sqlauthority.com/2013/02/10/sql-server-primary-key-and-nonclustered-index-in-simple-words/

we can create PK and specify index as noncluster. i asked when and why people create noncluster index along with PK creation.

related url

http://dba.stackexchange.com/questions/7741/when-should-a-primary-key-be-declared-non-clustered
http://stackoverflow.com/questions/20057108/nonclustered-index-on-primary-key-column
http://www.codeproject.com/Articles/545059/SQL-Server-Primary-Key-and-NonClustered-Index-in-S
http://use-the-index-luke.com/blog/2014-01/unreasonable-defaults-primary-key-clustering-key
https://social.technet.microsoft.com/Forums/sqlserver/en-US/52a3a105-7336-42b8-91c2-e31b15f9c739/difference-between-primary-key-with-clustered-index-and-primary-key-with-nonclustered-index?forum=sqldatawarehousing
http://logicalread.solarwinds.com/sql-server-primary-key-vs-clustered-index-part-1-mb01/#.VY08nFJ4b-Y
http://blogs.lessthandot.com/index.php/datamgmt/dbprogramming/adding-nonclustered-index-on-primary/

June 26th, 2015 7:42am

please see this link

http://blog.sqlauthority.com/2013/02/10/sql-server-primary-key-and-nonclustered-index-in-simple-words/

we can create PK and specify index as noncluster. i asked when and why people create noncluster index along with PK creation.

related url

http://dba.stackexchange.com/questions/7741/when-should-a-primary-key-be-declared-non-clustered
http://stackoverflow.com/questions/20057108/nonclustered-index-on-primary-key-column
http://www.codeproject.com/Articles/545059/SQL-Server-Primary-Key-and-NonClustered-Index-in-S
http://use-the-index-luke.com/blog/2014-01/unreasonable-defaults-primary-key-clustering-key
https://social.technet.microsoft.com/Forums/sqlserver/en-US/52a3a105-7336-42b8-91c2-e31b15f9c739/difference-between-primary-key-with-clustered-index-and-primary-key-with-nonclustered-index?forum=sqldatawarehousing
http://logicalread.solarwinds.com/sql-server-primary-key-vs-clustered-index-part-1-mb01/#.VY08nFJ4b-Y
http://blogs.lessthandot.com/index.php/datamgmt/dbprogramming/adding-nonclustered-index-on-primary/

Free Windows Admin Tool Kit Click here and download it now
June 26th, 2015 11:38am

Not exactly true Tony C -UK You can very well create a Primary key with a non-clustered index. Please see:

https://msdn.microsoft.com/en-us/library/ms189039.aspx

"You can define a primary key in SQL Server 2016 by using SQL Server Management Studio or Transact-SQL. Creating a primary key automatically creates a corresponding unique, clustered or nonclustered index."

See a very good article by Kim Tripp as to when or why you might choose to do so:

http://www.sqlskills.com/blogs/kimberly/more-considerations-for-the-clustering-key-the-clustered-index-debate-continue

June 26th, 2015 1:22pm

i am looking for few sample scenario where people create non-cluster index with PK.
Free Windows Admin Tool Kit Click here and download it now
June 26th, 2015 2:39pm

Scenario 1:

When your Primary key is a GUID that is being generated using the NEWID() function or the .NET application is providing the GUID. This is not the best candidate for a clustered key because it is not a narrow value (4 times larger than an int), nor is it ever increasing (creates fragmentation).

Scenario 2:

The column that you chose to place a Primary Key Constraint on is not necessarily static and may have a tendency to change over time. Not a good a candiate key in that sample scenario

Scenario 3:

The column you have elected to impose a Primary Key Constraint upon is Not of a fixed width because the clustered index gets wider by up to 32 bits.

June 26th, 2015 3:01pm

Why did you mark that answer as answer as it's not an absolute true?
Free Windows Admin Tool Kit Click here and download it now
June 26th, 2015 5:38pm

we know cluster index is created for PK but i heard that we can go for non cluster index for primary key. so i am looking for example scenario when non cluster index will be right choice for PK.

When it is better to cluster on a different column. :-)

As cmcghee says, sometimes can it be outright to cluster a PK, because it is a random value, for instance a GUID.

But there are also situations where some other column simply works better. Say a transactions table. Maybe you often view it per account, and look at a range of transactions, so cluster per account.

Generally, deciding on which column to cluster is an independent choice from determining the primary key. Just because PKs are clustered by default, does not mean that always clustering them is good practice.

June 26th, 2015 5:57pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics