With Values

 Using the clause WITH VALUES when you are adding a new column to an existing table using Microsoft SQL Server is a handy little trick. The use of WITH VALUES is when you want to add a default constraint to an existing table that allows null. Because when you add a default constraints that allows null it will NOT fill the existing rows with a value, this can effect your database logics ( if you have written some code depending on these values ). 

Let me demonstrate this with some simple tables.

• Create a simple table

CREATE TABLE Students(
id int NOT NULL,
name varchar(50) NULL
)

• Add some sample data

insert into Student (id,name) values
(1,'Cappadonna'),
(2,'UGod')

• Add a new column to the table , with NOT NULL clause

ALTER TABLE Student
ADD age int not null
CONSTRAINT age_default DEFAULT 99

Now if you query your table you will see that the existing rows are being populated with your default value. This is because we added the constraint as NOT NULL.

 

•  Now let's add another column with a default value , but this time we ALLOW NULL

ALTER TABLE Student
ADD address1 varchar(250) null
CONSTRAINT address1_default DEFAULT 'University Road'

Insert a new record as well

insert into Student (id,name) values (3,'Ghost Face Killah')

Now if you query the table you will see the default values has only been added to the newly inserted record. The existing records still have NULL.

• If you want to have values for existing records as well , even for a ALLOWS NULL column , WITH VALUES can be used

Add a new column using WITH VALUES

ALTER TABLE Student
ADD address2 varchar(250) null
CONSTRAINT address2_default DEFAULT 'Colombo' with values

Now if you query your table you can see that the default value is applied to existing rows as well.

 

So that is it ! As you can clearly see WITH VALUES saves the day. Having said that , while writing this post I was actually wondering why someone would need this? Because if you are inserting a column that allows null , then your code should ideally catch cases that has a null value. So now i am confused to why we even need this :p , but that's a question for another day ! Let me know if you guys have any thoughts. Until then , bye ! 

P.S - This is what Microsoft documentation says about it:

WITH VALUES
When adding a column AND a DEFAULT constraint, if the column allows NULLS using WITH VALUES will, for existing rows, set the new column's value to the value given in DEFAULT constant_expression. If the column being added does not allow NULLS, for existing rows, the column's value will always be set to the value given in the DEFAULT constant expression. Starting in SQL Server 2012 this may be a meta data operation adding-not-null-columns-as-an-online-operation. If this is used when the related column isn't also being added then it has no effect.
Specifies that the value given in DEFAULT constant_expression is stored in a new column that is added to existing rows. If the added column allows null values and WITH VALUES is specified, the default value is stored in the new column that is added to existing rows. If WITH VALUES is not specified for columns that allow nulls, the value NULL is stored in the new column, in existing rows. If the new column does not allow nulls, the default value is stored in new rows regardless of whether WITH VALUES is specified.