I have an existing table say, Items with Id | CreateDate (as DateTime) columns; want to add Year int not null, take the default year from the CreateDate column.
The Year value will be futer updated to different value, but just set it as default value when the column Year is added, because want to add non-nullable column to Year.
Is that possible?
If you want to add a new column and initialize it with the values from the other one, you can do:
-- add the column (as nullable)
alter table mytable add created_year int;
-- update the values on existing rows
update items set created_year = year(created_date);
-- make the column not nullable
alter table mytable alter column created_year int not null;
Don't bother with another column -- unless you will feel the need to change the value of the column in the future. Instead, just use a computed column:
alter table t add created_year as (year(created_date));
This is automatically calculated when you query the table, so it is always accurate, both for new rows and old rows.
Presumably, created_date is never updated.