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:

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.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron