Getting error when runs script on Azure sql server but working correctly on local sql server

Question

When I run following query on Azure SQL, it gives me following error:

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

Whereas the script runs perfectly fine on local sql server

Script:

--Script for removing 'IsDeleted' column from all the table and copy the reverse values to the 'IsActive' column
DECLARE @name VARCHAR(50)
DECLARE @TQ VARCHAR(500)
DECLARE @ConstrainName VARCHAR(500)

--Declare cursor and loop it on INFORMATION_SCHEMA.TABLES and get table name in variable @name one by one
DECLARE db_cursor CURSOR
FOR
    SELECT  TABLE_NAME
    FROM    INFORMATION_SCHEMA.TABLES
    WHERE   TABLE_TYPE = 'BASE TABLE'

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0
    BEGIN   --If 'IsDeleTed' column is present
        IF COL_LENGTH(@name, 'IsDeleted') IS NOT NULL
            BEGIN -- If 'IsActive' column is present
                IF COL_LENGTH(@name, 'IsActive') IS NOT NULL
                    BEGIN -- Copy the reverse value from 'IsDeleted' column to 'IsActive'
                        EXEC ('UPDATE '+@name+' SET  [IsActive] = 1 - [IsDeleted]')
                    END      
                ELSE -- If 'IsActive' column is not present
                    BEGIN
                        -- Add column named 'IsActive'
                        EXEC('ALTER TABLE '+@name+' ADD IsActive bit')
                        -- Copy the reverse value from 'IsDeleted' column to 'IsActive'
                        EXEC ('UPDATE '+@name+' SET  [IsActive] = 1 - [IsDeleted]')
                        -- Add default value constraint for newly added column 'IsActive'
                        EXEC('ALTER TABLE '+@name+' ADD CONSTRAINT DF_'+@name+' DEFAULT(1) for [IsActive]')

                    END
                IF EXISTS ( SELECT  *
                            FROM    sysobjects o
                                    INNER JOIN syscolumns c ON o.id = c.cdefault
                                    INNER JOIN sysobjects t ON c.id = t.id
                            WHERE   o.xtype = 'D'
                                    AND c.name = 'IsDeleted'
                                    AND t.name = @name )
                    BEGIN -- If default constraint exist on column 'IsDeleted', get the constraint name
                        SET @ConstrainName = ( SELECT o.name
                                             FROM   sysobjects o
                                                    INNER JOIN syscolumns c ON o.id = c.cdefault
                                                    INNER JOIN sysobjects t ON c.id = t.id
                                             WHERE  o.xtype = 'D'
                                                    AND c.name = 'IsDeleted'
                                                    AND t.name = @name
                                           )
                        -- Drop the default constraint from the column 'IsDeleted'
                        EXEC('ALTER TABLE ' + @name + ' drop constraint ' + @ConstrainName)
                    END
                -- Finally drop the column 'IsDeleted'
                EXEC('ALTER TABLE '+@name+' DROP COLUMN [IsDeleted]')
            END

        FETCH NEXT FROM db_cursor INTO @name   
    END   

CLOSE db_cursor   
DEALLOCATE db_cursor

The above script is simply looping through all the tables in the db, then finding 'IsDeleted' column and replacing it with 'IsActive' column. What changes I need to do in my above query so as to run it on Azure SQL?

I've one table in db which don't have clustered index. Its schema:

--CREATE TEMP TABLE
CREATE TABLE [dbo].[Temp](
    [LayoutId] [int] NOT NULL,
    [UnitTypeId] [int] NOT NULL,
    [ProjectId] [int] NOT NULL,
    [LayoutName] [nvarchar](150) NOT NULL,
    [LayoutDescription] [nvarchar](max) NOT NULL,
    [IsActive] [bit] NOT NULL,
    [IsDeleted] [bit] NOT NULL,
    [CreatedTs] [datetime] NOT NULL,
    [ModifiedTs] [datetime] NULL,

    CONSTRAINT PK_UserGroup PRIMARY KEY NONCLUSTERED ([LayoutId], [ProjectId])
) 

GO

ALTER TABLE [dbo].[Temp] ADD  CONSTRAINT [DF_Temp_IsActive]  DEFAULT ((1)) FOR [IsActive]
GO

ALTER TABLE [dbo].[Temp] ADD  CONSTRAINT [DF_Temp_IsDeleted]  DEFAULT ((0)) FOR [IsDeleted]
GO

ALTER TABLE [dbo].[Temp] ADD  CONSTRAINT [DF_Temp_CreatedTs]  DEFAULT (getdate()) FOR [CreatedTs]
GO

As I don't want the layoutId and ProjectId to be inserted manually, I created composite primary key with non clustered index. I want this table to be like this only. Is the error is because this table don't have clustered index?

Svein Fidjestøl Svein Fidjestøl · Accepted Answer · 2015-02-10T12:16:46

Yes, the error is because the table don't have a clustered index.

From Azure SQL Database General Guidelines and Limitations

Microsoft Azure SQL Database does not support tables without clustered indexes. A table must have a clustered index. If a table is created without a clustered constraint, a clustered index must be created before an insert operation is allowed on the table.

By extension, this also means update operations. So the following dynamically generated SQL in your script will give an error message when executed:

EXEC ('UPDATE '+@name+' SET  [IsActive] = 1 - [IsDeleted]')

Getting error when runs script on Azure sql server but working correctly on local sql server

1 Answers