#0309 – SQL Server – ALTER TABLE – Use the WITH VALUES to populate default values when adding new columns


Last week, Madhivanan (B) wrote an informative post on “Adding/Altering a column with default value”. The post outlines a general guideline on the steps required when adding a new column (or altering an existing column) with a default value constraint on it.


The post prompted me to revisit the Books On Line page for the ALTER TABLE statement and with subsequent research, I realized that SQL Server actually empowers us with different methods to work with default values depending upon whether we are adding a new column to an existing table or when we are altering an existing column.


Adding a new column to an existing table


If the column is being added to an existing table and has a default definition available, then the ALTER TABLE statement allows us to use the WITH VALUES clause to store the default value in each existing record of the table. Let’s study the usage of the WITH VALUES clause with an example.


Adding a new, NULL-able column with default values


The query below is quite simple. It builds upon the example from Madhivanan’s post, so the overall structure and test data are similar.



  1. Create a new table with some columns
  2. Add some test data into the column
  3. Alter the table with add a new, NULL-able column with a default constraint
  4. In the same ALTER TABLE statement, use with WITH VALUES clause to also populate the columns
  5. Finally, select data from the table to see the output
USE tempdb;
GO
–Safety Check
IF OBJECT_ID(‘dbo.test’,’U’) IS NOT NULL
DROP TABLE dbo.test;
GO

–Create the test table
CREATE TABLE dbo.test ( Col1 INT );
GO

–Insert some test data
INSERT INTO dbo.test ( Col1 )
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5;
GO

–Add a new, NULL-able column with default constraint
–Use with WITH VALUES clause to also populate the columns
ALTER TABLE dbo.test
ADD col2 INT DEFAULT(0)
WITH VALUES;
GO

–Select from the table to check the data
SELECT Col1,
Col2
FROM dbo.test;
GO

–Confirm the Column properties
SELECT isc.TABLE_SCHEMA,
isc.TABLE_NAME,
isc.COLUMN_NAME,
isc.IS_NULLABLE,
isc.COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS AS isc
WHERE isc.TABLE_SCHEMA = ‘dbo’
AND isc.TABLE_NAME = ‘test’
AND isc.COLUMN_NAME = ‘Col2’;
GO


The result is as shown below. As you can see, SQL Server added the new column and also populated existing records with the default value of 0.


image


Adding a new, NOT NULL column with default values


The same example can be repeated for a NOT NULL column with similar results. The script for the same is shown below.

USE tempdb;
GO
–Safety Check
IF OBJECT_ID(‘dbo.test’,’U’) IS NOT NULL
DROP TABLE dbo.test;
GO

–Create the test table
CREATE TABLE dbo.test ( Col1 INT );
GO

–Insert some test data
INSERT INTO dbo.test ( Col1 )
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5;
GO

–Add a new, NOT NULL column with default constraint
–Use with WITH VALUES clause to also populate the columns
ALTER TABLE dbo.test
ADD col2 INT NOT NULL DEFAULT(0)
WITH VALUES;
GO

–Select from the table to check the data
SELECT Col1,
Col2
FROM dbo.test;
GO

–Confirm the Column properties
SELECT isc.TABLE_SCHEMA,
isc.TABLE_NAME,
isc.COLUMN_NAME,
isc.IS_NULLABLE,
isc.COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS AS isc
WHERE isc.TABLE_SCHEMA = ‘dbo’
AND isc.TABLE_NAME = ‘test’
AND isc.COLUMN_NAME = ‘Col2’;
GO


The results are shown in the screenshot below. As you can see, the WITH VALUES clause added the NOT NULL new column after adding default values to the existing records.


image


Altering an existing column


The method highlighted by Madhivanan (B) in his post – “Adding/Altering a column with default value” is the only option that I know of based on my research. The basic steps therefore are:



  1. Add the default constraint on the column (if applicable)
  2. Update the NULL values on already existing records with the desired default value
  3. Alter the column to be NOT NULL

Further Reading




Until we meet next time,


Be courteous. Drive responsibly.

Advertisements

One thought on “#0309 – SQL Server – ALTER TABLE – Use the WITH VALUES to populate default values when adding new columns

Let me know what you think about this post by leaving your feedback here!

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