CODEWITHSUNDEEP
sql-serversql-server-2008
AnandPhadke
AnandPhadke

Reputation: 13506

Primary Key without any index in a table?

I am just wondering can we create a Primary key on a table in sql server without any type of index on it?

Upvotes: 8

Views: 8909

Answers (3)

gust
gust

Reputation: 21

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.

Upvotes: 2

Jitendra Gupta
Jitendra Gupta

Reputation: 824

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.

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239684

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.

Upvotes: 15

Related Questions