December 10, 2017 at 7:01 am
Hi,
With some googling, I learnt that SQL Server doesn't have persisted column names (like Netezza supports, for instance), which demands each of the computed columns to be (re)calculated as standalone atomic elements, which expects us to re-code the same syntax/logic.
For example:
1) Let's say, I am joining two tables and populating a 3rd table to feed 10 columns.
2) Columns 6, 7, 8, 9, 10 are computed columns.
3) Column 7 depends on column 6's value. Similarly, 8 depends on 7. 9 depends on 8. 10 depends on 9.
4) In T-SQL (2014), I think, to compute Column 7 - we have to physically REPEAT the code that was used for calculating Column 6. And, so forth.
IS THIS CORRECT?
If this is correct, how do we make sure there is no performance hit? Are there any alternate mechanisms than re-coding logic?
thank you
December 10, 2017 at 7:17 am
etl2016 - Sunday, December 10, 2017 7:01 AMHi,
With some googling, I learnt that SQL Server doesn't have persisted column names (like Netezza supports, for instance), which demands each of the computed columns to be (re)calculated as standalone atomic elements, which expects us to re-code the same syntax/logic.
For example:
1) Let's say, I am joining two tables and populating a 3rd table to feed 10 columns.
2) Columns 6, 7, 8, 9, 10 are computed columns.
3) Column 7 depends on column 6's value. Similarly, 8 depends on 7. 9 depends on 8. 10 depends on 9.
4) In T-SQL (2014), I think, to compute Column 7 - we have to physically REPEAT the code that was used for calculating Column 6. And, so forth.
IS THIS CORRECT?
If this is correct, how do we make sure there is no performance hit? Are there any alternate mechanisms than re-coding logic?
thank you
Firstly, computed columns can be persisted (link), so that is not an issue, and neither is performance.
So we come to the question of cut & paste regarding code, and, with that, you are correct. Are the calculations so complex that they cannot reasonably be coded in a view?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 11, 2017 at 8:21 am
It sounds like he's talking about in a SELECT query. Unfortunately, the answer remains the same -- you can't have the 7th column output of your query depend on the results of the 6th column formula, etc. The way to accomplish this, if you truly want to, would be using nested SELECTs.
-Nate the DBA natethedba.com
December 11, 2017 at 8:34 am
Nate the DBA - Monday, December 11, 2017 8:21 AMIt sounds like he's talking about in a SELECT query. Unfortunately, the answer remains the same -- you can't have the 7th column output of your query depend on the results of the 6th column formula, etc. The way to accomplish this, if you truly want to, would be using nested SELECTs.
Using several CROSS APPLYs in such a situation is tidier, at least to my eyes.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 11, 2017 at 10:14 am
Phil Parkin - Monday, December 11, 2017 8:34 AMUsing several CROSS APPLYs in such a situation is tidier, at least to my eyes.
Ooh, yes I like that better too. =)
-Nate the DBA natethedba.com
December 11, 2017 at 12:34 pm
Your approached RDBMS is completely wrong. SQL and the relational model are based on sets. That means that things happen "all at once" and not row by row or record by record, as in the old filesystems. Do you think that the SELECT list in a query is constructed in left to right order? No! The whole row in that list comes into existence all at once.
This is why the statement
UPDATE Foobar
SET a = b,
b = a;
Will swap the values in columns A and B. But if it worked theway you think it does, we would have gotten an execution pattern like this:
start with a=2 and b=3
then "b=a"
gives us a=2, b=2
then "a=b"
gives us a=2, b=2
If you're serious about pursuing this, then look up some of the work that Ed Dijkstra did on this kind of assignment. You can get his collected papers at the University of Texas website as PDFs. As far as I know, he was the first person to define the rules for simultaneous assignment of structures.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply