Using “WITH VALUES” in a DEFAULT clause

The WITH VALUES clause is simple to understand.

Suppose we have a table called Orders  :

Orders_table_main.jpg

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 :

Orders_1.jpg

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 :

Orders_2.jpg

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.

 

Advertisements
This entry was posted in Uncategorized. 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 )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s