Did You Know? Management Studio Got Smarter!

Kalen Delaney — Fri, 23 Jan 2009 22:32:00 GMT

Hopefully you're aware that there is no way using the ALTER TABLE statement to add a new column in a particular ordinal position within a table. I see people in the forums asking for this capability, and they may point out that it is possible to do this using the Table Designer in Management Studio. However, usually someone will point out to them that you're really not altering the table in that case, you're actually creating a whole new table. And if it's a BIG table, it can take a long time, because all the data has to be moved to the table with the new definition, and then all the indexes have to be rebuilt. You can do the same using TSQL statements, but it's not a single quick and fast operation.

I just discovered today that SQL Server 2008 Management Studio is a bit smarter about this. You are actually not allowed to save a change to a table that will require the table be rebuilt. You can, however, change that behavior to allow the change, as in older versions. I also discovered that this new behavior had already been discovered, and blogged about, by the awesome Brian Knight at

http://pragmaticworks.com/community/blogs/brianknight/archive/2008/06/04/sql-server-2008-designer-behavior-change-saving-changes-not-permitted.aspx

Brian shows you the error message, and the the dialog to change the behavior.

So after reading Brian's post, I did some quick tests to verify that after changing the option, I could indeed make changes to a table that required a rebuild.

Then when I was getting ready to blog this, I thought I would generate my own screenshot of the error message. So I used Table Designer to remove the identity property from the AdventureWorks.Sales.SalesOrderHeader.SalesOrderID column and tried to save the table. I received a warning, but not the one I expected. It told me that half a dozen other tables would have to be changed because of my change to SalesOrderHeader. I presumed the message about not be able to actually save the change that required rebuilding the table would come next... so I clicked OK. Oops, it didn't complain. Because I had turned off the option to prevent saving such changes, and I never turned it back on again.

Wasn't there a thread going around a while ago about goofs people had made?

Oh, well. At least I have the ability to re-create AdventureWorks.

Have fun!

~Kalen

Did you know? -- Altering the length of a fixed-length column

Kalen Delaney — Fri, 13 Oct 2006 16:33:00 GMT

You may be well aware that if you alter the length of a column to a larger value, that SQL Server doesn’t go through all the rows at the time you issue the ALTER. The ALTER is a metadata only change, and the actual movement of data doesn’t happen until a row is updated.

However, SQL Server does not reuse the original bytes in the row when a row’s length is altered. You may end up not being able to use the full allowable row length if you’ve altered column lengths.

Here’s the example. Create a table with two large char columns and a smallint. The initial length of each row will be just a bit more than 3000 bytes, out of a maximum row length of 8060 bytes.

CREATE TABLE bigchange

(col1 smallint, col2 char(2000), col3 char(1000));

Now alter the second column to increase its length to 3000 bytes. The length of a row should now be just a bit more than 4000 bytes.

ALTER TABLE bigchange

ALTER COLUMN col2 char(3000);

Now try and add another 3000 byte column, which should bring the length to just over 7000 bytes. This will fail:

ALTER TABLE bigchange

ADD col4 char(3000);

Msg 1701, Level 16, State 1, Line 1

Creating or altering table 'bigchange' failed because the minimum row size would be 9009, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

However, if you just create a table with two 3000-byte columns and a 1000-byte column, there will be no problem.

CREATE TABLE nochange

(col1 smallint, col2 char(3000), col3 char(1000), col4 char(3000));

Here’s a query you can run to see the metadata for where each column will start. Run it against the bigchange table:

SELECT c.name AS column_name, column_id, max_inrow_length,
pc.system_type_id, leaf_offset

FROM sys.system_internals_partition_columns pc

JOIN sys.partitions p

ON p.partition_id = pc.partition_id

JOIN sys.columns c

ON column_id = partition_column_id

AND c.object_id = p.object_id

WHERE p.object_id=object_id('bigchange');

Here’s my results:

column_name column_id max_inrow_length system_type_id leaf_offset

------------- ----------- ---------------- -------------- -----------

col1 1 2 52 4

col2 2 3000 175 3006

col3 3 1000 175 2006

Note that col1 is only 2 bytes long, and starts at offset 4 (after the row header information) but that the next column doesn’t start until offset 2006. Bytes 6 – 2005, the original 2000 bytes for col2, are not used for anything now.

So be careful when using large datatypes, especially if you want to make them fixed length instead of variable length.

-- Kalen

Kalen Delaney : ALTER TABLE

Did You Know? Management Studio Got Smarter!

Did you know? -- Altering the length of a fixed-length column