Create Clustered ColumnStore index for All tables

Problem :

we are working in big project SQL Server 2014 upgrade and SQL Server 2014 Performance this project our role to test the new feature in SQL Server 2014 from the performance wise so we decide  to deploy the new Enhancement in SQL Server 2014 (Clustered ColumnStore index ) CCI so i take backup from one database in my work environment and restored it again with prefix _2014 .

this new type of index to create it on the table this table should be no index on it so i drooped all the index from all the tables but the Problem now How can i create the Clustered columnstore index on all tables( tables supported CCI) by one Click.

Solution :

i do one script to create Clustered columnstore index on all supported tables and here the description of the Script

1- to create CCI on any table we have some limitations one of this limitations the data type of the Columns CCI not supported Some data type.

2- I inserted the data type not supported by CCI in temp Tables

CREATE TABLE #CCI_DataType_Limitation ( DataType NVARCHAR(MAX) )
INSERT INTO #CCI_DataType_Limitation
VALUES ( ‘text’ ),
( ‘timestamp’ ),
( ‘hierarchyid’ ),
( ‘Sql_variant’ ),
( ‘xml’ ),
( ‘geography’ ),
( ‘geometry’ )
Select * from #CCI_DataType_Limitation

3- Selected the tables that’s have this data type Plus i do join with DMV (INFORMATION_SCHEMA.COLUMNS) to Return the tables that’s have columns (Nvarchar) with (CHARACTER_MAXIMUM_LENGTH) = (-1) this meaning this NVARCHAR(MAX) Colustered columns stored index not supported also data type (NVARCHAR(MAX) AND VARCHAR(MAX)).

—-Tables have columns wih data type not supported by CCI
CREATE TABLE #CCI_Tables
(
Tablename NVARCHAR(200)
)
INSERT INTO #CCI_Tables
( Tablename
)
SELECT Distinct T.name TableName
FROM sys.columns AS c
JOIN sys.types AS Y ON c.user_type_id = Y.user_type_id
inner join Sys.tables As T
on T.object_id = C.object_id
Inner join INFORMATION_SCHEMA.COLUMNS INFO
on info.TABLE_NAME = T.name
WHERE Y.name IN ( SELECT DataType
FROM #CCI_DataType_Limitation )

or (Info.DATA_TYPE = ‘nvarchar’ and Info.CHARACTER_MAXIMUM_LENGTH =’-1′)
or (Info.DATA_TYPE = ‘varchar’ and Info.CHARACTER_MAXIMUM_LENGTH =’-1′)
Select * from #CCI_Tables

4- Select the tables that’s have already Clustered ColumnStore Index

—–Tables have already Clustered ColumnStored index

CREATE TABLE #tablehaveCCI
(
TableName NVARCHAR(200)
)
INSERT INTO #tablehaveCCI
( TableName
)
SELECT OBJECT_NAME(Object_ID) AS Tablename
FROM Sys.indexes
WHERE Type_desc = ‘CLUSTERED COLUMNSTORE’
Select * from #tablehaveCCI

5- Create Cursor to loop on the All tables when this tables not in ( the previous points)

Dwonload Demo Script Create CCi on All tables

CREATE TABLE #CCI_DataType_Limitation ( DataType NVARCHAR(MAX) )
INSERT INTO #CCI_DataType_Limitation
VALUES ( ‘text’ ),
( ‘timestamp’ ),
( ‘hierarchyid’ ),
( ‘Sql_variant’ ),
( ‘xml’ ),
( ‘geography’ ),
( ‘geometry’ )
Select * from #CCI_DataType_Limitation
—————————————————————————–
—-Tables have columns wih data type not supported by CCI
CREATE TABLE #CCI_Tables
(
Tablename NVARCHAR(200)
)
INSERT INTO #CCI_Tables
( Tablename
)
SELECT Distinct T.name TableName
FROM sys.columns AS c
JOIN sys.types AS Y ON c.user_type_id = Y.user_type_id
inner join Sys.tables As T
on T.object_id = C.object_id
Inner join INFORMATION_SCHEMA.COLUMNS INFO
on info.TABLE_NAME = T.name
WHERE Y.name IN ( SELECT DataType
FROM #CCI_DataType_Limitation )

or (Info.DATA_TYPE = ‘nvarchar’ and Info.CHARACTER_MAXIMUM_LENGTH =’-1′)
or (Info.DATA_TYPE = ‘varchar’ and Info.CHARACTER_MAXIMUM_LENGTH =’-1′)
Or (Info.DATA_TYPE = ‘varbinary’ and Info.CHARACTER_MAXIMUM_LENGTH =’-1′)
Select * from #CCI_Tables
————————————————————————————–
—–Tables have already Clustered ColumnStored index

CREATE TABLE #tablehaveCCI
(
TableName NVARCHAR(200)
)
INSERT INTO #tablehaveCCI
( TableName
)
SELECT OBJECT_NAME(Object_ID) AS Tablename
FROM Sys.indexes
WHERE Type_desc = ‘CLUSTERED COLUMNSTORE’
Select * from #tablehaveCCI
——————————————————————————————-
—-tables Support CCI and doesn’t have CCI on it.
–SELECT CONCAT(SC.name,’.’,T.name) AS FullTableName
— FROM sys.tables AS T
— Inner Join Sys.schemas AS SC
— On T.schema_id = SC.schema_id
— WHERE T.name NOT IN ( SELECT Tablename
— FROM #CCI_Tables )
— AND T.name NOT IN ( SELECT TableName
— FROM #tablehaveCCI )
————————————————————————————–
—Cursor to create Clustered ColumnStore index on All tables
Declare @SQL Nvarchar(MAX)
Declare @tablename Nvarchar(200)
DECLARE vend_cursor CURSOR
FOR
—-tables Support CCI and doesn’t have CCI on it.
SELECT CONCAT(SC.name,’.’,T.name) AS FullTableName
FROM sys.tables AS T
Inner Join Sys.schemas AS SC
On T.schema_id = SC.schema_id
WHERE T.name NOT IN ( SELECT Tablename
FROM #CCI_Tables )
AND T.name NOT IN ( SELECT TableName
FROM #tablehaveCCI )
OPEN vend_cursor
FETCH NEXT FROM vend_cursor
INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
Set @SQL =’CREATE CLUSTERED COLUMNSTORE INDEX [ClusteredColumnStoreIndex-‘+@tablename+’] ON
‘+@tablename+’ WITH (DROP_EXISTING = OFF)’

–Execute Sp_ExecuteSQL @SQL
Print @SQL

FETCH NEXT FROM vend_cursor INTO @tablename
END

CLOSE vend_cursor
DEALLOCATE vend_cursor
————————————————————-
—Clean
DROP TABLE #CCI_DataType_Limitation
DROP TABLE #CCI_Tables
DROP TABLE #tablehaveCCI