CREATE TABLE where multiple columns are inserted with same value

Question

Lets say I have a CREATE TABLE code like this:

CREATE TABLE Test (
  ID int NOT NULL IDENTITY(1,1),
  SortIndex int,
  Name nvarchar(50) NOT NULL
);

I was wondering if it's possible to make a table in MSSQL which had the ability to insert the ID's value into the SortIndex column when I run an INSERT.

So I would run this INSERT:

INSERT INTO Test (Name) VALUES ('Awesome Dude');

Which would normally yield the row:

ID,SortIndex,Name
1,NULL,"Awesome Dude"

But I'd like it to automatically be:

ID,SortIndex,Name
1,1,"Awesome Dude"

Is this even possible by altering the CREATE TABLE script, or do I have to use a TRIGGER?

Answer 1

I would be inclided to take a slightly different approach to this. If you want your SortIndex to default to the ID, but be overridable, I would use a nullable column, and a computed column:

CREATE TABLE Test (
  ID int NOT NULL IDENTITY(1,1),
  OverrideSortIndex int,
  Name nvarchar(50) NOT NULL,
  SortIndex AS ISNULL(OverrideSortIndex, ID)
);  

If you need to change the sort index for any reason, update the column OverrideSortIndex and this takes precedence.