• 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:

    To add to the explanation: just because you can doesn't mean you should. There are good reasons why the only way to reference another table is through a workaround that's complicated enough to fool SQL Server. Querying a table with such a computed column is dog-slow, since the UDF will be evaluated once for each row. This does not show on the execution plan or in the SET STATISTICS IO output, but you can see it when using Profiler. I checked the sample code that the explanation references, and Profiler shows that the CCTest table is scanned once (obvously) - and that there are five full table scans of the LeaveBalance table. Proper indexing would change that to five index seeks, but it will still have a drastic effect on performance if the row count goes above five.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/