Identity Problems During Database Creation

Question

Using SQL Server Management Studio, my issue stems from a database creation script. The script is written to create a database, many of whose tables have an identity column:

CREATE TABLE Workshop
(
    WorkshopID int IDENTITY,
    WorkshopName varchar(40) NOT NULL,
    Description varchar(800),
    CONSTRAINT PK_Workshop PRIMARY KEY (WorkshopID)
);

My issue is that even with the script plainly creating a column as an identity column, after the script runs none of the columns that should be identity columns actually have that column set to be identity.

To clarify: Running the above code will create that table as specified except WorkshopID will not be an identity column.

What needs to change so that the script will work as written?

Answer 1

FYI, if you generate script for this using SQL Management Studio's designer, this is the resulting script:

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Table_1
    (
    WorkshopID int NOT NULL IDENTITY (1, 1),
    WorkshopName varchar(40) NOT NULL,
    Description varchar(800) NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Table_1 ADD CONSTRAINT
    PK_Table_1 PRIMARY KEY CLUSTERED 
    (
    WorkshopID
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,   ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.Table_1 SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

If you create the table and then script it using the Create To... menu option you get a completely different script:

USE [MyDatabase]
GO

/****** Object:  Table [dbo].[Workshop]    Script Date: 11/27/2012 14:05:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Workshop](
    [WorkshopID] [int] IDENTITY(1,1) NOT NULL,
    [WorkshopName] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Description] [varchar](800) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_Workshop] PRIMARY KEY CLUSTERED 
(
    [WorkshopID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,    ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO