SQL Azure Error: Tables without a clustered index are not supported in this version of SQL Server

Home | Azure | SQL Azure Error: Tables without a clustered index are not supported in this version of SQL Server

I was trying to execute the following SQL Query in SQL Azure and got this error

Msg 40054, Level 16, State 1, Line 2

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

/****** Object:  Table [dbo].[Department]    Script Date: 2/18/2013 9:42:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Department](
    [DepartmentID] [int] IDENTITY(1,1) NOT NULL,
    [DepartmentName] [varchar](50) NOT NULL,
    [DepartmentCode] [char](5) NOT NULL,

 CONSTRAINT [PK_Department] PRIMARY KEY 
(
    [DepartmentId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)

)


GO
/****** Object:  Table [dbo].[Employee]    Script Date: 2/18/2013 9:42:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employee](
    [EmpID] [int] IDENTITY(1,1) NOT NULL,
    [EmpName] [varchar](50) NOT NULL,
    [EmpAge] [int] NOT NULL,
    [EmpExperience] [decimal](5, 2) NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY
(
    [EmpID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)

I’ve learn that the SQL Azure does not support tables that are without clustered Index. The fix for this is to tweak the above SQL Query to included clustered index.

/****** Object:  Table [dbo].[Department]    Script Date: 2/18/2013 9:42:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Department](
    [DepartmentID] [int] IDENTITY(1,1) NOT NULL,
    [DepartmentName] [varchar](50) NOT NULL,
    [DepartmentCode] [char](5) NOT NULL,

 CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED 
(
    [DepartmentId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)

)


GO
/****** Object:  Table [dbo].[Employee]    Script Date: 2/18/2013 9:42:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employee](
    [EmpID] [int] IDENTITY(1,1) NOT NULL,
    [EmpName] [varchar](50) NOT NULL,
    [EmpAge] [int] NOT NULL,
    [EmpExperience] [decimal](5, 2) NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
    [EmpID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)

The above changes highlighted in yellow has  solved my issue of “

Msg 40054, Level 16, State 1, Line 2

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

 Subscribe to my blog

One thought on “SQL Azure Error: Tables without a clustered index are not supported in this version of SQL Server

Leave a Reply