January 31, 2012 at 3:33 pm
This is what MSDN says (http://msdn.microsoft.com/en-us/library/ms191250.aspx):
A computed column is computed from an expression that can use other columns in the same table. The expression can be a noncomputed column name, constant, function, and any combination of these connected by one or more operators. The expression cannot be a subquery.
This gives a clear answer to today's question. Playing around with functions may be used to contradict every single condition mentioned here: you could reference not only columns of other tables but even computed columns, although the explanation of computed columns is completely different.
I personally do not learn much from questions of these kind. For me it is not helpful if I am (in my opinion intentfully) mislead. Considering the QotD it was a wasted day for me.
Best regards,
Dietmar Weickert.
January 31, 2012 at 7:24 pm
The question is "Can a non persisted computed column reference column(s) that are NOT in the same table as the computed column.? "
http://msdn.microsoft.com/en-us/library/ms191250.aspx says "A computed column is computed from an expression that can use other columns in the same table."
My english might be bad, but not bad enough to not understand what is written: computed column computes!
The expression used in the computation can use other column, but those "other columns" has to be in the same table.
The word "reference" in the question, from my point of view, means "use".
If you need a password to log on a computer, is that computer protected? Yes, but we agree that some bad people can go around it.
Finally, it does not matter: I wrote my comments to get back my point 😎
February 1, 2012 at 1:12 am
However the question was still wrong as you still dont reference another column. You reference a UDF.
SQL Kiwi (1/31/2012)
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 0SELECT 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!)
February 1, 2012 at 1:15 am
danielfountain (2/1/2012)
However the question was still wrong as you still dont reference another column. You reference a UDF.
Meh, semantics.
February 1, 2012 at 1:18 am
Hm, after some thinking i got it right :), although it is a kind of trick question as, to my knowledge, you cannot directly reference columns in another table, only through UDF.
February 2, 2012 at 5:53 am
Hi Friends,
Please do not bother about the points. Ultimately it has shown us a different way of referencing a non persistent computed column reference. But obviously performance wise it is bad.
February 2, 2012 at 10:32 pm
tricky question. a non persistent column cannot reference a column from another table but the example in the blog does not use a column from the current table but a std function for calculation. so the question is not correct I think.
February 3, 2012 at 1:56 pm
Good question as it led to alot of interesting discussion which is usually the most interesting part of the QoTD's.
Just to add another twist to the phrasing of the question.
"Can a non persisted computed column reference column(s) that are NOT in the same table as the computed column.?"
If the computed column is "non persisted" it does not reside in any table but is created each time the query is run.
February 3, 2012 at 2:39 pm
jlennartz (2/3/2012)
Good question as it led to alot of interesting discussion which is usually the most interesting part of the QoTD's.
I learn much more through the QOTD discussions than I do with the QOTD itself. Many of the experts show better ways of doing things, point out that the code doesn't work in their country, explains that it only works with specific default options set, and sometimes provides better URL references than the included BOL links.
February 3, 2012 at 2:50 pm
cengland0 (2/3/2012)
jlennartz (2/3/2012)
Good question as it led to alot of interesting discussion which is usually the most interesting part of the QoTD's.I learn much more through the QOTD discussions than I do with the QOTD itself. Many of the experts show better ways of doing things, point out that the code doesn't work in their country, explains that it only works with specific default options set, and sometimes provides better URL references than the included BOL links.
Nicely put.
February 3, 2012 at 4:01 pm
cengland0 (2/3/2012)
jlennartz (2/3/2012)
Good question as it led to alot of interesting discussion which is usually the most interesting part of the QoTD's.I learn much more through the QOTD discussions than I do with the QOTD itself. Many of the experts show better ways of doing things, point out that the code doesn't work in their country, explains that it only works with specific default options set, and sometimes provides better URL references than the included BOL links.
Tha's why I do QotD every weekday, any why I could justify to my boss that it is productive time.
February 7, 2012 at 8:04 am
Sometimes, having LESS experience is helpful. I took the question at face value with no regard for semantics.
"Can a non persisted computed column reference column(s) that are NOT in the same table as the computed column.?"
I saw no reason it could not, but the question got me thinking... WHY would it be bad to reference columns from another table? I began to think about normalization and the fact that maybe the computed column might belong somewhere else, especially if ALL the columns in the UDF came from the same table. I cannot think of a real world example right now where this would make sense. That is just the path my brain went down...
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
February 7, 2012 at 12:20 pm
Peter Trast (2/7/2012)
I cannot think of a real world example right now where this would make sense.
Real-world examples are easy enough to find. For instance, the current balance of an account can be calculated from the opening balance (both in the accounts table) and the sum of all tranactions (different table).
But there are technical challenges for allowing this as a computed column. If it's not persisted, any access to the table would automatically involve access to several rows in the other table. And if it is persisted, any change to the table of transactions would involve changing the current balance in the accounts table. This would be a fairly simple change to propagate (which is why you could implement this as an indexed view), but there are lots of scenarios where propagating changes to persisted computed columns becomes a lot more complex. (I know - because I get paid to think about and solve this kind of issues).
January 11, 2013 at 5:11 am
Since i'm not well versed with computed columns , referred BOL and answered NO. But SSC kicked me out saying Wrong.
But after reading all the threads posted here by experts says I'm correct.
--
Dineshbabu
Desire to learn new things..
Viewing 14 posts - 31 through 43 (of 43 total)
You must be logged in to reply to this topic. Login to reply