SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Computed Columns


Computed Columns

Author
Message
ningaraju.n
ningaraju.n
Right there with Babe
Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)

Group: General Forum Members
Points: 754 Visits: 425
Hi Guys,

Is there a way to reference columns of other table in computed columns except triggers?

Thanks in Advance
ALZDBA
ALZDBA
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47843 Visits: 9075
I don't think so. simple reason ... this cannot be "at run time" but must be persisted, so a trigger is the only way.

Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Elliott Whitlow
Elliott Whitlow
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39158 Visits: 5314
The answer is yes, sort of.. You can build a UDF that references another table in the same database, and use that UDF in the formula, I believe you can persist it.

If you can persist it DO! You don't want it hitting that UDF for every access to that column..

That UDF becomes bound to the table and cannot be changed without a mod to the table first to remove the reference.

so get it right the first time..

CEWII
ALZDBA
ALZDBA
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47843 Visits: 9075
great... now you've had two answers saying No and Yes ...

That's what makes these fora good, you'll get alternatives and test hints.

I don't have time right now, but go on and just test the alternatives.

Pleas post feedback, so others can use your refs for their testsCool

In both cases you'll have to take into account there is a hidden join for your every application doing stuff with your table, unless this column is persisted.

Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Sean Lange
Sean Lange
SSC Guru
SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)

Group: General Forum Members
Points: 101463 Visits: 18186
This was actually the topic of yesterday's QOTD.

Here is a link to the ensuing discussion.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
ALZDBA
ALZDBA
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47843 Visits: 9075
Thanks for this addition Sean Wow

OP: Keep in mind it is not because you can do something in a certain way, you should. Think about the consequences.

Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Paul White
Paul White
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54688 Visits: 11391
ningaraju.n (11/10/2010)
Is there a way to reference columns of other table in computed columns except triggers?

You could use a UDF that references the other table, but please don't. Functions that perform data access cannot be persisted, so it would be re-evaluated (effectively by running a separate query) once for every row it touches, every time. Absolutely horrible.

The idea of computed columns referencing other tables sounds like a VIEW to me.

Perhaps you could explain the circumstances more and provide an example to demonstrate what you are trying to achieve.

Again, please consider every possible alternative to using a function that does data access in a computed column.

Paul



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Paul White
Paul White
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54688 Visits: 11391
Code to demonstrate the issue, and show that UDFs that access data cannot be persisted:


CREATE TABLE dbo.Data
(
item INTEGER NOT NULL PRIMARY KEY,
value INTEGER NOT NULL,
);

INSERT dbo.Data (item, value)
SELECT V.number,
RAND(CHECKSUM(NEWID())) * 1000000
FROM master.dbo.spt_values V
WHERE V.type = N'P ';
GO
CREATE FUNCTION dbo.BadFunction(@item INTEGER)
RETURNS INTEGER
WITH SCHEMABINDING
AS
BEGIN
RETURN
(
SELECT D.value
FROM dbo.Data D
WHERE D.item = @item
)
END;
GO
-- Error if PERSITED keyword is uncommented:
-- Msg 4934, Level 16, State 3, Line 1
-- Computed column 'value' in table 'BadIdea' cannot be persisted
-- because the column does user or system data access.
CREATE TABLE dbo.BadIdea
(
row_id INTEGER PRIMARY KEY,
value AS
dbo.BadFunction (row_id)
--PERSISTED
);
GO
INSERT dbo.BadIdea(row_id)
SELECT V.number
FROM master.dbo.spt_values V
WHERE V.type = N'P ';
GO
-- Trace in Profiler to see separate function call
-- and query execution per row. (Does not show in
-- SSMS actual query plan).
-- Trace SQL:Batch Starting and SP:Starting
-- Shows 2048 separate calls to the UDF BadFunction.
-- Notice how long this simple query takes to run
-- on only 2048 rows.
SELECT MIN(value)
FROM dbo.BadIdea;
GO
DROP TABLE dbo.BadIdea;
DROP FUNCTION dbo.BadFunction;
DROP TABLE dbo.Data;





Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
ALZDBA
ALZDBA
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47843 Visits: 9075
Paul White NZ (11/15/2010)


If this message still is not understood, there will be no other means except for the server to blow up w00t

Thanks Paul for this clarification.

I still suffer SPMOS (SQLP*** Memory Overload Symptoms )
I'll need another couple of days until common sense returns. ;-)

Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search