Computed Columns? Worth their Salt?

<< Surrogate Keys….The Devil’s spawn (OK Not really) | Home | Export out entire database >>

Computed Columns? Worth their Salt?

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

Print | posted on Wednesday, June 16, 2004 8:59 AM | Filed Under [ SQL Server ]

Feedback

# re: Computed Columns? Worth their Salt?

Hey Brett, I am with you, UDF's are my preferred method.

6/16/2004 9:36 AM | Jay

# re: Computed Columns? Worth their Salt?

On computed column you can create index.

6/16/2004 10:29 AM | Safi

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

6/16/2004 11:12 AM | Brett

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

6/16/2004 7:22 PM | DavidM

# 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

6/17/2004 3:58 AM | Safi

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

6/17/2004 10:02 AM | Derrick Leggett

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

6/17/2004 1:27 PM | Mcrowley

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

8/7/2006 1:22 PM | Todd

Comments have been closed on this topic.

x002548's Blog

My Links

Article Categories

Archives

Post Categories

Image Galleries

Misc

SQL Server