Columnstore A Table
Published: 31st March 2015
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:

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
end


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.
Comments:
NB: Comments will only appear once they have been moderated.