SQL Server is a very interesting system, but the people who work in SQL Server are even more remarkable. The amount of communication, the thought process, the brainstorming that they do are always phenomenal. Today I will share a quick conversation I have observed in one of the organizations that I recently visited.
While we were heading to the conference room, we passed by some developers and I noticed the following script on the screen of one of the developers.
CREATE TABLE TestTable
(FirstCol INT NOT NULL)
GO
------------------------------
-- Option 1
------------------------------
-- Adding New Column
ALTER TABLE TestTable
ADD SecondCol INT
GO
-- Updating it with Default
UPDATE TestTable
SET SecondCol = 0
GO
-- Alter
ALTER TABLE TestTable
ALTER COLUMN SecondCol INT NOT NULL
GO
Curious, I asked why he wrote such a long script. He replied with another question, asking whether there is any existing short method that he can use to add a column which is not null and can be populated with some specific values.
Of course! The method exists and here is the quick script. When he learned it he said, “Well, I searched at your blog but it was not there.”
------------------------------
-- Option 2
------------------------------
-- Adding Value with Default Value
ALTER TABLE TestTable
ADD ThirdCol INT NOT NULL DEFAULT(0)
GO
Well, now it’s in my blog.
Reference: Pinal Dave (https://blog.sqlauthority.com)
41 Comments. Leave new
Thanks for the query, its very simple and helpful.
ISOLIS: how can i alter an existing column??? i need to define 1 like a default value
CREATE TABLE [dbo].[MyTable](
[C1] [nchar](10) NULL,
[C2] [nchar](10) NULL
) ON [PRIMARY]
GO
— Adding default value
ALTER TABLE dbo.MyTable ADD CONSTRAINT
DF_MyTable_C2 DEFAULT N’Pinal’ FOR C2
GO
Hi Pinal,
ALTER TABLE TestTable
ADD ThirdCol INT NOT NULL DEFAULT(0)
What happened if the table have millions of records, is it take long time to process
Thanks, just used this for the default for an nvarchar and it worked perfectly :)
DEFAULT(‘my default text’)
Hi, returning back to original article above i think your solution with single line script and ‘default’ constraint is bad. The three line script is correct because you don’t want to add default constraint. Just only need to set some value into new columns in existing rows. You don’t want the server to add default value if incomplete insert is sent.
Hi,
I did it as following:
create table test (id varchar(3));
insert into test values (‘aaa’);
select * from test;
alter table test add test01 int null default 0
After executing commands above, I found the default value of test01 is not 0, null instead.
Thanks,
Hi Pinal. A lot of people read your blog, so please help them to get into the good habit of naming their constraints. If you don’t name them, SQL Server chooses a name for you, and you end up with an ugly alphabet soup in sys.objects of constraints whose purpose is not self-evident. Worse, if you execute the same script in several environments, you’ll have a different name for the constraint in each. Worse still, if you don’t know the name of the constraint at design time, how are you going to write a script to drop it (especially if it’s different in all your environments)? A case in point is dhanalakshmi’s question posted on May 14, 2013.
Using your own example, here’s how easy it is. You don’t have to follow my naming convention, of course, but choose something that works for you and makes it easy to identify by sight which constraint relates to which column on which table.
——————————
— Option 2
——————————
— Adding Value with Default Value
ALTER TABLE TestTable
ADD ThirdCol INT NOT NULL
CONSTRAINT DF_TestTable_ThirdCol DEFAULT(0)
GO
John
Great suggestion.
Please also “add in your blog” the way of adding a new column in sql table with a default value as the value of an existing column. Or if it is already in your blog, then please provide the link.
doesnt works good if you have million of records in the table :(
YES…Need to add column with default constraint but allow NULL. Then update existing data with default value. And lastly alter column to NOT NULL… That is the only way I have got it to work without impacting the overall performance of the database. Table had 1.3 billion records…