WITH VALUES TSQL
Solution 1
WITH VALUES
applies default values to the null fields
http://technet.microsoft.com/en-us/library/ms187742.aspx and a short blog post about it.
Solution 2
When you add a nullable column with a default constraint to a table, then all existing rows will get the new column with a NULL
as its value. The defined default values will only be applied to new rows being inserted (if they don't have a value for that column in their INSERT
statement).
When you specify WITH VALUES
, then all existing rows will get that defined default value instead of NULL
If the column you're adding to your new table is non-nullable and has a default constraint, then that default value is applied to all existing rows in the table automatically (no need for WITH VALUES
because the column must have a value other than NULL
)
TheWommies
Updated on July 09, 2022Comments
-
TheWommies almost 2 years
I have been trying to understand what the
WITH VALUES
statement does?I cant seem to find any documentation that explains it properly.
ALTER TABLE Table1 ADD newGuidId UniqueIdentifier NULL CONSTRAINT DF_Guid Default newid() with values
-
zedfoxus over 7 yearsWhoever is reading this answer, please also read marc_s's answer as it provides more details.