Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Computed Columns 1 Expand / Collapse
Author
Message
Posted Tuesday, January 31, 2012 2:22 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 28, 2014 12:22 AM
Points: 1,298, Visits: 471
In my reading, you can't reference a column, you can reference an UDF only.
Post #1244137
Posted Tuesday, January 31, 2012 2:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 10, 2014 1:48 AM
Points: 2,359, Visits: 730
Hugo Kornelis (1/31/2012)

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

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
Post #1244140
Posted Tuesday, January 31, 2012 2:33 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:54 AM
Points: 1,710, Visits: 463
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.
Post #1244145
Posted Tuesday, January 31, 2012 2:45 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 9, 2013 11:12 PM
Points: 1,263, Visits: 1,081
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.

+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.
Post #1244152
Posted Tuesday, January 31, 2012 2:59 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:27 AM
Points: 1,405, Visits: 1,815
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://beyondrelational.com/modules/2/blogs/77/nakuls-blog.aspx
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
Post #1244162
Posted Tuesday, January 31, 2012 3:25 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354, Visits: 1,299
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.

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.
Post #1244181
Posted Tuesday, January 31, 2012 4:02 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 3, 2014 9:52 AM
Points: 1,319, Visits: 176
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.
Post #1244213
Posted Tuesday, January 31, 2012 4:19 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 4:47 AM
Points: 1,030, Visits: 1,395
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
Post #1244220
Posted Tuesday, January 31, 2012 5:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:50 AM
Points: 11,192, Visits: 11,098
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1244244
Posted Tuesday, January 31, 2012 5:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:50 AM
Points: 11,192, Visits: 11,098
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1244254
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse