none
Changing Clustered Index in Azure

    Question

  • 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.

    Monday, February 22, 2010 2:35 PM

Answers

  • 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
    Tuesday, February 23, 2010 6:16 PM

All replies

  • 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 workaround.
    Lante, shanaolanxing This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, February 23, 2010 6:46 AM
  • 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
    Tuesday, February 23, 2010 6:16 PM
  • 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
    


    Wednesday, June 20, 2012 10:52 AM
  • 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.
    Friday, March 28, 2014 4:09 PM
  • 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) + ')'


    Thursday, July 30, 2015 4:25 AM