Got a question..... we are busy modifying an existing application to work against SQL Azure, and I'm busy making some performance-related changes.
As part of this, I've got to change some primary keys (which are using clustered indexes on identity columns) to be non-clustered. I know that Azure requires a clustered index on every table, and have been trying to get round this by doing the following:
1. Drop the clustered primary key;
2. Create a clustered index on one or more other columns in the table;
3. Recreate the primary key with the NONCLUSTERED option.
However, Azure won't let me even complete step 1 - I get the "Msg 40054, Level 16, State 2, Line 1 Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again." error.
This is despite there being no data in the table.
Does anybody know of a way to get around this issue, and do what I want to do with the primary key?
Thanx,
Dave.
We do not have support for heap tables. However, one workaround is to create a new table with the index structure you like, move data over and rename the tables in a single transaction.
begin
tran
exec
sp_rename 'db1','db1_old'
exec
sp_rename 'db1_new','db1'
commit
tran
- Proposed as answer by Cihan Biyikoglu Tuesday, February 23, 2010 6:16 PM
- Marked as answer by Yi-Lun Luo Friday, February 26, 2010 5:17 AM
In case it helps anyone, who runs into the same brickwall as me when attempting to using Entity Framework migrations and Primary keys cannot be dropped. Here is a stored proc that copies structure, data, constraints and indexes (not triggers as yet).
create procedure dbo.ChangePK @src sysname, @pklist nvarchar(4000), --comma list of primary key fields @skipfinalrename bit = 1 --set to not perform anything destructive on src table or related tables (for testing) as set nocount on declare @tmpPrefix nvarchar(10) set @tmpPrefix = 'tmp_' declare @dest sysname set @dest = 'tmpCopy' declare @sql nvarchar(max) set @sql = '' --create table script select @sql = @sql + ' IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N''' + @dest + ''') AND OBJECTPROPERTY(id, N''IsTable'') = 1) drop table [' + @dest + ']; create table [' + @dest + '] (' + o.list + ')' + CASE WHEN tc.Constraint_Name IS NULL THEN '' ELSE 'ALTER TABLE ' + @dest + ' ADD CONSTRAINT ' + @tmpPrefix + tc.Constraint_Name + ' PRIMARY KEY ' + ' (' + @pklist + ')' END + ';' from sysobjects so cross apply (SELECT ' ['+column_name+'] ' + data_type + case data_type when 'sql_variant' then '' when 'text' then '' when 'decimal' then '(' + cast(numeric_precision_radix as varchar) + ', ' + cast(numeric_scale as varchar) + ')' else coalesce('('+case when character_maximum_length = -1 then 'MAX' else cast(character_maximum_length as varchar) end +')','') end + ' ' + case when exists ( select id from syscolumns where object_name(id)=so.name and name=column_name and columnproperty(id,name,'IsIdentity') = 1 ) then 'IDENTITY(' + cast(ident_seed(so.name) as varchar) + ',' + cast(ident_incr(so.name) as varchar) + ')' else '' end + ' ' + (case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' + case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END + ', ' from information_schema.columns where table_name = so.name order by ordinal_position FOR XML PATH('')) o (list) cross apply (SELECT ', ['+column_name+'] ' from information_schema.columns where table_name = so.name order by ordinal_position FOR XML PATH('')) c (columnlist) left join information_schema.table_constraints tc on tc.Table_name = so.Name AND tc.Constraint_Type = 'PRIMARY KEY' cross apply (select '[' + Column_Name + '], ' FROM information_schema.key_column_usage kcu WHERE kcu.Constraint_Name = tc.Constraint_Name ORDER BY ORDINAL_POSITION FOR XML PATH('')) j (list) where xtype = 'U' AND name NOT IN ('dtproperties') and name = @src print 'create' print @sql exec sp_executesql @sql --now the inserts set @sql = '' select @sql = @sql + ' set identity_insert [' + @dest + '] on; insert into [' + @dest + '] (' + STUFF(c.columnlist,1,2,'') + ') select ' + STUFF(c.columnlist,1,2,'') + ' from [' + @src + '] ; set identity_insert [' + @dest + '] off;' from sysobjects so cross apply (SELECT ', ['+column_name+'] ' from information_schema.columns where table_name = so.name order by ordinal_position FOR XML PATH('')) c (columnlist) left join information_schema.table_constraints tc on tc.Table_name = so.Name AND tc.Constraint_Type = 'PRIMARY KEY' cross apply (select '[' + Column_Name + '], ' FROM information_schema.key_column_usage kcu WHERE kcu.Constraint_Name = tc.Constraint_Name ORDER BY ORDINAL_POSITION FOR XML PATH('')) j (list) where xtype = 'U' AND name NOT IN ('dtproperties') and name = @src print 'data' print @sql exec sp_executesql @sql --now the foreign keys set @sql = '' select @sql = @sql + case when tc.Constraint_Name is null then '--no foreign keys' else 'ALTER TABLE ' + @dest + ' WITH CHECK ADD CONSTRAINT ' + @tmpPrefix + tc.Constraint_Name + ' FOREIGN KEY ' + ' (' + STUFF(fk1.list,1,2,'') + ') REFERENCES [' + rctc.table_name + '] (' + STUFF(fk2.list,1,2,'') + ');' end from sysobjects so left join information_schema.table_constraints tc on tc.Table_name = so.Name AND tc.Constraint_Type = 'FOREIGN KEY' left join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rcc on tc.constraint_name = rcc.constraint_name left join INFORMATION_SCHEMA.table_constraints rctc on rcc.unique_constraint_name = rctc.constraint_name cross apply (select ', [' + Column_Name + ']' FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu WHERE kcu.Constraint_Name = tc.Constraint_Name ORDER BY ORDINAL_POSITION FOR XML PATH('')) fk1 (list) cross apply (select ', [' + kcu.Column_Name + ']' FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu on rc.unique_constraint_name = kcu.constraint_name WHERE rc.Constraint_Name = tc.Constraint_Name ORDER BY ORDINAL_POSITION FOR XML PATH('')) fk2 (list) where xtype = 'U' and name = @src print 'foreign keys' print @sql exec sp_executesql @sql --now the unique keys set @sql = '' select @sql = @sql + case when tc.Constraint_Name is null then '--no unique keys' else 'ALTER TABLE ' + @dest + ' WITH CHECK ADD CONSTRAINT ' + @tmpPrefix + tc.Constraint_Name + ' UNIQUE NONCLUSTERED ' + ' (' + STUFF(fk1.list,1,2,'') + ');' end from sysobjects so left join information_schema.table_constraints tc on tc.Table_name = so.Name AND tc.Constraint_Type = 'UNIQUE' cross apply (select ', [' + Column_Name + ']' FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu WHERE kcu.Constraint_Name = tc.Constraint_Name ORDER BY ORDINAL_POSITION FOR XML PATH('')) fk1 (list) where xtype = 'U' and name = @src print 'unique keys' print @sql exec sp_executesql @sql --now check constraints set @sql = '' select @sql = @sql + case when tc.Constraint_Name is null then '--no check constraints' else 'ALTER TABLE ' + @dest + ' WITH CHECK ADD CONSTRAINT ' + @tmpPrefix + tc.Constraint_Name + ' CHECK ' + ' (' + cc.check_clause + ');' end from sysobjects so left join information_schema.table_constraints tc on tc.Table_name = so.Name AND tc.Constraint_Type = 'CHECK' left join INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc on cc.Constraint_Name = tc.Constraint_Name where xtype = 'U' and name = @src print 'check constraints' print @sql exec sp_executesql @sql if (@skipfinalrename = 1) return set xact_abort on --now we start affecting the src table begin tran --drop fk constraints on src referencing current primary key set @sql = '' select @sql = @sql + case when tc.Constraint_Name is null then '--no fk constraints to drop' else 'ALTER TABLE ' + rctc.table_name + ' DROP CONSTRAINT ' + rc.Constraint_Name + ';' end from sysobjects so left join information_schema.table_constraints tc on tc.Table_name = so.Name AND tc.constraint_type = 'PRIMARY KEY' left join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc on tc.constraint_name = rc.unique_constraint_name left join information_schema.table_constraints rctc on rc.constraint_name = rctc.constraint_name where xtype = 'U' and name = @src --create fk constraints on dest referencing new primary key declare @sql2 nvarchar(max) set @sql2 = '' select @sql2 = @sql2 + case when tc.Constraint_Name is null then '--no fk constraints to drop' else 'ALTER TABLE [' + rctc.table_name + '] WITH CHECK ADD CONSTRAINT ' + @tmpPrefix + rc.Constraint_Name + ' FOREIGN KEY ' + ' (' + STUFF(fk1.list,1,2,'') + ') REFERENCES [' + @dest + '] (' + STUFF(fk2.list,1,2,'') + ');' end from sysobjects so left join information_schema.table_constraints tc on tc.Table_name = so.Name AND tc.constraint_type = 'PRIMARY KEY' left join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc on tc.constraint_name = rc.unique_constraint_name left join information_schema.table_constraints rctc on rc.constraint_name = rctc.constraint_name cross apply (select ', [' + Column_Name + ']' FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu WHERE kcu.Constraint_Name = rc.Constraint_Name ORDER BY ORDINAL_POSITION FOR XML PATH('')) fk1 (list) cross apply (select ', [' + kcu.Column_Name + ']' FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1 JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu on rc1.unique_constraint_name = kcu.constraint_name WHERE rc1.Constraint_Name = rc.Constraint_Name ORDER BY ORDINAL_POSITION FOR XML PATH('')) fk2 (list) where xtype = 'U' and name = @src ---- print 'create new ref fk' print @sql2 exec sp_executesql @sql2 print 'drop original ref fk' print @sql exec sp_executesql @sql --now we can create the index sql set @sql2 = '' select @sql2 = @sql2 + ' IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''' + @src + ''') AND name = N''' + i.name + ''') CREATE ' + i.type_desc COLLATE Latin1_General_CS_AS + ' INDEX ' + i.name + ' ON [' + @src + '] ( ' + STUFF(ix.list,1,2,'') + ' ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]; ' from sys.tables as t inner join sys.indexes as i on t.[object_id] = i.[object_id] cross apply (select ', [' + ac.name + ']' FROM sys.index_columns ic inner join sys.all_columns as ac on ic.[object_id] = ac.[object_id] and ic.[column_id] = ac.[column_id] WHERE ic.[object_id] = i.[object_id] and ic.[index_id] = I.[index_id] ORDER BY key_ordinal FOR XML PATH('')) ix (list) where t.name = @src --now drop the original table set @sql = 'drop table [' + @src + '];' print 'drop original original' print @sql exec sp_executesql @sql --now rename the constraints (remove leading tmp_ in effect) set @sql='' select @sql = @sql + ' exec sp_rename ''' + tc.constraint_name + ''', ''' + substring(tc.constraint_name, 5, len(tc.constraint_name)-4) + ''';' from sysobjects so left join information_schema.table_constraints tc on tc.Table_name = so.Name where xtype = 'U' and name = @dest print 'rename constraints to original names' print @sql exec sp_executesql @sql --now rename the table back to the original exec sp_rename @dest, @src print 'finally apply the indexes' print @sql2 exec sp_executesql @sql2 print 'finished' commit tran --rollback
One small issue I found with Ian1971's solution is the use of numeric_precision_radix incorrectly - should be numeric_precision when building tables with decimal columns.
I had a table with DECIMAL(19,6), the stored proc created a temp table of DECIMAL(10,6) instead and when inserting into the tmp table, it fails on sufficiently large numbers.
Easy fix, tho - change
when 'decimal' then '(' + cast(information_schema.columns.numeric_precision_radix as varchar) + ', ' + cast(information_schema.columns.numeric_scale as varchar) + ')'
TO
when 'decimal' then '(' + cast(information_schema.columns.numeric_precision as varchar) + ', ' + cast(information_schema.columns.numeric_scale as varchar) + ')'