Computed columns

[piei2000]

I have a table with fields called fname (First Name) and lname (Last Name). I need the user´s email thai is compose from lname and fname:
LOWER(LEFT (fname,1) + lname)

Is there any difference between creatig this computed column ia a table or in a view in SQL Server 2000?

I can do:

1. CREATE TABLE Users(
fname varchar(20),
lname varchar(20),
email as LOWER(LEFT (fname,1) + lname) )

Or

2. CREATE TABLE Users (
fname varchar(20),
lname varchar(20))

CREATE VIEW Vw_users (fname, Lname ,
email)
AS
SELECT fname, Lname ,
LOWER(LEFT (fname,1) + lname) )


Is one of them is better?

Paulo

[rmiao]

I think you should put it in table so have one less object involved.

[sdives]

I would say that it depends on the requirements of the application, the size of the table, and the activity (current and future) against the derived column.

If it's a wide table with millions of rows then the view might be the best approach.

If the column is going to be referenced frequently for a variety of purposes then creating the column would likely be the best approach.

Sidney Ives
Database Administrator
Sentara Healthcare

[piei2000]

Why is better to create the computed column as a column of a table? Is there a performance difference? If a understood correctly even I create the computed column in a table, SQL Server won´t create data for this column? Is it correct or i´m wrong?

Paulo

[rmiao]

You are correct. For performance, you can test both method to see if have any difference. To put it in view, you may need add pkey column in the view and join table to view to list user name along with email address.

[MAK]

In my opinion both are same. It computes a column only when it is queried. Say for example use getdate() or newid() in computed column

example:

create table x (id int, date as getdate())

insert into x (id) select 1
insert into x (id) select 2
insert into x (id) select 3
insert into x (id) select 4
insert into x (id) select 5
go
create table y (id int)

insert into y (id) select 1
insert into y (id) select 2
insert into y (id) select 3
insert into y (id) select 4
insert into y (id) select 5
go
create view y_heh as select id,getdate() as date from y
go
select * from y_heh
or


create table z (id int, st as Newid())

insert into z (id) select 1
insert into z (id) select 2
insert into z (id) select 3
insert into z (id) select 4
insert into z (id) select 5

select * from z

[KingSexy182]

BETTER, You could just CREATE 1 TABLE
with all 3 fields and create a TRIGGER ON INSERT/UPDATE which updates the email field AUTOMATICALLY.

THIS METHOD WILL ENSURE THAT YOU DO NOT HAVE TO MANUALLY MAINTAIN the computed column

--u will have to look up bol to get the correct syntax on how to create the trigger.

ON INSERT:
update mytable
set
email = firstname + surname
where mytable.id = inserted.id

ON UPDATE
update mytable
set
email = firstname + surname
where mytable.id = deleted.id