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.
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:
- Create a new table with the new specifications
- Copy all the data over from the existing table to the new table
- 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.
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
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:
- 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
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.