StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POScript table as CREATE includes recent column additions as ALTERs?
text
Body
copied!<p>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.</p> <p>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 <code>ALTER TABLE T ADD [Column]...</code> statements for each of the new columns.</p> <p>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.</p> <p>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).</p> <p>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?</p> <p>The following sample does not repeat the behavior, but it illustrates (simplified) what I'm seeing:</p> <pre><code>CREATE TABLE dbo.Table_1 ( ID int NOT NULL, title nvarchar(50) NOT NULL ) </code></pre> <p>Then,</p> <pre><code>ALTER TABLE dbo.Table_1 ADD [description] [varchar](50) NULL, [numthing] [nchar](10) NULL </code></pre> <p>I expected to have this generated:</p> <pre><code>CREATE TABLE [dbo].[Table_1]( [ID] [int] NOT NULL, [title] [nvarchar](50) NOT NULL, [description] [varchar](50) NULL, [numthing] [nchar](10) NULL, </code></pre> <p>However, this was generated:</p> <pre><code>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 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload