469,106 Members | 2,338 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,106 developers. It's quick & easy.

Computed columns


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


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #1
1 6994

"Paulo Andre Ortega Ribeiro" <pa************@terra.com.br> wrote in message
news:3f*********************@news.frii.net...

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


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


Adding a computed column means the value is computed on demand (unless you
index that column) when you query the table. The view is rather like storing
a query definition in the database, so the value is 'calculated' only when
you query the view (unless you index the view). In that sense, both
approaches are very similar.

However, the view is a separate object, which means you can have separate
security permissions on it. A view will also be more portable to other
database systems, if that is a consideration for you.

In any case, neither solution is a good one, assuming that this isn't a
theoretical question. You will quickly have duplicate email addresses, even
with just a few names. If you have John Smith, and then James Smith is
added, their email addresses would be the same, so the best all-round
solution is to use a normal table column for the email address.

Simon
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by DMAC | last post: by
4 posts views Thread by Henning N?rg?rd | last post: by
reply views Thread by Jim Heavey | last post: by
3 posts views Thread by Raymond Du | last post: by
reply views Thread by am72de | last post: by
7 posts views Thread by Aamir Mahmood | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.