locked
PK and Clustering index RRS feed

  • Question

  • 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,


    Monday, July 6, 2015 9:43 PM

Answers

All replies

  • Deleted
    • Marked as answer by sharpXO Tuesday, July 7, 2015 5:43 AM
    Monday, July 6, 2015 11:00 PM
  • 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

    Monday, July 6, 2015 11:38 PM
  • 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 /

     

    Amit kr mishra, IT Consltant

    Tuesday, July 7, 2015 12:34 AM
  • 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.  


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Tuesday, July 7, 2015 12:42 AM
    Answerer
  • 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


    Regards, Pradyothana DP. Please Mark This As Helpful if it helps to solve your issue. ========================================================== https://social.technet.microsoft.com/Profile/pradyothanadp http://www.dbainhouse.blogspot.in/

    Tuesday, July 7, 2015 2:55 AM
  • 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 ?


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    • Proposed as answer by Visakh16MVP Tuesday, July 7, 2015 5:30 AM
    Tuesday, July 7, 2015 4:14 AM
    Answerer
  • 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

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, July 7, 2015 5:32 AM