March 15, 2012 at 5:44 am
Regarding indexes, i always thought the clustering key and the primary key could be different.
In sql server howerver when i create a primary key it automatically creates a clustered index. was my initial assumption incorrect?
March 15, 2012 at 5:59 am
winston Smith (3/15/2012)
Regarding indexes, i always thought the clustering key and the primary key could be different.In sql server howerver when i create a primary key it automatically creates a clustered index. was my initial assumption incorrect?
Your initial assumption is correct, but has some "wrongs" in it.
There are no such thing as "CLUSTERING KEY" in SQL Server as such, but CLUSTERED INDEX.
Key clustering is a term of cryptography 🙂
Yes, SQL Server, by default, will create Clustered Index when creating Primary Key constrain on a table which doesn't already have Clustered Index. Why? Because, I would say in more than 90% of cases, it's exactly what you would want anyway.
When you don't want to have clustered PK, you can:
1. When creating a table in design window of SSMS, define clustered index first, then define PK
2. Define your PK using T-SQL:
create table MyTable
( id int not null
,val varchar(10)
,CONSTRAINT PK_MyTable PRIMARY KEY NONCLUSTERED (id))
Please note: You always want your table to have Clustered Index, as "heap" tables are beasts of hell :hehe:
March 15, 2012 at 6:06 am
Eugene Elutin (3/15/2012)
There are no such thing as "CLUSTERING KEY" in SQL Server as such, but CLUSTERED INDEX.
The term 'clustering key' refers to the key column(s) of the clustered index, while it's maybe not a formal term it is a quite widely used one.
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
March 15, 2012 at 6:16 am
GilaMonster (3/15/2012)
Eugene Elutin (3/15/2012)
There are no such thing as "CLUSTERING KEY" in SQL Server as such, but CLUSTERED INDEX.The term 'clustering key' refers to the key column(s) of the clustered index, while it's maybe not a formal term it is a quite widely used one.
I knew that I will have something from you on this :-).
Yeah, I thought some people could use this term, but for me there is a difference: when I hear "clustered key" I relate it to SQL straight away, but "clustering key" or "key clustering" recalls something about cipher algorithms in cryptography.
But, that is me only 😉
Otherwise, you are absolutely right!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply