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
Technology Tips and News
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
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.
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/
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/
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:
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.
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.