Errors With Column Store Index in SQL Server 2012: DEMO



For those who started like me , I am creating Column store index first in SQL Server 2012 and then in SQL server 2014 in my next post. 

Here I will discuss scenarios in which column store index is successful or failed with error
IN 2012:

 1. Creating Clustered Column store index

 This will fail with error "Clustered columnstore index is not supported." because clustered columnstore index was launched in Sql server 2014 (we will see this working in SQL 2014 implementation)

 /***********************************************/
USE [tempdb]
GO
CREATE TABLE [dbo].[CSI]
(
[ID] [int] NOT NULL,
[Name] [varchar](100) NULL,
[Age] [int] NOT NULL,
CONSTRAINT [PK_CSI] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
)
GO
CREATE CLUSTERED COLUMNSTORE INDEX [PKCSI_ID_ColumnStore]
ON [CSI]
(ID)
GO
/***********************************************/




2. Creating Non Clustered Column store index

Non Clustered Columnstore Index Error


1. On Column With Varchar (MAX)
  • With varchar (MAX) / blob / text implementation is not allowed

/*************************************************/
USE [tempdb]
GO
CREATE TABLE [dbo].[CSI]
(
[ID] [int] NOT NULL,
[Name] [varchar](max) NULL,
[Age] [int] NULL,
CONSTRAINT [PK_CSI] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)
)
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX [NCCSI_Name_ColumnStore]
ON [CSI]
(Name)
GO
/**************Fail With Error "Column 'Name' in table is of a type that is invalid for use as a key column in an index."*************************/


2. Multiple Nonclustered Columnstore Indexes On Same or Different Column Is Not Allowed 

/*************************************************/
USE [tempdb]
GO
CREATE TABLE [dbo].[CSI]
(
[ID] [int] NOT NULL,
[Name] [varchar](100) NULL,
[Age] [int] NULL,
CONSTRAINT [PK_CSI] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)
)
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX [NCCSI_age_ColumnStore]
ON [CSI]
(age)
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX [NCCSI_age1_ColumnStore]
ON [CSI]
(age)
GO
/**************Fail With Error "Multiple nonclustered columnstore indexes are not supported."*************************/

3. ColumnStore Index Without The UNIQUE Keyword Is Not Allowed
/*************************************************/
USE [tempdb]
GO
CREATE TABLE [dbo].[CSI]
(
[ID] [int] NOT NULL,
[Name] [varchar](100) NULL,
[Age] [int] NULL,
CONSTRAINT [PK_CSI] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)
)
GO
CREATE Unique NONCLUSTERED COLUMNSTORE INDEX [NCCSI_age_ColumnStore]
ON [CSI]
(age)
GO
/**************Fail With Error "CREATE INDEX statement failed because a columnstore index cannot be unique. Create the columnstore index without the UNIQUE keyword or create a unique index without the COLUMNSTORE keyword."*************************/

4.  Columnstore Index on a View Is Not Allowed
/*************************************************/ 
USE [tempdb]
GO
CREATE TABLE [dbo].[CSI]
(
[ID] [int] NOT NULL,
[Name] [varchar](100) NULL,
[Age] [int] NULL,
CONSTRAINT [PK_CSI] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)
)
GO
CREATE VIEW [dbo].[vw_CSI] as Select * from dbo.CSI
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX [NCvw_CSI_age_ColumnStore]
ON [vw_CSI]
(age)
GO
/**************Fail With Error "CREATE INDEX statement failed because a columnstore index cannot be created on a view. Consider creating a columnstore index on the base table or creating an index without the COLUMNSTORE keyword on the view."*************************/

5. Columnstore Index on a computed column (Persisted \ Non Persisted) Is Not Allowed
/*************************************************/
USE [tempdb]
GO
CREATE TABLE [dbo].[CSI]
(
[ID] [int] NOT NULL,
[Name] [varchar](100) NULL,
[Age] [int] NULL,
CONSTRAINT [PK_CSI] PRIMARY KEY CLUSTERED 
( [ID] ASC )
)
GO
ALTER TABLE [dbo].[CSI] ADD
keyvalue AS (id+' '+age) --- Compute Column
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX [NC_CSI_keyvalue_ColumnStore]
ON [dbo].[CSI]
(keyvalue) 
GO

/**************Fail With Error "CREATE INDEX statement failed because column on table is a computed column and a columnstore index cannot be created on a computed column. Consider creating a nonclustered columnstore index on a subset of columns that does not include the column."*************************/


6. Columnstore Filter Index  Is Not Allowed
/*************************************************/
USE [tempdb]
GO
CREATE TABLE [dbo].[CSI]
(
[ID] [int] NOT NULL,
[Name] [varchar](100) NULL,
[Age] [int] NULL,
CONSTRAINT [PK_CSI] PRIMARY KEY CLUSTERED 
( [ID] ASC )
)
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX [NC_CSI_age_ColumnStore]
ON [dbo].[CSI]
(id) where age > 20
GO

/**************Fail With Error "CREATE INDEX statement failed because a columnstore index cannot be a filtered index. Consider creating a columnstore index without the predicate filter." *************************/

7. Columnstore Index On Sparse Column Is Not Allowed
/*************************************************/
USE [tempdb]
GO
CREATE TABLE [dbo].[CSI]
(
[ID] [int] NOT NULL,
[Name] [varchar](100) SPARSE NULL,
[Age] [int] SPARSE NULL,
CSIKEY XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
CONSTRAINT [PK_CSI] PRIMARY KEY CLUSTERED 
( [ID] ASC )
)
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX [NC_CSI_age_ColumnStore]
ON [dbo].[CSI]
(age) 
GO
/**********Fail With Error "CREATE INDEX statement failed because a columnstore index cannot be created on a sparse column. Consider creating a nonclustered columnstore index on a subset of columns that does not include any sparse columns. "********************/
8. Columnstore Index With Include Clause Is Not Allowed
/*************************************************/
USE [tempdb]
GO
CREATE TABLE [dbo].[CSI]
(
[ID] [int] NOT NULL,
[Name] [varchar](100)  NULL,
[Age] [int]  NULL
CONSTRAINT [PK_CSI] PRIMARY KEY CLUSTERED 
( [ID] ASC )
)
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX [NC_CSI_age_ColumnStore]
ON [dbo].[CSI]
(age) include (name)
GO
/**********Fail With Error "CREATE INDEX statement failed because a columnstore index cannot have included columns.   Create the columnstore index on the desired columns without specifying any included columns. "********************/
9. 8. Columnstore Index With  Is Not Allowed
/*************************************************/
USE [tempdb]
GO
CREATE TABLE [dbo].[CSI]
(
[ID] [int] NOT NULL,
[Name] [varchar](100)  NULL,
[Age] [int]  NULL
CONSTRAINT [PK_CSI] PRIMARY KEY CLUSTERED 
( [ID] ASC )
)
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX [NC_CSI_age_ColumnStore]
ON [dbo].[CSI]
(age) WITH (DATA_COMPRESSION = ROW)
GO
/**********Fail With Error "CREATE INDEX statement failed because specifying DATA_COMPRESSION is not allowed when creating a columnstore index. Consider creating a columnstore index without specifying DATA_COMPRESSION.  Columnstore indexes are always compressed automatically. "********************/
There are Many more features not supported by sql server 2012 column store index. You can read those here 













No comments:

Post a Comment

Subscribe to: Post Comments (Atom)