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]

Re: Scalar Function Columns

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]

Re: Scalar Function Columns

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" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote in message
news:MbqdnQopMe Xwu2XdRVn-tw@giganews.com ...[color=blue]
> Use a view. It's better to avoid dependent columns where possible because[/color]
of[color=blue]
> 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
> --
>
>[/color]

[--CELKO--]

Re: Scalar Function Columns

>> 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.
[color=blue][color=green]
>> For example, if I have .. <<[/color][/color]

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);
[color=blue][color=green]
>> Let's say in table B I put price information for each detail and in[/color][/color]
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]

Re: Scalar Function Columns

Tyler Hudson (TylerH@Spam.Me NOTallpax.com) writes:[color=blue]
> 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?[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp