January 31, 2012 at 3:25 am
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.
January 31, 2012 at 4:02 am
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.
January 31, 2012 at 4:19 am
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
January 31, 2012 at 5:15 am
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
SQLkiwi blog
@SQL_Kiwi
January 31, 2012 at 5:29 am
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):Persisted="0"[Accesses(TableB:AnyColumn)]]]
Anyway, it's only one point, and everyone knows the real action happens in the QotD discussion, right? 😉 😎
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 31, 2012 at 5:31 am
Koen Verbeeck (1/30/2012)
Hmmmm, this is arguable.MSDN says that it can only use columns for the same table.
And a computed column can't reference a column outside another table, it can reference a UDF which on his part references columns from other table.
So the question is a bit ambigous, as it is not clear if direct or indirect reference is meant.
No, it's worse than that. The explanation and the article it links to have nothing to do with referencing columns in other tables, so are completely irrelevant to the question.
However, the YES answer is correct. It has nothing to do with indirect use (via a UDF) of a column value from another table to get the value of the computed column. It has to do with referencing column(s) in another table, which is what the question is about.
Since "references" is an SQL keyword with a clear and unambiguous meaning, and since we talk about "referential transparency", the meaning of "column A references column B" is clearly that there is a foreign key constraint. So bitbucket has not asked the question he meant to ask, but a different (and easier) question: "can a persisted columns support foreign key constraints?".
As I said above, the answer is unambiguously YES, as described on the BoL Create Table page. But as that's hard reading, here's some DDL that makes a computed column directly reference two columns in another table without any jiggery-pokery with UDFs:
create table A(
KA int primary key,
V nvarchar(22),
MULTIPLIER tinyint not null,
UA AS KA*MULTIPLIER persisted not null unique
)
create table B(
KB int primary key,
OFFSET int,
FKBKA AS KB+OFFSET persisted references A(KA),
CONSTRAINT FKBUA FOREIGN KEY (FKBKA) references A(UA)
)
(NB DDL provided only as demonstration of concept, with no intention of defining any useful tables)
Tom
January 31, 2012 at 5:51 am
L' Eomot Inversé (1/31/2012)
So bitbucket has not asked the question he meant to ask, but a different (and easier) question: "can a persisted columns support foreign key constraints?".
:laugh:
Just leave it to Tom to come up with an interpretation that nobody else saw coming! Nicely done, Tom!
(Unfortunately, Ron explicitly mentioned non persisted columns in the question, which changes the answer in this surprising new interpretation completely...)
January 31, 2012 at 6:05 am
SQL Kiwi (1/31/2012)
Anyway, it's only one point, and everyone knows the real action happens in the QotD discussion, right? 😉 😎
That of course is where the real points are:cool::w00t:
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 31, 2012 at 6:22 am
SQL Kiwi (1/31/2012)
...Perhaps English is not the right language for QotD?...Anyway, it's only one point, and everyone knows the real action happens in the QotD discussion, right?
I seem to recall from the MCSE tests that Microsoft can play games with English as well. Most of the time, the questions aren't that hard...once you figure out exactly what they are asking. Having said that, my own preference would be to not have "tricky" questions in the QOTD. I just don't feel that it's completely fair to single out the QOTD author when MS themselves have issues in this area.
BTW, I totally agree that the real value in the QOTD lies in the discussion forums and the associated links/examples from both BOL and others! 😀
January 31, 2012 at 8:53 am
tks for the question and the discussion today.
+1 for interpreting the question as directly accessing fields in other tables... doh!
January 31, 2012 at 9:19 am
MSDN states (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."
A little thinking here made me realize that nowhere it reads "a computed column MUST USE columns in the same table", so it was obvious to me that there would be a way to use them.
Tricky question though.
BTW, those who want their points back can just keep posting and will get more points than awarded by the QotD.
🙂
January 31, 2012 at 9:28 am
A simple yer or no, and I got it wrong... Thanks, Bitbucket -- a good lesson!
January 31, 2012 at 9:53 am
Thanks for the question. I wouldn't have thought to use a UDF this way.
January 31, 2012 at 10:20 am
Revenant (1/31/2012)
A simple yer or no, and I got it wrong... Thanks, Bitbucket -- a good lesson!
My thanks to you. I have followed your posting here on SSC and have learned to great deal from them ....
January 31, 2012 at 10:26 am
Hugo Kornelis (1/31/2012)
(Unfortunately, Ron explicitly mentioned non persisted columns in the question, which changes the answer in this surprising new interpretation completely...)
Woops, I was so diverted by "reference" that I didn't notice the exclusion of persisted columns. So the right answer was NO after all.
Tom
Viewing 15 posts - 16 through 30 (of 43 total)
You must be logged in to reply to this topic. Login to reply