Columnstore A Table
Published: Jan 15, 2017
At some point I will explain the nuances of the Columnstore index, how they work, when and where you should use them, and what they’re for… but in the mean time I have recently had situations in which I have upgraded to SQL 2014 and have needed to Columnstore a table.
This code manages to do that task quite well and therefore I thought I would share it with you all.
A columnstore is a clustered index and therefore cannot be created if you already have non-clustereds or unique references on the table. Likewise it will complain if you have a foreign key. Therefore we need to remove these.
So this is the script I came up with which will remove all constraints and keys and then create a columnstore on your table:
This may well not be perfect so please run in a test environment first, but it’s worked where I’ve needed it. Feel free to comment and adjust as appropriate.
This code manages to do that task quite well and therefore I thought I would share it with you all.
A columnstore is a clustered index and therefore cannot be created if you already have non-clustereds or unique references on the table. Likewise it will complain if you have a foreign key. Therefore we need to remove these.
So this is the script I came up with which will remove all constraints and keys and then create a columnstore on your table:
set nocount on
declare @commands table
(
id int identity,
command varchar(max)
)
declare @tableName varchar(100) = 'sales.salesOrderDetail'
declare @table varchar(100), @schema varchar(100)
select @table = substring(@tableName, charindex('.', @tableName, 1)+1, len(@tableName)),
@schema = substring(@tableName, 1, charindex('.', @tableName, 1)-1)
-- non-clustered indexes
insert into @commands
select 'drop index ' + i.name + ' on ' + @tableName
from sys.indexes i
join sys.objects o
on i.object_id = o.object_id
join sys.schemas s
on o.schema_id = s.schema_id
where o.name = @table
and s.name = @schema
and i.type = 2
and i.is_unique = 0
-- unique constraints
insert into @commands
select 'alter table ' + @tableName + ' drop constraint ' + i.name
from sys.indexes i
join sys.objects o
on i.object_id = o.object_id
join sys.schemas s
on o.schema_id = s.schema_id
where o.name = @table
and s.name = @schema
and i.type = 2
and i.is_unique = 1
-- primary key
insert into @commands
select 'alter table ' + @tableName + ' drop constraint ' + i.name
from sys.indexes i
join sys.objects o
on i.object_id = o.object_id
join sys.schemas s
on o.schema_id = s.schema_id
where o.name = @table
and s.name = @schema
and i.type = 1
-- create clustered columnstore
insert into @commands
select 'create clustered columnstore index c_' + @schema + @table + ' on ' + @tableName
-- Run Commands
declare @sql varchar(max), @counter int = 1
while @counter <= (select max(id) from @commands)
begin
select @sql = command
from @commands
where id = @counter
print @sql
exec(@sql)
select @counter += 1
endThis may well not be perfect so please run in a test environment first, but it’s worked where I’ve needed it. Feel free to comment and adjust as appropriate.