Hi, I want to have table with 1 PK and 1 Clustered Index on Column2 and Id, this is my code:

CREATE TABLE [dbo].[Test] (
    [Id]        INT PRIMARY KEY NOT NULL IDENTITY(1,1),
    [Column2]   INT NOT NULL,
    [Column3]   INT NOT NULL,
    [Column4]   INT NOT NULL,   
);

CREATE CLUSTERED INDEX IX_Test ON dbo.Test (Column2, Id);

Error: "Cannot create more than one clustered index on table..."

I know, that PRIMARY KEY automatically create Clustered index on Id.

Then I manually delete existing Clustered index, and run this code part again:

CREATE CLUSTERED INDEX IX_Test ON dbo.Test (Column2, Id);

Everything is fine, except I lost my PK on Id...

How can I leave PK on Id and create my custom Clustering Index?

Thanks,

"_developer", you can't create more than one clustered index per table.

• CREATE INDEX
  
  
  

Try

CREATE nonclustered INDEX IX_Test on dbo.Test (Column2, Id);

or

CREATE unique nonclustered INDEX IX_Test on dbo.Test (Column2, Id);

 

or

-- code #1
CREATE TABLE [dbo].[Test] (
    [Id]        INT NOT NULL IDENTITY(1,1),
    [Column2]   INT NOT NULL,
    [Column3]   INT NOT NULL,
    [Column4]   INT NOT NULL,   
    constraint PK_Test primary key nonclustered (Id)
);

CREATE clustered INDEX IX_Test on dbo.Test (Column2, Id);

Hello,

Try this example if it helps.

CREATE TABLE [dbo].[TestTable] (

    [Id]        UNIQUEIDENTIFIER NOT NULL,

    [FirstName] NVARCHAR (10)    NOT NULL,

    [LastName]  NVARCHAR (10)    NOT NULL,

    [Type]      INT              NOT NULL,

    [Timestamp] ROWVERSION       NOT NULL,

    PRIMARY KEY NONCLUSTERED (Id));

CREATE CLUSTERED INDEX IX_TestTable on TestTable (FirstName, LastName);

 

Thanks

---------------------------------------------------------------------------------------------------------------

Please Mark This As Answer if it solved your issue. Please Vote This As Helpful if it helps to solve your issue

try this way


CREATE TABLE TblTest (
    Col1 int    identity(1,1) NOT NULL,
    Col2 VARCHAR(10)    NOT NULL,
    Col3 VARCHAR(10)    NOT NULL,

    PRIMARY KEY NONCLUSTERED (Col1)); 
CREATE CLUSTERED INDEX IX_TblTest on TblTest (Col1, Col2);

This script will create a PK as well as a cluster index /

 

When you create a primary key without an explicit clustered/nonclustered specification, the default primary key index is clustered unless a clustered index already exists on the table.  I suggest you make habit of explicitly specify the desired type of index when creating constraints and indexes to avoid ambiguity.  

When you create a PRIMARY KEY constraint, a unique clustered index on the column or columns is automatically created if a clustered index on the table does not already exist and you do not specify a unique nonclustered index. The primary key column cannot allow NULL values.

https://msdn.microsoft.com/en-us/library/ms186342.aspx

First please note that you can create Both Clustered and Non clustered index on PK. You only have to specify what kind of index you want when defining primary key if you will not define it will automatically create CI.

Why do you want to have PK on both ID and col 2 any specific requirement ?

As I understand what you want to do is to create the PK on Id specifying Non clustered as option and create the clustered index on Column2