Scalar Function Columns

[Tyler Hudson]

Is it ill-advised to have columns whose values pull from scalar functions
using other fields in the record as parameters? For example, if I have

create table a(iID int primary key)
create table b(
iID int ,iDetail int,
CONSTRAINT PK PRIMARY KEY(iID,iDetail),
CONSTRAINT FK FOREIGN KEY (iID) REFERENCES a(iID)
)
Let's say in table b I put price information for each detail and in table a
I'd like to put a column that sums these prices for the children of each
record. Should I make a computed column that references a function using
iID as a parameter? Or would it be better to create a view for this kind of
purpose?
Regards,
Tyler

[David Portas]

Use a view. It's better to avoid dependent columns where possible because of
the work involved in keeping them up to date. The view will likely
outperform a computed column UDF doing the same job.

--
David Portas
SQL Server MVP
--

[Mischa Sandberg]

There's also a hidden gotcha with computed columns and INDEX, should that
arise ...
you start to need SCHEMA_BINDING, which has a ripple effect
in causing stored procedures to recompile, which adds to locking conflicts
....
yadda yadda.

"David Portas" <RE****************************@acm.org> wrote in message
news:Mb********************@giganews.com...

Use a view. It's better to avoid dependent columns where possible because of the work involved in keeping them up to date. The view will likely
outperform a computed column UDF doing the same job.

--
David Portas
SQL Server MVP
--

[--CELKO--]

>> Is it ill-advised to have columns whose values pull from scalar
functions using other fields [sic] in the record [sic] as parameters
[sic]? <<

What are you talking about?? Rows are not records; fields are not
columns; tables are not files; parameters are used by functions and
procedures, not tables.

For example, if I have .. <<
Read ISO-11179 so you will stop prefixing data elements with their
storage type; it makes your code look, read And maintain like 1960's
BASIC OR 1950's FORTRAN II. I know this has nothing to do with your
question, but it is so fundamentally wrong I have to correct your bad
habit.

CREATE TABLE A
(a_id INTEGER NOT NULL PRIMARY KEY);

CREATE TABLE B
(a_id INTEGER NOT NULL REFERENCES A(a_id),
detail INTEGER NOT NULL);
Let's say in table B I put price information for each detail and in

table A I'd like to put a column that sums these prices for the
children of each
record [sic]. <<

Then you would use a VIEW and you'd have a price column *somewhere* in
the schema. What you posted was awful, even for a sample schema
skeleton.

Why would anyone even consider a proprietary, non-relational thing in
SQL? Because if you were in a file system, which does have fields and
records, you would write procedural code to solve the problem! YOu
have a lot to un-learn.

[Erland Sommarskog]

Tyler Hudson (Ty****@Spam.MeNOTallpax.com) writes:

Is it ill-advised to have columns whose values pull from scalar functions
using other fields in the record as parameters? For example, if I have

create table a(iID int primary key)
create table b(
iID int ,iDetail int,
CONSTRAINT PK PRIMARY KEY(iID,iDetail),
CONSTRAINT FK FOREIGN KEY (iID) REFERENCES a(iID)
)
Let's say in table b I put price information for each detail and in
table a I'd like to put a column that sums these prices for the children
of each record. Should I make a computed column that references a
function using iID as a parameter? Or would it be better to create a
view for this kind of purpose?

A view would be better, because if you say

SELECT iID, totprice FROM a

and totprice is a computed column with a UDF, the SELECT statement
is likely to be serialized as if it was a cursor with disastrous
effects on performance.

This does not happen if you make it a view.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp