By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,844 Members | 836 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,844 IT Pros & Developers. It's quick & easy.

Computed columns

P: n/a

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
Share this Question
Share on Google+
1 Reply


P: n/a

"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.