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
Continue reading “Create Clustered ColumnStore index for All tables”
DB Cloud TECH 