Script table as CREATE includes recent column additions as ALTERs?

Question

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

Answer 1

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.