We know that Clustered column store index is not supported in SQL server 2012 so I will Create same in my post on sql server 2014 Column store index
Here I am creating non clustered column store indexes with different options in Sql server 2012
1. On Primary Key \ Clustered Index Column
/**********************************************/
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 NONCLUSTERED COLUMNSTORE INDEX [NCCSI_ID_ColumnStore]
ON [CSI]
(ID)
GO
2. On Column With or Without Null
/**********************************************/
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_ID_ColumnStore]
ON [CSI]
(AGE)
GO
3. On Column With Varchar (finite value)
/**********************************************/
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_Name_ColumnStore]
ON [CSI]
(Name)
GO
4. Creating Nonclustered and No clusterd columnstore index on same cloumn
/**********************************************/
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 INDEX [NCCSI_ID_nonClustered]
ON [CSI]
(AGE)
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX [NCCSI_age_ColumnStore]
ON [CSI]
(age)
GO
/**************Command(s) completed successfully*************************/
To find whats features of cloumn store index is not supported in sql 2012 please refer:
why are you creating non-clustered index as well as column store index in same column, how it will benefit in search?
ReplyDeleteCREATE NONClustered INDEX [NCCSI_ID_nonClustered]
ON [CSI]
(AGE)
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX [NCCSI_age_ColumnStore]
ON [CSI]
(age)
No use!, Column store is most use when you include more than one column..
Delete