CodeCloudy

Azure | .Net | JQuery | Javascript | Umbraco

Sql azure blindly adding bulk clustered indexes

In a previous post i mentioned when migrating to SQL Azure you have to create clustered indexes for each of the table that doesn’t have a clustered index (heap tables).

the error you will get is:

Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again.

Lets say you have a huge database with huge amount of heap tables. And you need to do a blind test by adding a clustered index for the first column of each table.

Step 1:

Check the heap tables you have:

— List all heap tables
SELECT SCH.name + ‘.’ + TBL.name AS TableName
FROM sys.tables AS TBL
INNER JOIN sys.schemas AS SCH
ON TBL.schema_id = SCH.schema_id
INNER JOIN sys.indexes AS IDX
ON TBL.object_id = IDX.object_id
AND IDX.type = 0 — = Heap
ORDER BY TableName

Step 2:

declare @SQL nvarchar(max)
SELECT @SQL = STUFF((
SELECT ‘; CREATE CLUSTERED INDEX NewPK_’ + TBL.name + ‘ ON ‘ + ‘[‘+TBL.name+’]’ + ‘ (‘+(
sELECT top 1 ‘[‘+column_name+’]’ FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = TBL.name
)+ ‘)’

— List all heap tables
FROM sys.tables AS TBL
INNER JOIN sys.schemas AS SCH
ON TBL.schema_id = SCH.schema_id
INNER JOIN sys.indexes AS IDX
ON TBL.object_id = IDX.object_id
AND IDX.type = 0 — = Heap
ORDER BY TBL.name

FOR XML PATH(”)),1,2,”)
SET @SQL = ‘ ‘ + @SQL
PRINT @SQL — execute until this statement to see only the script to be executed.

EXECUTE (@SQL) — execute until this statement to actually execute the script

NOTE: this is only good for a blind test. but if you are going to move forward. you need to consider the tables case by case and create the clustered indexes for the suitable columns or else it will bring huge performance issues.

Advertisements
Leave a comment »