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