This is a simple script to demonstrate how computed columns work in SQL Server. Also demonstrated are the effects of updates/deletes on them. A persisted computed column is also added to demonstrate that indexes can be built on them. It is suggested that you run each segment of code separately to make it easier to understand the overall process.
-- test on adding a computed column to a table to see if existing procs still work-- setup environment
set nocount on
go-- create table
if exists (select name from sysobjects where name = 'tbTest') drop table tbTest
go
create table tbTest (TestID int identity, col1 varchar(10), col2 varchar(10), col3 varchar(10))
go-- create insert procedure
if exists (select name from sysobjects where name = 'spTest_INS') drop procedure spTest_INS
go
create procedure spTest_INS
@col1 varchar(10),
@col2 varchar(10),
@col3 varchar(10)
as
begin
insert into tbTest (col1, col2, col3) values (@col1, @col2, @col3)
end
go-- create update procedure
if exists (select name from sysobjects where name = 'spTest_UPD') drop procedure spTest_UPD
go
create procedure spTest_UPD -- yes I'm aware this doesn't allow updating columns to null
@TestID int,
@col1 varchar(10) = null,
@col2 varchar(10) = null,
@col3 varchar(10) = null
as
begin
update tbTest set col1 = coalesce(@col1, col1), col2 = coalesce(@col2, col2), col3 = coalesce(@col3, col3) where TestID = @TestID
end
go-- create delete procedure
if exists (select name from sysobjects where name = 'spTest_DEL') drop procedure spTest_DEL
go
create procedure spTest_DEL
@TestID int
as
begin
delete from tbTest where TestID = @TestID
end
go-- create view
if exists (select name from sysobjects where name = 'vwTest') drop view vwTest
go
create view vwTest as select * from tbTest
go-- call insert proc
declare @x int
select @x = 1
while @x
