SQL Server Computed Column
I always thought the only way to have a computed or derived column in SQL was to use some kind of a view in place, but I found out that you can actually create a computed column on a table and that you can even “persist” the data and have it indexed.
I’m only guessing that behind the scenes, SQL treats this somewhat like a trigger, like a before-trigger, where just prior to the transaction being posted, the calculations are applied and the column is populated, then the full transaction is applied.
Here’s the article where I found this information.
And here’s an example:
use database
create table t
( a int, b int);
--adding the computed column
alter table t add
x as case a
when -1 then
b
else
a
end persisted not null
--using "persisted" is required if you want to make column "not null". I believe "persisted" is somewhat implied if computed column is derived from PK columns.
--just showing that you can create an index on the new column
create index z on t (x)
insert into t values (1, -1)
insert into t values (2, -1)
insert into t values (-1, 4)
insert into t values (-1, 3)
insert into t values (-1, -1)
select * from t
--you can see that the computed column worked.
a b x
1 -1 1
2 -1 2
-1 4 4
-1 3 3
-1 -1 -1
Recent Buzz