Advertisements
Home > SQL Server 2014, SQL Tips > Maintaining Uniqueness with Clustered ColumnStore Index | SQL Server 2014

Maintaining Uniqueness with Clustered ColumnStore Index | SQL Server 2014


Column Store indexes were introduced in SQL Server 2012 with a flavor of Non-Clustered index i.e. “Non-Clustered ColumnStore” index. However there is a big limitation that the underlying table becomes read-only as soon as you create one.

In SQL Server 2014 this behavior is unchanged and addition to this you can also create ColumnStore index as a Clustered index. And the good thing is that the table having “Clustered ColumnStore” index can also be updated. However there is one more big limitation here that there is no Clustered Key with this type if index, thus risking the Uniqueness in the table.
 

–> Here we will see this limitation and a workaround which can be used in some scenarios:

USE tempdb
GO

-- Create a simple table with 3 columns having 1st column to contain Unique values:
CREATE TABLE dbo.TableWithCCI
(
	PKCol int NOT NULL,
	Foo int,
	Bar int
)
GO

-- Now create a "Clustered ColumnStore" index on this table:
CREATE CLUSTERED COLUMNSTORE INDEX CCI_TableWithCCI ON dbo.TableWithCCI
GO

Notice: While creating this index there is no provision to provided the “Clustering Key”, as this index includes all of the columns in the table, and stores the entire table by compressing the data and store by column.

On checking the metadata (by ALT+F1) of the table, you will see NULL under the index_keys column:

SQLServer2014_Unique_CCI
 

– Now let’s check this feature of absence of Uniquenes. We will enter 2 records with same value:

insert into dbo.TableWithCCI
select 1,2,3

insert into dbo.TableWithCCI
select 1,22,33
GO

SELECT * FROM dbo.TableWithCCI
GO

You will see 2 records with same duplicate value.
 

– Now, let’s create another Unique index to enforce this constraint:

CREATE UNIQUE INDEX UX_TableWithCCI ON dbo.TableWithCCI(PKCol)
GO

We get an error that you cannot create more indexes if you have a Clustered ColumnStore index:

Msg 35303, Level 16, State 1, Line 25
CREATE INDEX statement failed because a nonclustered index cannot be created on a table that has a clustered columnstore index. Consider replacing the clustered columnstore index with a nonclustered columnstore index.

 

–> Workaround: As a workaround we can create an Indexed/Materialized View on top this table, with Clustering Key as the PK (1st column of the table/view):

CREATE VIEW dbo.vwTableWithCCI 
	WITH SCHEMABINDING
AS 
	SELECT PKCol, Foo, Bar
	FROM dbo.TableWithCCI
GO

-- Delete duplicate records entered previously:
DELETE FROM dbo.TableWithCCI
GO

-- Create a Unique Clustered Index on top of the View to Materialize it:
CREATE UNIQUE CLUSTERED INDEX IDX_vwTableWithCCI ON dbo.vwTableWithCCI(PKCol)
GO

– Now let’s try to enter duplicate records again and see if these can be entered or not:

insert into dbo.TableWithCCI
select 1,2,3

insert into dbo.TableWithCCI
select 1,22,33
GO

– As expected we get an error after we inserted 1st records and tried to insert the 2nd duplicate record:

(1 row(s) affected)
Msg 2601, Level 14, State 1, Line 48
Cannot insert duplicate key row in object ‘dbo.vwTableWithCCI’ with unique index ‘IDX_vwTableWithCCI’. The duplicate key value is (1).
The statement has been terminated.

–> Not sure why Microsoft has put this limitation of not maintaining the Uniqueness with these indexes. While using this workaround you need to consider this approach if possible. Like in some scenarios where the table is very big and there are frequent updates (INSERT/UPDATE/DELETES) this approach of maintaining another Indexed-View would be expensive. So this approach should be evaluated before implementing.
 

-- Final Cleanup:
DROP VIEW dbo.vwTableWithCCI
GO
DROP TABLE dbo.TableWithCCI
GO

 

I look forward in new versions of SQL Server to address this limitation.
You can also refer to MSDN BOL [here] for checking all limitations with ColumnStore Indexes.
 

[Update as of May-2015] with SQL Server 2016 you can make unique Clustered ColumnStore Index indirectly by creating Primary/Unique Key Constraint on a heap with a Non-Clustered Index, [check here].
 

Update: Know on ColumnStore Indexes as of SQL Server 2016:


 

Check & Like my FB Page


Advertisements
  1. Suman
    April 7, 2015 at 3:32 am

    Any idea how to find the number of rows going into delta store.
    I am seeing a performance issue with bulk load in SSIS, but the same works real fast while running a INSERT statement.

    • April 7, 2015 at 10:38 am

      You can check the number of rows going into delta-store by using this query:

      select *
      from sys.column_store_row_groups
      where object_id = object_id(‘your_table_name’)

      How many rows you are inserting by Bulk load? You are seeing slow performance may be because you are having more than 100k rows and thus bulk load is bypassing the delta-store and directly inserting into compressed rows.

  2. DrBob
    February 19, 2015 at 6:29 pm

    Creating the Cluster index, the Unique Key is also created. There are nothing like Cluster Key in SQL. The Index on top of View are working like index on “results” not directly as an index on the table/s used by view. Updating the view check all below table constraints. This is not bug. you have missunderstand the concept.

    • February 22, 2015 at 5:29 pm

      @DrBob, thanks for your reply.

      I think you didn’t read my article completely, I’m talking about new “ColumnStore” clustered Indexes introduced in SQL2014, they are non-unique unlike normal Clustered Indexes.

      Thanks,
      Manoj

  1. August 11, 2014 at 2:20 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: