I am just wondering can we create a Primary key on a table in sql server without any type of index on it?
6
- 1I know abt that.My question was I want to create Pk but not any index on itAnandPhadke– AnandPhadke2012-09-04 07:22:43 +00:00Commented Sep 4, 2012 at 7:22
- 3Or to be pedantic a PK is a logical constraint that is always implemented by a unique index in current versions of SQL Server.Martin Smith– Martin Smith2012-09-04 07:22:53 +00:00Commented Sep 4, 2012 at 7:22
- Well to the public for sure (removing the INDEX via DROP)... but internally it always keeps a unique clustered/composite index to enforce uniqueness. I guess its not a good idea to use a PK without an index. If you must, just use a non PK with UNIQUE flag (but internally it will keep an constraint again...)Najzero– Najzero2012-09-04 07:24:42 +00:00Commented Sep 4, 2012 at 7:24
- Perhaps to help us better understand - I've given two features in my answer of PK features (that also happen to be index features). Could you give an example of a PK feature that you believe could be useful to you that doesn't require (or is separable from) an index?Damien_The_Unbeliever– Damien_The_Unbeliever2012-09-04 07:30:26 +00:00Commented Sep 4, 2012 at 7:30
- Damien_The_Unbeliever -- RIght now I am not having any requirement on this.I was just wondering on thisAnandPhadke– AnandPhadke2012-09-04 07:31:43 +00:00Commented Sep 4, 2012 at 7:31
| Show 1 more comment
3 Answers
No. As an implementation detail, SQL Server maintains the primary key using an index. You cannot prevent it from doing so. The primary key:
- Ensures that no duplicate key values exist
- Allows individual rows to be identified/accessed
SQL Server already has mechanisms that offer these features - unique indexes - so it uses those to enforce the constraint.
answered Sep 4, 2012 at 7:23
Damien_The_Unbeliever
241k28 gold badges358 silver badges470 bronze badges
1 Comment
gstackoverflow
I want to add that by default sql server creates clustered index. You can override this behaviour and server will create non clustered index.
You can create a table with a primary key that is not a clustered index by adding the keyword NONCLUSTERED after the primary key word.
1 Comment
tarzanbappa
Instead of posting just the answer to a question like this. Just provide an example in order to understand well.
Actually indexing functions in the same way a book is traversed. One cannot go to a particular page or topic unless there is page number and their relation to the topics. This "Paging" (ordering) of rows is done physically by Clustered Index in SQL Server. If there is no PK in a table one can add clustered index on any unique-key qualifying column(s). As there cannot be more than one clustered index on a table, and all other non-clustered indices depend on clustered index for search/traversal, you cannot make a column PK without clustered index.
1 Comment
Damien_The_Unbeliever
PK != Clustered index. You can have tables with multiple non-clustered indexes and no clustered index (aka heap). Physical ordering is practically irrelevant, and not wholly determined by the clustered index.