Adding Default Column to a table in Oracle and SQL Server.

Adding a column to an existing table with default exhibit many question.
For instance
What would be value for that column on existing rows ?
What would happen if new column added is Null?
What would happen if new column added is Not Null?

lets see through in both database Oracle and SQL Server on how addition of new column with a default value is perform.

lets start with SQL Server,

SELECT TAB.COL1, TAB.COL2 INTO DEFAULTCOLTAB
FROM
(
VALUES (1,2),(3,4) ,(5,6)
) TAB(COL1,COL2);

SELECT * FROM DEFAULTCOLTAB;

COL1 COL2
----------- -----------
1 2
3 4
5 6

Adding a column with Default and Not null.

ALTER TABLE DEFAULTCOLTAB ADD COL3 INT NOT NULL DEFAULT 0;

COL1 COL2 COL3
----------- ----------- -----------
1 2 0
3 4 0
5 6 0

When we specify column to be not null and with a default it will update default value for existing rows as well.
now lets add a column with default and null-able.

ALTER TABLE DEFAULTCOLTAB ADD COL4 INT DEFAULT 0;

COL1 COL2 COL3 COL4
----------- ----------- ----------- -----------
1 2 0 NULL
3 4 0 NULL
5 6 0 NULL

When we specify column to be NULL, SQL Server does not update existing rows for newly added column. it is set to NULL and not to Default value.

If in case we want to update, existing rows with default value for null-able column.
we can use “WITH VALUES” clause and update existing rows with default values.

ALTER TABLE DEFAULTCOLTAB ADD COL5 INT DEFAULT 0 WITH VALUES;

COL1 COL2 COL3 COL4 COL5
----------- ----------- ----------- ----------- -----------
1 2 0 NULL 0
3 4 0 NULL 0
5 6 0 NULL 0

Now lets see how it works for Oracle.

CREATE TABLE DEFAULTCOLTAB
AS
SELECT ROWNUM COL1 , ROWNUM +1 COL2 FROM DUAL CONNECT BY ROWNUM <4;

COL1 COL2
---------- ----------
1 2
2 3
3 4

Adding a column with default and not null constraint.
ALTER TABLE DEFAULTCOLTAB ADD COL3 INT DEFAULT(0) NOT NULL ;

COL1 COL2 COL3
---------- ---------- ----------
1 2 0
2 3 0
3 4 0

It behave same as in SQL Server, adding default values for all existing rows.

Adding a column with default and null-able constraint.

ALTER TABLE DEFAULTCOLTAB ADD COL4 INT DEFAULT(0) NULL ;

COL1 COL2 COL3 COL4
---------- ---------- ---------- ----------
1 2 0 0
2 3 0 0
3 4 0 0

here it differ on how same work in SQL Server, though column was null-able it still add default values for all existing rows.
it is not dependent on NULL constraint on column, it update default values for all existing rows.

About Deepak Mahto

Having 9+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Oracle Internal. 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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s