CODEWITHSUNDEEP
sql-server-2005ssmssql-server-2008
Chris Simmons
Chris Simmons

Reputation: 7266

Script table as CREATE includes recent column additions as ALTERs?

I'm running SQL Server Management Studio 2008 against a SQL Server 2005 back-end. SSMS just exhibited a behavior I have never seen before. I don't know if this is something new in SSMS 2008 or just a function of something else.

Basically, what happened in that I added some new columns to an existing table. After adding those columns, I executed "Script table as...CREATE" within the IDE on the table. I expected to just get a single CREATE TABLE statement with all the rows, prvious and new. However, the generated code was the CREATE statement for the original definition of the table, plus individual ALTER TABLE T ADD [Column]... statements for each of the new columns.

This isn't a problem (and actually could be useful from a recent change management point-of-view ... sorta), but it is behavior I've never seen before.

I thought that this may have to do with row length, but the length comes in under the 8,000 byte limit before the table page gets split (forgive my terminology ... I'm a developer and not a DBA). Granted, it's not a small table (127 columns now with the additions and a little over 7,000 byte rowlength).

What am I seeing? Is this a feature/function of SSMS or SQL Server itself? Is this a side effect of the large table definition?

The following sample does not repeat the behavior, but it illustrates (simplified) what I'm seeing:

CREATE TABLE dbo.Table_1
(
ID int NOT NULL,
title nvarchar(50) NOT NULL
)

Then,

ALTER TABLE dbo.Table_1 ADD 
[description] [varchar](50) NULL,
[numthing] [nchar](10) NULL

I expected to have this generated:

CREATE TABLE [dbo].[Table_1](
[ID] [int] NOT NULL,
[title] [nvarchar](50) NOT NULL,
[description] [varchar](50) NULL,
[numthing] [nchar](10) NULL,

However, this was generated:

CREATE TABLE [dbo].[Table_1](
[ID] [int] NOT NULL,
[title] [nvarchar](50) NOT NULL)
ALTER TABLE [dbo].[Table_1] ADD [description] [varchar](50) NULL
ALTER TABLE [dbo].[Table_1] ADD [numthing] [nchar](10) NULL

Upvotes: 2

Views: 369

Answers (2)

user7037
user7037

Reputation: 81

I suspect that you "cleaned up" the SQL in your post since it would normally contain many of other SET and GO statements. I am assuming that you removed the "SET ANSI_PADDING" statements.

Some columns in the table may have ANSI_PADDING set to ON while others are OFF. The ANSI_PADDING option affects all columns created after it was set Since the columns are going to be created in table order, the the ANSI_PADDING option will need to be used a few times depending on the table. The real problem is that MS SQL Server cannot set the ANSI_PADDING option within the CREATE TABLE statement. So it needs to do some of that work after the initial CREATE by using ALTER TABLE statements after the appropriate SET ANSI_PADDING statement.

See: http://kevine323.blogspot.com/2011/03/ansipadding-and-scripting-tables-from.html

Upvotes: 1

Stu Pegg
Stu Pegg

Reputation: 1317

I believe you're seeing Microsoft re-using two sections of SQL generation code.

I'm guessing you haven't saved the changes when you click to generate the CREATE script; so the generation code doesn't have an up-to-date version of the table to generate it from. Instead, it runs the normal generation code on the old table, and then the code behind the "Script changes" option to bring it up to date.

Clever code reuse with peculiar results.

Upvotes: 1

Related Questions