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