Newb Question about Computed Column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Newb Question about Computed Column

Author

Topic

dprichard
Yak Posting Veteran

94 Posts

Posted - 2006-09-22 : 13:07:38

Okay, newb question alert!!!

I created a computer column that is based on the difference between the column start_date and getdate().

Does the computed column only update when you update the column or does it change when you select it also?

dprichard
Yak Posting Veteran

94 Posts

Posted - 2006-09-22 : 13:27:17

Nevermind... I was looking at month so I couldn't tell, but I changed it to seconds and I can see that it does update.

Sorry about the worthless post!!!

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-22 : 14:15:50

Actually just to clarify, it doesn't update when you update the row, a calculated column is calculated when you query the table. The value of the calculated column isn't stored at all, so that's why you do indeed see the correct value when you run your query.

dprichard
Yak Posting Veteran

94 Posts

Posted - 2006-09-22 : 14:26:34

Kewl, thanks for the clarification!!!

Is there a way to compute like this between two tables? Or store the results from another compute column in another table in my user table?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-22 : 14:51:41

>>Is there a way to compute like this between two tables?
No. A computed column is an expression using other columns in the same table.
A VIEW, however, can "define" a column as an expression using columns from any table in the FROM clause.

>>Or store the results from another compute column in another table in my user table?
You could populate a table based on the SELECT statement from a table with a computed column (if that's what you mean). The column value (based on the computed column) would be "as of" the time of the select statement.

What are you trying to achieve?

Be One with the Optimizer
TG

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-22 : 14:53:16

Not just with a computed column. To do that, you'll need to put your calculation in a function and then call the function in the computed column, like this

create table Test1
(ID1 int, Data1 varchar(30))
go
create function dbo.fnOneLookup (@ID1 int)
returns varchar(30)
as
begin
	declare @retVal varchar(30)
	select @retVal = Data1 from Test1 where ID1 = @ID1
	return @retVal
end
go
create table Test2
(ID2 int, ID1 int, Data2 varchar(30), CalcFrom1 as (dbo.fnOneLookup(ID1)))
insert Test1 values (1, 'blah blah blah in 1')
insert Test1 values (2, 'blah blah blah in 1')
insert Test1 values (3, 'blah blah blah in 1')

insert Test2 values (1, 1, 'blah blah blah in 2')
insert Test2 values (2, 2, 'blah blah blah in 2')
insert Test2 values (3, 2, 'blah blah blah in 2')

select * from Test2

In most cases though you shouldn't do that (ie. create lots of functions that pull in values from other tables) - you should rather use joins between the various tables and calculate what you need in the select list of the query.

dprichard
Yak Posting Veteran

94 Posts

Posted - 2006-09-22 : 14:59:19

Thank you for the info. So you think I am better off just doing a query with joins in the page directly rather than trying to have the database do all that?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-22 : 15:05:48

>>in the page

I think you're better off writing a stored procedure (which has the joins and derived column) and then calling that from your web app. Rather than embedding sql code in your web app.

Be One with the Optimizer
TG

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-22 : 15:07:09

quote:
So you think I am better off just doing a query with joins in the page directly rather than trying to have the database do all that?

I didn't exactly say that

When you say "with joins in the page directly" you're asking something different. I'm guessing you mean you're going to access the database from within a web application? In that case I'd recommend creating stored procedures with the queries that use joins and call those stored procedures from the application.

That way the database does all that it should, but you're not building all the query logic into the table definitions.

dprichard
Yak Posting Veteran

94 Posts

Posted - 2006-09-22 : 15:18:12

Sorry, never done a Stored Procedure. Started reading up on them now and will try that. Do you recommend ever writing the queries in the page itself?

Thank you so much again for all your hep!!!

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-22 : 15:26:17

It's a hotly debated topic - personally I think stored procedures are better although I'm not militant about it like some people

Something you'll come across as you start doing this is how to pass parameters to the stored procedures - there are essentially two ways, you can create a string in your web page that concatenates the parameter value into the command to call the stored procedure, or can create parameters on the database command (in ADO.NET that would be a SqlParameter in the Parameters collection of the SqlCommand).

The second method is a little more work, but you must do it that way. The first way is vulnerable to SQL Injection Attacks and more prone to errors.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-22 : 15:41:56

I think the debate is getting pretty onesided

try googling this, then decide for yourself:

parameterized queries vs stored procedures

Be One with the Optimizer
TG

dprichard
Yak Posting Veteran

94 Posts

Posted - 2006-09-22 : 16:47:07

So, if this is my query:

SELECT     FLOOR(emp_accrued_time_off) AS daystotal
FROM         employee
WHERE     (emp_id = @emp_id)

Would this be how I make my stored procedure?

CREATE PROCEDURE daystotal (@emp_id BIGINT)
SELECT     FLOOR(emp_accrued_time_off) AS daystotal
FROM         employee
WHERE     (emp_id = @emp_id)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-22 : 16:52:57

slight modification:


if object_id('daystotal') > 0
	drop proc daystotal 
go

CREATE PROCEDURE daystotal 
	@emp_id BIGINT
as

SELECT    FLOOR(emp_accrued_time_off) AS daystotal
FROM      employee
WHERE     emp_id = @emp_id

go
grant exec on daystotal to <TheAccountYourAppRunsUnder>
go

You must have a lot of employees

Be One with the Optimizer
TG

dprichard
Yak Posting Veteran

94 Posts

Posted - 2006-09-22 : 16:57:21

quote:
You must have a lot of employees

Why do you say that?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-22 : 17:00:29

quote:
Originally posted by snSQL

It's a hotly debated topic - personally I think stored procedures are better although I'm not militant about it like some people

I'm very militant about stored procedures. Either you use them or your code doesn't make it to QA, let alone production. Plus we only grant access to stored procedures. No table access is allowed by the applications or users. So if a developer tries to slip in some inline sql in their application, it'll blow up in the QA environment since it won't have permissions. It doesn't blow up in the development because we grant developers db_owner. They can grant the application table level access, but they certainly can't in QA.

Tara Kizer

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-22 : 17:12:40

quote:
Originally posted by dprichard

quote:
You must have a lot of employees

Why do you say that?

because employeeid is BIGINT

In case you couldn't tell, I'm in Tara's camp on that debate. (there is no debate where I work as well)

Be One with the Optimizer
TG

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-22 : 17:14:01

quote:
Originally posted by tkizer

I'm very militant about stored procedures.

See, told you

dprichard
Yak Posting Veteran

94 Posts

Posted - 2006-09-25 : 08:32:17

quote:
because employeeid is BIGINT

I got a lot of bad advice early on in learning all this. The company has about 250 employees. Should I not use BIGINT with this?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-25 : 09:44:13

quote:
Originally posted by dprichard

quote:
because employeeid is BIGINT

I got a lot of bad advice early on in learning all this. The company has about 250 employees. Should I not use BIGINT with this?

well, bigint can hold values from -9223372036854775808 to 9223372036854775807. So if your company hires every human on the planet you'll still have plenty of room to start hiring all the primates, invertabrates, and even a good deal of the insects

Unless you are having disk space issues I don't think it's a big deal (with your small database) Check out Books Online: "int, bigint, smallint, and tinyint" for storage size and value capacity information.

I was just joking with my comment ("you must have a lot of employees"), but if you are going to be designing databases than one of things you should not take lightly is what datatypes to use for any given entity. One common error from newbs (and this is worth changing) is using varchar to store date values.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources