The WITH VALUES clause is simple to understand.
Suppose we have a table called Orders :
Now we add a column with a default value :
- alter table orders add newcol varchar(100) default 10
- insert orders (orderid,orderdate,custid) values (101,getdate(),1)
Now query the table again :
You can see that the new column has default values only for the next new row. So the original row, ie with orderid 100, does not have the new default value applied.
Now drop the column and add it again WITH VALUES :
- alter table Orders drop column newcol;
- alter table orders add newcol varchar(100) default 10 with values
Now query the table :
You can see that specifying WITH VALUES has updated all rows in the table, including the first one with orderid 100.
Hope this clarifies the use of WITH VALUES.