CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2008-r2
Dev
Dev

Reputation: 6720

Create table script adds column with alter statement

I have noticed a strange behavior of SQL Server 2008 R2's "Script Table As.." and "Generate Script" functionality today.

I am having a table in database. And using ALTER query, I had inserted a new column on that existing table.

Now, I want the CREATE TABLE script for the same table. So that I have right clicked on that table and from context menu I selected "Script Table As -> CREATE TO -> New Query Window" option.

And I am surprised by finding an ALTER Table script after CREATE Table Query in New query window.

The result I get in new query window is as below : 

/****** Object:  Table [dbo].[TblMember]    Script Date: 11/23/2013 18:47:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TblMember](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [First_Name] [varchar](50) NULL,
    [Middle_Name] [varchar](50) NULL,
    [Last_Name] [varchar](50) NULL,
    [Gender] [varchar](10) NULL,
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[TblMember] ADD [Religion] [varchar](50) NULL
ALTER TABLE [dbo].[TblMember] ADD [RaceId] [int] NULL
/****** Object:  Index [PK_TblMember]    Script Date: 11/23/2013 18:47:26 ******/
ALTER TABLE [dbo].[TblMember] ADD  CONSTRAINT [PK_TblMember] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Also I have tried "Generate Script", but found the same result.

Can anyone tell me why it is adding an alter query, instead of adding columns directly to the table?

Thanks

Upvotes: 1

Views: 2220

Answers (1)

Alejandro
Alejandro

Reputation: 7819

The difference I see is in the value of SET ANSI_PADDING. The first set of columns where created with SET ANSI_PADDING ON while in the second set it was OFF, note that the SET is the only thing between them. As such, that cannot be scripted in any way in a single statement, it must be split in two.

It's also a recommended practice to always leave SET ANSI_PADDING ON for everything, and often the designer loves to change this and other settings at will, leaving scripts in undefined states. Possibly, the table and its columns were created with different settings by designers at different stages, hence the different settings.

Additionally, there is a further ALTER TABLE that adds a constraint. There is no justification for that, it's always possible to add it together with the columns it affect, but the designers simply do that afterwards in the most inefficient way.

Generally, I prefer to NEVER use the designer, since it's too buggy and unreliable. It's better to learn and write your own scripts and use those.

Upvotes: 2

Related Questions