DEFAULT value in SQL Server

In this section, I would like to tell you about adding a column to the existing table with a default value.

Default value is very useful in the sense if you don’t supply any value to that column SQL Server will automatically insert the default value into that column.

Let us create a DEPT table.

CREATE TABLE [dbo].[DEPT](
[DEPTNO] [int] NOT NULL,
[DNAME] [varchar](30) NULL
)

Run the below insert statements

INSERT INTO DEPT VALUES(1,'Human Resource');
INSERT INTO DEPT VALUES(2,'Project Management');
INSERT INTO DEPT VALUES(3,'Business Analysis');
INSERT INTO DEPT VALUES(4,'IT Analyst');
INSERT INTO DEPT VALUES(5,'Software Developer');

Now let's say I want to add CATEGORY column to this table and store the default value as “IT”.


ALTER TABLE DEPT
ADD CATEGORY VARCHAR(3) NOT NULL DEFAULT ('IT')

When you run the above statement it will not only add a new column with default value but it will update the existing values to “IT”.

Now if you look at the table definition, you can see the new column with DEFAULT clause is set to “IT”.

CREATE TABLE [dbo].[DEPT](
[DEPTNO] [int] NOT NULL,
[DNAME] [varchar](30) NULL,
[CATEGORY] [varchar](3) NOT NULL DEFAULT ('IT')
)


This is perfect when you are adding a new column. What if you want to modify the definition of a column to remove the default value??

Wait for my next post or tell me if you have the answer……