SQL Server BUG – May cause loss of data – Changing a column from NULLable in to NOT NULL

There is a bug in SQL Server (2008 R2 fully patched) when you change a column from NULLABLE to NOT NULL which can cause loss of data

This bug has been around for ages and I have been caught out by it more than once so I thought I would share with you so that you may prevent data loss.

When you make a change to a table in management studio, it creates a script to do the alteration. This script runs within the management studio error handler if you click the save button.

If you generate the script, rather than clicking save, then you will get exactly the same script that management studio was going to run but this script will not have an error handler and will not always rollback correctly if an error occurs.

Microsoft are aware of this and cover themselves with the following message which they put in all scripts that the designer generates
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/

The key thing to remember when running a script to alter a table schema is to always backup the table. Preferrably backup the database first too but as a minimum always script the table in to a new one to make a data backup in the local DB.

e.g.
SELECT * INTO table_bkp_date FROM table

Do you want to see this happen first hand in a safe environment so that you may prepare for it.

Steps to reproduce…

Create a table with a NULLable integer column

CREATE TABLE [dbo].[Table1](
[id] [int] IDENTITY(1,1) NOT NULL,
      [intcolumn] [int] NULL,
[textcolumn] [varchar](50) NULL
) ON [PRIMARY]

Put some data in to the table with NULL in the integer column

INSERT INTO [SQLServerBug].[dbo].[Table1] ([intcolumn],[textcolumn])
VALUES (NULL,'NULL VALUE')

INSERT INTO [SQLServerBug].[dbo].[Table1] ([intcolumn],[textcolumn])
VALUES (1,'NON NULL VALUE')

Now use the designer to change the NULLable column in to a non-null column with a default of 0

Save – You’ll get an error

‘Table1’ table
– Unable to modify table.
Cannot insert the value NULL into column ‘intcolumn’, table ‘SQLServerBug.dbo.Tmp_Table1’; column does not allow nulls. INSERT fails.
The statement has been terminated.

However, if you generate a script to do this there is no error handler in the script so the error is not caught but due to severeity the script continues and you end up with a slightly altered empty table.

ALL DATA IS LOST

here’s the unaltered script that the management studio creates…

/* 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
CREATE TABLE dbo.Tmp_Table1
(
id int NOT NULL IDENTITY (1, 1),
intcolumn int NOT NULL,
textcolumn varchar(50) NULL
)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_Table1 SET (LOCK_ESCALATION = TABLE)
GO
ALTER TABLE dbo.Tmp_Table1 ADD CONSTRAINT
DF_Table1_intcolumn DEFAULT 0 FOR intcolumn
GO
SET IDENTITY_INSERT dbo.Tmp_Table1 ON
GO
IF EXISTS(SELECT * FROM dbo.Table1)
EXEC('INSERT INTO dbo.Tmp_Table1 (id, intcolumn, textcolumn)
SELECT id, intcolumn, textcolumn FROM dbo.Table1 WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_Table1 OFF
GO
DROP TABLE dbo.Table1
GO
EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
GO
COMMIT

Examine this script – Note the lack of the words ROLLBACK, TRY OR CATCH
Part of the problem here for Microsoft is their use of the statement delimiter GO
With a GO statement delimiter you cannot use a try catch and without a try catch it’s pretty hard to jump to a global rollback. You could get around this with a check after each statement (see older scripts from previous SQL versions)
Here’s what the script does…

  • Makes a temp table called Tmp_Table1 which is a copy of your table.
  • Alters this table with the change you have just requested.

Now for the bug.

  • Attempts to copy your original table data to this new table (which won’t work as your table contains NULL values but it is not going to check for this or any other error when this fails)

Now for the worst part of the bug

  • The script now deletes your original table which is the only place your data is held and then then it will rename the new empty table to the previous name of your table.

Now you have a table with schema changes applied but no data in it.

A better script might have been

BEGIN TRY
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_Table1
(
id int NOT NULL IDENTITY (1, 1),
intcolumn int NOT NULL,
textcolumn varchar(50) NULL
) ON [PRIMARY]
ALTER TABLE dbo.Tmp_Table1 SET (LOCK_ESCALATION = TABLE)
ALTER TABLE dbo.Tmp_Table1 ADD CONSTRAINT
DF_Table1_intcolumn DEFAULT 0 FOR intcolumn
SET IDENTITY_INSERT dbo.Tmp_Table1 ON
IF EXISTS(SELECT * FROM dbo.Table1)
EXEC('INSERT INTO dbo.Tmp_Table1 (id, intcolumn, textcolumn)
SELECT id, intcolumn, textcolumn FROM dbo.Table1 WITH (HOLDLOCK TABLOCKX)')
SET IDENTITY_INSERT dbo.Tmp_Table1 OFF
DROP TABLE dbo.Table1
EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
COMMIT
ROLLBACK
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
Print ERROR_MESSAGE()
END CATCH

Advertisements

About davidbridge

I am a contractor for David Bridge Technology Limited specialising in database design and Web development using Microsoft technologies such as c#, MVC .net and SQL Server (full stack)
This entry was posted in Developer stuff, SQL Stuff and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s