SQL SERVER default primary key, clustered index by default

Move from my Netease blog .....

If SQL SERVER creates a primary key when creating a table without a clustered index, the primary key is a clustered index by default for a long time. Ah. In this way, the clustering index becomes the primary key. It's annoying to modify it ..

After studying for half a day, we found that there are two methods:

First, let's talk about the table I created:

Order details T_DetailOrder

Field name	Description	Data Type	Primary Key
DO_ID	Primary Key	Bigint	Yes
O_ID	Order No.	Bigint
Mname	Dish name	Nvarchar (20)
Mprice	Price	Float

Create an O_ID clustered index for the Order details.

The first method to create a clustered index:

 

After deleting the table, create a new table:

Create a clustered index directly at the time of creation

 create table T_DetailOrder (  DO_ID Bigint identity,  O_ID Bigint,     Mname Nvarchar(20),    Mpirce float, primary key nonclustered (DO_ID),  unique clustered (O_ID) )

Method 2:

Because a primary key exists, the primary key is the clustered index by default. Deleting the clustered index on the primary key fails. You only need to delete the primary key first, then create a primary key, and then create a clustered index.

Obtain the table's primary key: exec sp_pkeys T_DetailOrder. The primary key is PK_T_DetailOrder.

Delete primary key: alter table T_DetailOrder drop constraint PK_T_DetailOrder

Create a primary key and clustering index:

Alter table T_DetailOrder add CONSTRAINT PK_T_DetailOrder primary key nonclustered (DO_ID) <br/> create clustered index O_IDindex on T_DetailOrder (O_ID) <br/>

 

In this way, the O_ID index is created .. Poor SQL SERVER ....