Adding a Not Null Column to a Table in SQL Server

Today one of my colleagues was working on a work item which involved adding a Not Null column to an existing table which had few million records in it already. When I looked at his approach, I somehow felt there should be a more simple approach and in this post, I will outline the approach which he was following and the simple approach that I came up with.

Following are the two approaches which he was following:

Approach 1

• Add a Null Column to a Table
• Update the Column with some dummy data
• Alter the Column to make it Not Null
• Pull the data from Source and Update the newly added Column with actual data. The data coming from the source for this column will be NOT NULL for sure.

However, with this approach, he was facing memory issues due to huge amount of data. Then he mentioned about another approach which he tried, the Approach 2 outlined below.

Approach 2

• Add a Null Column to a Table
• Update the Column with some dummy data
• Alter the Column to make it Not Null
• Pull the data from Source and Update the newly added Column with actual data in Batches. The data coming from the source for this column will be NOT NULL for sure.

With this approach for few tables it worked fine but for few bigger tables he started getting similar memory related issues.

After I looked at the above two approaches, I felt that there should be some simple approach to deal with this problem and I thought it could have been tackled with the help of a Default Constraint. However, I was not quite sure of the syntax [I am not so good at remembering syntaxes ;)]. So I just went up to my best friend (well, most of our best friend), msdn and did some search around the Default Constraint and the result of that is this simple approach as outlined below with the help of a simple script.

Approach 3 (Simple Approach)

-- Create a Sample Table for demo

CREATE TABLE dbo.NotNullColumnAdditionDemo (

ID INT IDENTITY(1,1) NOT NULL,
Name NVARCHAR(255) NOT NULL
)
GO

-- Insert some sample data

INSERT INTO dbo.NotNullColumnAdditionDemo (Name)
SELECT 'Datta' AS Name
UNION ALL
SELECT 'Raghu' AS Name
UNION ALL
SELECT 'Venkat' AS Name
GO

-- Check the data in the table
SELECT * FROM dbo.NotNullColumnAdditionDemo
GO

Now, let us add the new column as NOT NULL along with a DEFAULT Constraint.

-- Alter Table to add a new NOT NULL Column with a Default Constraint
ALTER TABLE dbo.NotNullColumnAdditionDemo
ADD City NVARCHAR(255) NOT NULL CONSTRAINT [DF_NotNullColumnAdditionDemo_City] DEFAULT ('Bengaluru')
GO

-- Check the data in the table
SELECT * FROM dbo.NotNullColumnAdditionDemo
GO

As you can see after executing the previous SELECT Statement, a new column City has been added with a value Bengaluru populated into it for the existing records as well.

Now that we have added the column and we know for sure that the data which will be loaded into this new column will be NOT NULL for sure (Based on the scenario explained above), let's go ahead and drop the Constraint.

-- Drop the Constraint
ALTER TABLE dbo.NotNullColumnAdditionDemo
DROP CONSTRAINT [DF_NotNullColumnAdditionDemo_City]
GO

-- Check the data in the table
SELECT * FROM dbo.NotNullColumnAdditionDemo

GO

That's it !
And the best part about this approach is, it takes very less time to add column, populate default value and drop default constraint when compared to the Approach 1 & Approach 2 outlined above.

Until next time, Happy Reading & Happy Learning :)

Related Article: Working with Default Constraints in SQL Server

Reference: http://msdn.microsoft.com/en-us/library/ms190273.aspx

Readers Feedback:

Did you like this article? If Yes, then please share it: | More Options

Did you like my blog? If Yes, then please help spread the word by sharing this blog with your colleagues, friends, & anyone else in the MSBI Space: Like us on Facebook , Follow us on Twitter .