CODEWITHSUNDEEP
sql-serverprimary-key
Marius
Marius

Reputation: 1

Primary keys without defaul index (sort) - SQL2005

How do I switch off the default index on primary keys I dont want all my tables to be indexed (sorted) but they must have a primary key

Upvotes: 0

Views: 620

Answers (4)

iDevlop
iDevlop

Reputation: 25262

What does " I dont want all my tables to be sorted" mean ? If it means that you want the rows to appear in the order where they've been entered, there's only one way to garantee it: have a field that stores that order (or the time if you don't have a lot of transactions). And in that case, you will want to have a clustered index on that field for best performance.
You might end up with a non clustered PK (like the productId) AND a clustered unique index on your autonumber_or_timestamp field for max performance.
But that's really depending on the reality your're trying to model, and your question contains too little information about this. DB design is NOT abstract thinking.

Upvotes: 0

nvogel
nvogel

Reputation: 25526

Tables are always unsorted - there is no "default" order for a table and the optimiser may or may not choose to use an index if one exists.

In SQL Server an index is effectively the only way to implement a key. You get a choice between clustered or nonclustered indexes - that is all.

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239694

The means by which SQL Server implements Primary and Unique keys is by placing an index on those columns. So you cannot have a Primary Key (or Unique constraint) without an index.

You can tell SQL Server to use a nonclustered index to implement these indexes. If there are only nonclustered indexes on a table (or no indexes at all), you have a heap. It's pretty rare that this is what you actually want.

Just because a table has a clustered index, this in no way indicates that the rows of the table will be returned in the "order" defined by such an index - the fact that the rows are usually returned in that order is an implementation quirk.

And the actual code would be:

CREATE TABLE T (
    Column1 char(1) not null,
    Column2 char(1) not null,
    Column3 char(1) not null,
    constraint PK_T PRIMARY KEY NONCLUSTERED (Column2,Column3)
)

Upvotes: 0

Phil Hunt
Phil Hunt

Reputation: 8531

You can define a primary key index as NONCLUSTERED to prevent the table rows from being ordered according to the primary key, but you cannot define a primary key without some associated index.

Upvotes: 1

Related Questions