SQL ColumnStore Dönüşümü

ColumnStore’a geçişte kullandığım dönüşüm scriptidir. Öncesinde ntext ve text sütunları ayrı / bağlantılı tablolara almıştım.

-- drop PK and IX
select s.name, t.name, i.name, IIF(i.name LIKE 'PK_%', 'ALTER TABLE ' + t.name + ' DROP CONSTRAINT ' + i.name, 'DROP INDEX ' + i.name + ' ON ' + t.name)
from sys.tables t
inner join sys.schemas s on t.schema_id = s.schema_id
inner join sys.indexes i on i.object_id = t.object_id
where i.index_id > 0
and i.type in (1, 2) -- clustered & nonclustered only
and i.is_unique_constraint = 0 -- do not include UQ
and i.is_disabled = 0
and i.is_hypothetical = 0
and t.name NOT LIKE '%_ft' AND t.name NOT LIKE '%_detay'
order by t.name, i.name
-- drop stat
SELECT 'DROP STATISTICS ' + t.name + '.' + s.name FROM sys.stats s, sys.tables t WHERE s.object_id = t.object_id and s.name NOT LIKE 'PK_%' GROUP BY t.name, s.name ORDER BY t.name, s.name
-- create columnstore
select s.name, t.name, 'CREATE CLUSTERED COLUMNSTORE INDEX CSI_' + t.name + ' ON ' + t.name + ' WITH (DROP_EXISTING = OFF)'
from sys.tables t
inner join sys.schemas s on t.schema_id = s.schema_id
where t.name NOT LIKE '%_ft' AND t.name NOT LIKE '%_detay'
order by t.name
-- create PK
select s.name, t.name, 'ALTER TABLE ' + t.name + ' ADD CONSTRAINT PK_' + t.name + ' PRIMARY KEY NONCLUSTERED (' + IIF(t.name LIKE '%_%', RIGHT(t.name, CHARINDEX('_', REVERSE('_' + t.name)) - 1), t.name) + '_id ASC)'
from sys.tables t
inner join sys.schemas s on t.schema_id = s.schema_id
where (select count(*) from sys.indexes i where i.object_id = t.object_id AND i.name LIKE 'PK_%') = 0
order by t.name
-- drop columnstore
select s.name, t.name, i.name, i.type, 'DROP INDEX IX_' + t.name + ' ON ' + t.name + ' WITH (ONLINE = OFF)'
from sys.tables t
inner join sys.schemas s on t.schema_id = s.schema_id
inner join sys.indexes i on i.object_id = t.object_id
where i.index_id > 0 and i.type = 5
order by t.name, i.name
Like what you read? Give Hunili Yazılımcı a round of applause.

From a quick cheer to a standing ovation, clap to show how much you enjoyed this story.