Create Column store index in SQL Server 2012 : Demo




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:

2 comments:

  1. why are you creating non-clustered index as well as column store index in same column, how it will benefit in search?
    CREATE NONClustered INDEX [NCCSI_ID_nonClustered]

    ON [CSI]

    (AGE)

    GO

    CREATE NONCLUSTERED COLUMNSTORE INDEX [NCCSI_age_ColumnStore]

    ON [CSI]

    (age)

    ReplyDelete
    Replies
    1. No use!, Column store is most use when you include more than one column..

      Delete