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
Although , I do not understand SQL Server much but the way you explained in this particular post is awesome…
Other bloggers (Including me ) might have just blogged the last script saying “This script is for this purpose ” but the way you cam to the point is really awesome …
great articulated
Thanks sir
Dhananjay Kumar
A reason you may want to create the column without a default is: if it’s a large table, it will take exponentially longer and will result in locking/blocking. Creating a new column without a default takes no time regardless the size of the table. Then you can populate the table in increments, then add the NOT NULL as a last step.
Yes but altering an existing table for adding an new column with default value,the update statement is necessary for updating the existing data in table with default value.
Hi Dave,
use AdventureWorks
select
FirstName +’ ‘+ LastName as ‘Name’
from Person.Contact
go
this will give you the output like
Name
——–
Gustavo Achong
Catherine Abel
Kim Abercrombie
Humberto Acevedo
Pilar Ackerman
but i wnat the output like
temp_id Name
———– ———–
1 Gustavo Achong
2 Catherine Abel
3 Kim Abercrombie
4 Humberto Acevedo
5 Pilar Ackerman
so i want it only in one select statement.
i tried like
use AdventureWorks
select
IDENTITY(1,1) as ‘temp_id’, FirstName +’ ‘+ LastName as ‘Name’
from Person.Contact
go
do i have any option for that in sqlserver.
select
row_number() over (order by (select 1)) as temp_id,FirstName +’ ‘+ LastName as ‘Name’
from Person.Contact
Great one. But 1 small confusion (when it comes to memorize the syntax of ALTER Statement):
For the ALTER statement, when we ADD a new column there is no keyword “COLUMN”, but when we ALTER the column then it uses the keyword “COLUMN”.
Wish there was consistency, so that we could fire the SQL without any compilation error for the 1st time ;)
I’m using “method 2” via SQL-Server Management Studio. Very useful for adding additional fields. I find it easier to remember via UI rather written query.
Pinal, as probably the most dedicated SQL teacher online what the reason behind tables with NULL values at all except the fact they take less HDD space than 0 for instance? It is much harder to maintain those in ASP.NET later on.
Nice explanation on the use of NULL and its effect on performance. I have seen places where business rules are built around when a certain column is NULL, I feel that is not a good practice. Good script.
Can you expalin or write the query if I want to delete the column which had a default value.
There wont be any issue. What happened when you tried it?
If this is true, I would have told for the developers to find a new line work.
I want to add column description in alter statement. How to mention Column description in the alter statement.
example.
Columnname Desc.
AltModelSlNo. AlternativeModelSerialNo.
how to specify.in the alter statement.
hi,
i create table “emp” and insert data into it.
Then,i add column in table “emp” contain default constraint.
Now, i want to set the value of default in pre-existing table”emp” which contain data.
how can i set default value???
p.s:i need answer its urgent.
You need to use UPDATE statement to update existing data
forgot to mention column name above.
how can i set default value in column name “country”?
ALTER TABLE dbo.employees ADD salary int NOT NULL DEFAULT(15000)
For adding a default to a table with pre-existing data you can do it all at one with ALTER TABLE xxx ALTER COLUMN yyyy DEFAULT (0) WITH VALUES
The WITH VALUES will apply the default value to the NULL fields
Hello,
Ok, so i have used the syntax:
ALTER TABLE TestTable
ADD ThirdCol INT NOT NULL DEFAULT(0)
but when i use this syntax:
ALTER TABLE TestTable
ADD ThirdCol INT NULL DEFAULT(0)
the default 0 is not applied, the field still holds a null value. Can you please tell me why so?
When you apply ADD ThirdCol INT NULL DEFAULT(0), it will assign defaults only to new records.
See Kelvin’s post (above) – use the “with values” clause to add the default value to existing records
Hi sir i want to drop the default value but it is not successful please help me
here i mentioned my codings
create table colour(type1 varchar(50), type2 varchar(50), type3 varchar(50) default ‘blue’)
insert into colour values(‘white’, ‘black’,’orange’)
select * from colour
insert into colour values(‘block’,’gray’,’ ‘)
insert into colour values(‘rose’,’green’,default)
insert into colour (type1,type2) values (‘gg’,’bb’)
alter table colour alter column type3 drop default
error:Incorrect syntax near the keyword ‘default’.
My case is I added a exsisting column With a default Unique Value i had 20000 records almost
using a Row number
I had added a AdmissionForm_No Column with Datatype Varchar
Run this
with xyz as (select AdmissionForm_No, Row_number() over(order by Reg_no asc) as RN from Students)
Update xyz Set AdmissionForm_No = RN
and AdmissionForm_No was updated by unique Row Number Value
it did my Half Work but issue is
i need to append ‘Frm-‘ now Against each record of AdmissionForm_No
Like
Frm-00001,Frm-2, frm-3, frm-30051,frm-55555
hi
i want to enter the value in a column for each row
and each row there is different value
how to enter
SELECT NEWID() FROM
Not sure if I am missing something, but NULL is used when a value is not known. This applies to both numeric and char based columns. For example an age from four rows where all four ages are known or only three are know will be different. say the total was 100 from the four, the average would be 25. If only three were known i.e. one was NULL the average would be 33.3r. You cannot substitute 0 for null in this case
The results of Option 1 and 2 are not the same, and one should be used over the other depending on the purpose.
It might make sense to update all the current records “SecondCol” to 0, but what if you want to force this value to be set explicitly for future entries. By using “default” this will implicitly set the value to 0 for INSERT statements which don’t reference SecondCol.
This is fine in most cases, but thought I should just point out “Option 2” is not a short-hand method of “Option 1”. They both produce different schemas.
Hey Pinal, they way you explain is superb and outstanding…Please do this charity for the Indian IT people