Creating a primary key… | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Creating a primary key…

A primitive question…[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br />Does creating a primary key creates a clustered index? In my database i created a table with a primary key column. it automatically created a clustered index and it doesnt allow me to drop that index.<br /><br />do we have any setting for this or is this the normal expected behavior???<br /><br />Thanks,<br />Ram<br /><br />"It is easy to write code for a spec and walk in water, provided, both are freezed…"
Hi ya, yes this is expected behaviour it will create a clustered index unless one already existed on the table before you define the primary key. If you don’t want a clustered index created then you need to use the nonclustered keyword when creating the primary key. Check out BOL for more info. You can then only drop the column by first removing the primary key Cheers
Twan
Ooh… didnt know this all these months. thanks for the info… any idea why a clustered index is created? Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"
http://www.sql-server-performance.com/q&a71.asp and also refer Inside SQL 2000 book by Kalen Delaney for complete understanding on indexes. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
A clustered index helps in most cases to greatly improve performance. And because MS obviously think each table should have a clustered index they implemented this a default feature. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
thats cool link satya[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br />thanks frank for the expln. [<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />]<br /><br />Thanks,<br />Ram<br /><br />"It is easy to write code for a spec and walk in water, provided, both are freezed…"
]]>