Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


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.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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

Categories


© Copyright 2020 SQLTraining Ltd.