Blog

Since releasing Entity Framework code first, I've approximately haven't written any SQL code to Table Migration! I was just thinking about that and tried to add a column to an SQL server table with a default value, here is the code I wrote:

 

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES

 

For example, create a table named Product by the following query:

 

CREATE TABLE [dbo].[Product](

[Id] [int] NOT NULL,

[Name] [nvarchar](50) NULL,

[Price] [nvarchar](50) NULL,

 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

After inserting some record to the table, you can add another required column to the table via the query below:

 

ALTER TABLE [dbo].[Product]

        ADD Quality tinyInt 

 CONSTRAINT Product_Quality

    DEFAULT (0)

WITH VALUES