Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2016
    Posts
    5

    Answered: SQL2016 ColumnStore Index with previous row CLUSTERED INDEX not primary key

    I've downloaded SQL2016 evaluation and installed an old BI Database.

    Just wondering how I should setup old indexes with new Column Store index, for example:

    Got a table with about 40million rows and have created a Clustered ColumnStore Index, which works well.

    CREATE CLUSTERED COLUMNSTORE INDEX cl_Fact_WarehouseEntry
    ON [dbo].[Fact_WarehouseEntry]
    ON [PRIMARY];
    GO


    The table previously had on it a primary key NON CLUSTERED:

    ALTER TABLE [dbo].[Fact_WarehouseEntry] ADD CONSTRAINT [PK_Fact_WarehouseEntry] PRIMARY KEY NONCLUSTERED
    (
    [BI_WarehouseEntryID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO


    And a also CLUSTERED Index on two columns:

    CREATE CLUSTERED INDEX [NK_Fact_WarehouseEntry] ON [dbo].[Fact_WarehouseEntry]
    (
    [NAV_CompanyID] ASC,
    [NAV Entry No_] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO


    I'm guessing this clustered key NK_Fact_WarehouseEntry was making sure the ETL couldn't insert two identical [NAV_CompanyID] and [NAV Entry No_]?
    Just trying to get my head around it how columnstore should work with old indexes, from what I've found on the net examples are all row based clustered index on primary key.

    Thanks,
    Roger

  2. Best Answer
    Posted by DNance

    "clustering has nothing to do with UNIQUENESS, also, UNIQUENESS doesn't require a PK. The other answer you have to try out for yourself, as mileage will vary.I would say try it without the other index, then create the index and check the performance output of both."


  3. #2
    Join Date
    Oct 2016
    Posts
    5
    I just tried on the old table inserting identical records with same [NAV_CompanyID] and [NAV Entry No_] and it allows it,
    so I suppose this index: CREATE CLUSTERED INDEX [NK_Fact_WarehouseEntry] ON [dbo].[Fact_WarehouseEntry]
    doesn't stop the same record being entered twice only the primary key index.

    So does that mean now I only really would need the one COLUMNSTORE CLUSTERED INDEX to cover all sql queries and also the above row based NON CLUSTERED on Primary key to keep records unique?

  4. #3
    Join Date
    Oct 2007
    Posts
    52
    Provided Answers: 3
    clustering has nothing to do with UNIQUENESS, also, UNIQUENESS doesn't require a PK. The other answer you have to try out for yourself, as mileage will vary.I would say try it without the other index, then create the index and check the performance output of both.

  5. #4
    Join Date
    Oct 2016
    Posts
    5
    Thanks DNance.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •