A few months ago, I wrote about how one might end up with a Message #1701 error when attempting to execute an ALTER TABLE…ALTER COLUMN statement. You can refer the original post here. Vishal Gajjar (blog) recently wrote about the Table Designer component of SSMS. In his post, he draws attention to the fact that altering the data-type of a column causes the Table Designer to re-create the entire table, which is one of the solutions to this problem.

However, the table designer while useful in this case, may not be that useful always. As I try to build my case through the post, do let me know whether the conclusions I derive sound as logical to you as they do to me.

A recap

Assume that we have the following setup:

CREATE DATABASE foo
GO

USE foo
GO
CREATE TABLE MyTbl (MyName CHAR(4030))
GO

INSERT INTO MyTbl
SELECT REPLICATE('a',4030)
GO

Let us assume that we need to modify the structure of our table:

ALTER TABLE MyTbl ALTER COLUMN MyName CHAR(4031)
GO

The result of this would be a Message #1701 error:

Msg 1701, Level 16, State 1, Line 2
Creating or altering table 'MyTbl' failed because the minimum row size would be 8068, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

The Root Cause

The root cause of the error was that the maximum allowed row size in Microsoft SQL Server is 8060 bytes (calculations presented in the original post here).

The ALTER TABLE statement attempts to expand the column “in-place”, i.e. it copies the data over to a new column with the new length specifications on the same row, making the row total now 8061 bytes, which is more than the 8060 maximum – resulting in the error seen.

The Solution – SSMS Table Designer

Besides modifying the storage to have smaller, more granular tables,the only solution I see is to apply the change in the following sequence:

1. Create a new table with the new specifications
2. Copy all the data over from the existing table to the new table
3. Drop the old table and rename the newly created table

Let’s see how the table designer can help us overcome the issue at hand with the row size increasing the 8060-byte limit.

Right-click the table in question and choose “Design” to launch the Table Designer.
In the Designer, simply change the column size.
Script the changes to a file
Save the changes & confirm in the Object Explorer that the size change completed successfully

For those interested, the scripted output of SSMS is as under, with descriptive comments added by me as required to demonstrate the essentials.

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
---Nakul - Step 01: Creating the new table
CREATE TABLE dbo.Tmp_MyTbl
	(
	MyName char(4031) NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_MyTbl SET (LOCK_ESCALATION = TABLE)
GO
---Nakul - Step 02: Pump data from old table to new table
IF EXISTS(SELECT * FROM dbo.MyTbl)
	 EXEC('INSERT INTO dbo.Tmp_MyTbl (MyName)
		SELECT MyName FROM dbo.MyTbl WITH (HOLDLOCK TABLOCKX)')
GO
---Nakul - Step 03: Drop the old table and rename the new table
DROP TABLE dbo.MyTbl
GO
EXECUTE sp_rename N'dbo.Tmp_MyTbl', N'MyTbl', 'OBJECT' 
GO
COMMIT

Conclusion

As we just saw, dropping and recreating tables is the standard scripting mechanism used by the Table Designer. While it does make our life easy, the fact remains that:

1. The Table Designer method is performance intensive
• Assume that we are trying to change the size of a character based column in a table that has a million or more rows
• When using the table designer, we might end up waiting for what would look like infinity, with I/O shooting through the roof and at the end, we would have a bloated transaction log
2. The Table Designer performs a table recreate even if it is not required (while a recreate is essential in our case, it may not be the case when changing a column from NOT NULL to a NULL

The bottom line is: If efficient DDL can be written to make the changes required, avoid using the Table Designers as far as possible.

There is no denying that the designer is supposed to make life simpler, and that’s where I would share my philosophy around these. I use the designers as a stepping stone using the scripts generated as a base, and then trying to improve upon them. After practicing this a couple of times, I was able to churn out pretty decent, standardized DDL without using the designer at all.

Microsoft SQL Server comes with a set of some pretty good designers (Table & Query designer to name a few) which are useful productivity enhancers under most conditions, but might end up causing issues in other cases. Use them with caution.

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here!