Hi All

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.

Hello, this is by design. Tables without a clustered index are not supported in SQL Azure. That means you can't create new tables without cluestered indexes, and you cannot drop the existing clustered index on an existing table. There's no

Hi 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

I just wanted to Ian1971 for that proc.  A fully supported and endorsed version should be available as a workaround by the SQL Azure team. I just hadn't taken the time to build it out in such a way.

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) + ')'