With Values Using Alter Table

Step-1: Create Table

CREATE TABLE STUDENT

(

ID INT IDENTITY(1,1) PRIMARY KEY,

STUDENTNAME VARCHAR(100) NOT NULL ,

LIVINGLOCATION VARCHAR(100) NULL

)

Step-2: Insert Some Sample Data

INSERT INTO STUDENT(STUDENTNAME,LIVINGLOCATION)

SELECT 'Rakesh','Hyderabad'

UNION ALL

SELECT 'Raju','Delhi'

UNION ALL

SELECT 'Madhu','Hyderabad'

UNION ALL

SELECT 'Naresh',NULL

UNION ALL

SELECT 'Venaktesh','Chennai'

Step-3:

If we need to add DOJ column to Student table with default constraint , But all the Existing rows want to update Default GETDATE() , if WITH VALUES are not used all existing rows DOJ column should be NULL.

ALTER TABLE STUDENT ADD DOJ DATETIME NULL DEFAULT GETDATE() WITH VALUES

Step-4:

SELECT * FROM STUDENT