Brett Kaiser (x002548) Blog

Not Just a Number - Brett Kaiser

Computed Columns? Worth their Salt?

by Brett Kaiser (x002548) in  

Well maybe if it was for a margarita...but me,  I prefer Rocks no salt.

But it would be an interesting poll to see how often they are used.

Me, I've never used them...and in reading BOL, seems like more of a headache than anything else.  Why not just create a function?

Anyone?

Geez what a lame post....just curious though...

FOR: 2

AGAINST: 2

 

 

Legacy Comments
Jay
2004-06-16		 re: Computed Columns? Worth their Salt?
Hey Brett, I am with you, UDF's are my preferred method.
Safi
2004-06-16		 re: Computed Columns? Worth their Salt?
On computed column you can create index.
Brett
2004-06-16		 re: Computed Columns? Worth their Salt?
An Index? Really? Wouldn't a computed column in an index be a stage 2 predicate?

Interesting...worth a test....

DavidM
2004-06-16		 re: Computed Columns? Worth their Salt?
Safi has a point but the wrong one. Stuff the index, I want the contraints! AFAIK they can take a CHECK, UNIQUE and PRIMARY contraint but not be part of a FK contraint.

This gives you the ability to build lots of rules.. eg the dodgy unique value, many nulls contraint..
Safi
2004-06-17		 re: Computed Columns? Worth their Salt?
That is true... But see the showplan:

if object_id('a') is not null
drop table a
go
if object_id('b') is not null
drop table b
go
create table a(id int identity(1,1),
firstname varchar(100),
lastname varchar(100),
[name] as (firstname+' '+lastname))
go
alter table a add constraint a_id primary key (id)
go
create unique index a_name on a(name)
go

create table b(id int identity(1,1),
[Name] varchar(200)
)
go
create unique index b_name on b(name)
go

insert into a values('jack','smith')
go

set showplan_text on
go
select name from a where name = 'jack smith'
go
set showplan_text off
go
Derrick Leggett
2004-06-17		 re: Computed Columns? Worth their Salt?
I don't have any problem with computed columns. For statistical purposes, they work fine. Also, we're in the middle of a process to update our usernames to all uppercase. We're using an indexed computed column that's all uppercase to do this until the conversion is complete. Before that our developers had UPPER(l.first_name) LIKE @first_name which is REALLY, REALLY slow. It's made the conversion process much smoother.
Mcrowley
2004-06-17		 re: Computed Columns? Worth their Salt?
I have actually used a computed column to a staggeringly good effect, if I should say so myself. ;-) We deployed a system wherein there was a join between an integer column and a varchar column. THe developers paid no mind to the difference. After all, all of the testing they did showed no problems. Fast-forward to the production deployment, and I am seeing the CPU of the databsae server....well, it wasn't exactly a spike...it was more of a flatline...at 100%. The short of it is, we added a computed column to the table with the integer field, which converted the integer to varchar. Presto-change-o, and all of the indexes were valid once more. Now the CPU stays around 50% with gusts to 80.
Todd
2006-08-07		 re: Computed Columns? Worth their Salt?
Computed columns are FAR better than UFNs for performance when selecting multiple rows. We have a 400,000 row person table. We were using a UFN to return the formatted person name. Selecting all rows from the table without the UFN takes only a few seconds. Adding the UFN makes it take almost a minute. Then we replaced the UFN with a computed column where the formula contains the same logic as the UFN (but does not call the UFN). Now it takes barely any longer than selecting all rows without the UFN.