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


Computed Columns 1


Computed Columns 1

Author
Message
palotaiarpad
palotaiarpad
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2083 Visits: 773
In my reading, you can't reference a column, you can reference an UDF only. Angry
dawryn
dawryn
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3369 Visits: 909
Hugo Kornelis (1/31/2012)

"Is this a question about the official rules and limitations, or about workarounds?" -- I guessed wrong. Crying

As the question is worded it seems about the official rules:
- referencing column in another table as such is not possible
- getting values from column in another table somehow is possible.
I knew about latter but wording got me Doze
kaspencer
kaspencer
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2475 Visits: 836
I agree wholeheartedly with Daniel Fountain. SQLServerCentral's Question of the Day really should not be about trickery.

I entered "No", but at the back of my mind, I was considering whether there might be a relatively obscure trick here that I had missed.

Get rid of the tricksters in our business, that's what I say.

Ken.

You never know: reading my book: "All about your computer" might just tell you something you never knew!
lulu.com/kaspencer
michael.kaufmann
michael.kaufmann
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1323 Visits: 1082
Hugo Kornelis (1/31/2012)
Meh, I don't like questions where people who really understand the subject have a 50/50 chance of getting it right because they have to second-guess the author's intention.

"Is this a question about the official rules and limitations, or about workarounds?" -- I guessed wrong. Crying

+1
Guess it's down to language subtleties again--while BOL uses 'compute' to describe the behavior, the author of the question used 'reference' (with 'not persisted' being irrelevant for the scope of the question). So from a language perspective, that's two different things/meanings...

Thanks for an interesting question that sparked an even more interesting and enlightening discussion.

PS: And no, I do not want my point back.
Nakul Vachhrajani
Nakul Vachhrajani
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2222 Visits: 2143
The way I interpreted the question, it was all about whether or not computed column definitions can contain references to columns of other tables. The answer, according to Books On Line is NO.

Yes, there is a workaround, but it's just that - a workaround. It's not really a valid answer to this question because the computed column definition referenced a UDF - not an external column.

I have an issue when the whole purpose of the question is to trick the reader. A rephrasing of the question could have avoided the second-guessing of intentions.

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
cengland0
cengland0
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1668 Visits: 1300
Hugo Kornelis (1/31/2012)
Meh, I don't like questions where people who really understand the subject have a 50/50 chance of getting it right because they have to second-guess the author's intention.

"Is this a question about the official rules and limitations, or about workarounds?" -- I guessed wrong. Crying

I was aware of the ability to do it indirectly so I answered that it was possible and guessed correctly. If I got it wrong, I was prepared to give examples of how it could be done.

Some of these QOTD's are tough because you don't know if the author is using BOL or their own experience to generate the question. Just because it's in BOL doesn't mean there isn't a workaround such as using a UDF to access another table's column.

For the person referencing msdn's page http://msdn.microsoft.com/en-us/library/ms191250.aspx :

It does not state you cannot access columns from another table, it says:

A computed column is computed from an expression that can use other columns in the same table.

Notice it says "can use" instead of "must use"

It also goes on to say:

The expression can be a noncomputed column name, constant, function, and any combination of these connected by one or more operators.

Notice that it specifically states you can use a function. As we all know, the function can derive information from any of your tables.
ipounder
ipounder
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1339 Visits: 177
Just because you CAN trick SQL Server into allowing a computed column to indirectly reference columns in other tables does not mean you should. My strong suspicion is that if you need to do this then the basic database design is inadequate to meet the business and performance requirements.

Similarly, I get very suspicious of people embedding business logic in databases. I am not saying it should never be done, but you need to ask yourself several times if it really is the best place for it. A closed 'stove-pipe' application may warrant it, but as data becomes more general in its use, locking a database to the specific functionality of one part of the business restricts its usefulness to the rest of the organisation.
paul.goldstraw
paul.goldstraw
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1302 Visits: 1765
The link you referred to said it for me

"A computed column can not directly access any column outside its table. This limitation may be overcome by using a User Defined Function"

The computed column is not referencing the column in another table, the UDF is doing that. I was another person who knew the answer but struggled due to the rigidness of the available options. Technically it is possible to get access to that data, but not without using an intermediate piece of code

Paul
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15544 Visits: 11354
Hugo Kornelis (1/31/2012)
There are good reasons why the only way to reference another table is through a workaround that's complicated enough to fool SQL Server.

I wish this feature did not exist, but it is incorrect to say it is a workaround, or that it is fooling SQL Server. This is, unfortunately, a supported and intentional feature. If scalar functions were implemented differently, it might even be a very useful one, but I digress. At the risk of spoiling Ron's series of questions, consider the following daft function, and a table that references it using a computed column:


CREATE FUNCTION dbo.f()
RETURNS integer AS
BEGIN
RETURN
(
SELECT TOP (1)
p.ProductID
FROM Production.Product AS p
ORDER BY
p.ProductID
);
END;
GO
CREATE TABLE dbo.T1 (a integer NULL, z AS dbo.f());


Now try to create an index on the computed column:
--Msg 2729, Level 16, State 1, Line 1
--Column 'z' in table 'T1' cannot be used in an index or statistics
--or as a partition key because it is non-deterministic.
CREATE INDEX i ON T1 (z);


Fine, the function is non-deterministic:
-- Returns 0
SELECT OBJECTPROPERTYEX(OBJECT_ID(N'dbo.f', N'FN'), 'IsDeterministic');


Let's make the function schema-bound so SQL Server inspects it for determinism:

DROP TABLE dbo.T1;
GO
ALTER FUNCTION dbo.f()
RETURNS integer
WITH SCHEMABINDING AS
BEGIN
RETURN
(
SELECT TOP (1)
p.ProductID
FROM Production.Product AS p
ORDER BY
p.ProductID
);
END;
GO
-- Returns 1 now!
SELECT OBJECTPROPERTYEX(OBJECT_ID(N'dbo.f', N'FN'), 'IsDeterministic');


So can we create an index now?

CREATE TABLE T1 (a int, z AS dbo.f());
--Msg 2709, Level 16, State 1, Line 1
--Column 'z' in table 'T1' cannot be used in an index or statistics
--or as a partition key because it does user or system data access.
CREATE INDEX i ON T1 (z);


No go (and making it PERSISTED makes no difference either). As the error message shows, functions are deliberately allowed in computed column definitions, but they cannot currently be indexed (think how SQL Server would go about maintaining that index!)



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15544 Visits: 11354
The criticism of the question's wording is just a little on the harsh side for my taste. After all, this is a one-point question with a 50/50 choice. Perhaps English is not the right language for QotD? Perhaps we need our own...

EXISTS[Method[ComputedColumn(TableA)Tongueersisted="0"[Accesses(TableB:AnyColumn)]]]



Anyway, it's only one point, and everyone knows the real action happens in the QotD discussion, right? ;-) Cool



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
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