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
Category: Data
Tags: Sql Server