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 IXselect 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 statSELECT '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 columnstoreselect 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 PKselect 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 columnstoreselect 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
Image for post

CreaCity

Articles

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch

Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore

Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store