January 31, 2013 at 5:55 am
nice simple question.
I wonder why MS has this restriction?
Tom
January 31, 2013 at 6:10 am
L' Eomot Inversé (1/31/2013)
I wonder why MS has this restriction?
Hi tom,
I don't think there will be a real need of chaining computed columns. Whatever experssion may be, we can incorporate in the single column itself.
This is just my assumption.
Tom, Please let me know if there are any real time requiremnt which really need chaining of computed columns, so that I can rectify my assumption.
--
Dineshbabu
Desire to learn new things..
January 31, 2013 at 6:12 am
Thanks for the question.
January 31, 2013 at 7:21 am
Dineshbabu (1/31/2013)
L' Eomot Inversé (1/31/2013)
I wonder why MS has this restriction?Hi tom,
I don't think there will be a real need of chaining computed columns. Whatever experssion may be, we can incorporate in the single column itself.
This is just my assumption.
Tom, Please let me know if there are any real time requiremnt which really need chaining of computed columns, so that I can rectify my assumption.
I don't know... I would sure have liked the ability to reference a computed column with another one. Things like calendar dimension tables being a prime use to me for something like that. My DW's calendar dimension has several periods type tables... day_periods, week_periods, month_periods that perform calculations to determine how many of said period a given date is in the past from the current date. I have text values of the same thing, but they do things like 'current week', 'prior week', '2 weeks ago'. If I could reference the calculation for the periods as integers and not have to replicate all of that logic for the next column it would have made looking at my table definitions easier.
January 31, 2013 at 7:26 am
L' Eomot Inversé (1/31/2013)
nice simple question.I wonder why MS has this restriction?
My guess is that is makes it much easier if they don;t need to deal with the possibility of an unknown number of successively computed columns
e.g. column A depends on B ; B depends on C, C depends on D and so on.
Also, it means they don't need to consider the possiblity of a chain of 'circular references' :
e.g. A depends on B , B depends on C, C depends on D , and D depends on A
January 31, 2013 at 9:21 am
archie flockhart (1/31/2013)
L' Eomot Inversé (1/31/2013)
nice simple question.I wonder why MS has this restriction?
My guess is that is makes it much easier if they don;t need to deal with the possibility of an unknown number of successively computed columns
e.g. column A depends on B ; B depends on C, C depends on D and so on.
Also, it means they don't need to consider the possiblity of a chain of 'circular references' :
e.g. A depends on B , B depends on C, C depends on D , and D depends on A
This is my guess, too. While MS could code SQL Server to allow computed column definitions to be dependent on other computed column definitions, it would require some logic for sorting out which computed column value has to be computed first to anchor the dependency chain and error trapping for circular references or other "forward dependency" situations. It's certainly a lot easier from MS's standpoint, and not too burdensome on users, to say,
"If you want something like this:
CREATE TABLE (colA int, colB int, colC AS colA + colB, colD AS colE * colA, colE AS colC * colB),
just do this:
CREATE TABLE (colA int, colB int, colC AS colA + colB, colD AS ((colA + colB) * colB) * colA , colE AS (colA + colB) * colB)."
(I think I wrote equivalent computed column definitions in each statement - at least that's what I meant to do!)
Jason Wolfkill
January 31, 2013 at 11:00 am
archie flockhart (1/31/2013)
L' Eomot Inversé (1/31/2013)
nice simple question.I wonder why MS has this restriction?
My guess is that is makes it much easier if they don;t need to deal with the possibility of an unknown number of successively computed columns
e.g. column A depends on B ; B depends on C, C depends on D and so on.
Also, it means they don't need to consider the possiblity of a chain of 'circular references' :
e.g. A depends on B , B depends on C, C depends on D , and D depends on A
Maybe that is the reason: but it would easily be fixed by insisting that any computed column declared in the table definition could only refer to columns defined at an earlier point in the text of the create table statement, and any computed column added in Alter table could only refer to non-computed columns either added earlier in the alter table statement or columns existing in the table as it was before the alter table statement and not dropped in the alter table statement. The existing (at SQL 2008 R2) code for alter table contains a check that no column referenced by a computed column is dropped unless the referencing computed column itself is also dropped, and that is a sufficient rule to handle drop column even with chained column definitions. The existing (2008 R2) code also prevents a computed column or any column referenced by a compted column from being the subject of an ALTER COLUMN clause.
Since any order of column definitions which obeys the suggested rules is also a safe computing order and is certainly cycle-free, the metadata can simply include the order in which the columns were defined - there is no need to compute a safe order at run time, only to record it at computed column creation time for use at run time.
So the issues you suggest they are avoiding by forbidding chained computed column definitions could instead be avoided easily with simple constraints on the text of commands create table and alter table which may contain multiple computed column definitions.
Tom
January 31, 2013 at 2:15 pm
Dineshbabu (1/31/2013)
L' Eomot Inversé (1/31/2013)
I wonder why MS has this restriction?Hi tom,
I don't think there will be a real need of chaining computed columns. Whatever experssion may be, we can incorporate in the single column itself.
This is just my assumption.
Tom, Please let me know if there are any real time requiremnt which really need chaining of computed columns, so that I can rectify my assumption.
It's quite clear that T-SQL is Turing Complete except in so far as it can have only finite resources, so there can be no argument that any functionality is missing that prevents certain things from being done - it may make it harder to do them, but it can't make them impossible in principle. If the storage required to do something is more than SQL Server can handle, or the time it would take to do it would be longer that the remaining life of the universe, adding new features to T-SQL won't fix that. If it isn't possible to do something because it is not computable using lambda calculus then Church's thesis suggests that it is impossible to compute it at all, so adding features to T-SQL wouldn't allow T-SQL to do it. Besides, we know how to get the same effect as chaining computed solumns (in at least three different ways - nested views, chained CTEs, repeated expressions, and probably a few more ways).
So many people will say there is no need for such a feature.
From my point of view, those people are wrong. I started in IT doing work on language design, and I learned early on (as does everyone who works in that field) that technical completeness or the ability to do things using complicated constructs or in a way that necessitates having multiple copies of the same source reduces development effectiveness (productivity, quality of delivered product, and cost of maintenance and enhancement). So language design should not exclude a feature that would remove complexity or reduce repetition in source code unless the sonst of the feature would be enough to offset those gains. Chained computed columns would do those things, so the question is whether the added complexity of the T-SQL system (documentation, optimiser, data engine, parser, etc) is great enough to offset the value of the reduced source complexity of things coded in SQL.
Some people will claim that the gains are very small, by producing trivial examples , for example where the most complex expression for a computed value contains a single operator with two arguments and the maximum chaining depth would be two. Real life is unlikely to be like that - the expressions might be quite long and complex, and the chaining depth might be rather higher. To evaluate whether the feature would be worth providing, it would be neccessary to get information from people who would want to use it to find how many had cases where the real complexity/verbosity imposed by the absence of the feature is a significant problem. Until someone carries out such a survey (maybe MS have already done so, I don't know) and publishes the results it's impossible to say whether it would be worth providing the feature.
Tom
January 31, 2013 at 2:34 pm
L' Eomot Inversé (1/31/2013)
Dineshbabu (1/31/2013)
L' Eomot Inversé (1/31/2013)
I wonder why MS has this restriction?Hi tom,
I don't think there will be a real need of chaining computed columns. Whatever experssion may be, we can incorporate in the single column itself.
This is just my assumption.
Tom, Please let me know if there are any real time requiremnt which really need chaining of computed columns, so that I can rectify my assumption.
<snipped>
So many people will say there is no need for such a feature.
From my point of view, those people are wrong. I started in IT doing work on language design, and I learned early on (as does everyone who works in that field) that technical completeness or the ability to do things using complicated constructs or in a way that necessitates having multiple copies of the same source reduces development effectiveness (productivity, quality of delivered product, and cost of maintenance and enhancement). So language design should not exclude a feature that would remove complexity or reduce repetition in source code unless the sonst of the feature would be enough to offset those gains. Chained computed columns would do those things, so the question is whether the added complexity of the T-SQL system (documentation, optimiser, data engine, parser, etc) is great enough to offset the value of the reduced source complexity of things coded in SQL.
Some people will claim that the gains are very small, by producing trivial examples , for example where the most complex expression for a computed value contains a single operator with two arguments and the maximum chaining depth would be two. Real life is unlikely to be like that - the expressions might be quite long and complex, and the chaining depth might be rather higher. To evaluate whether the feature would be worth providing, it would be neccessary to get information from people who would want to use it to find how many had cases where the real complexity/verbosity imposed by the absence of the feature is a significant problem. Until someone carries out such a survey (maybe MS have already done so, I don't know) and publishes the results it's impossible to say whether it would be worth providing the feature.
At the risk of being labeled a cynic, the decision to add functionality that would allow users to accomplish more efficiently a task they can already do with existing functionality is an economic one. Microsoft incurs no direct costs in telling users to use existing functionality, but the users incur the costs of inefficiency each time they have to do so. It becomes worth it to Microsoft to add the new functionality only when the users' costs of inefficiency become so great that they turn to alternative products to meet their needs. Of course, both Microsoft and users typically consider the product as a whole rather than making decisions based on a single feature, but until users reach their pain point with existing but inefficient functionality, Microsoft has no incentive to add new functionality to save users the trouble.
Jason Wolfkill
January 31, 2013 at 2:47 pm
Nice and easy..
Thanks
January 31, 2013 at 3:18 pm
Nice Question..
Thanks..
January 31, 2013 at 7:03 pm
L' Eomot Inversé (1/31/2013)
It's quite clear that T-SQL is Turing Complete except in so far as it can have only finite resources, so there can be no argument that any functionality is missing that prevents certain things from being done - it may make it harder to do them, but it can't make them impossible in principle. If the storage required to do something is more than SQL Server can handle, or the time it would take to do it would be longer that the remaining life of the universe, adding new features to T-SQL won't fix that. If it isn't possible to do something because it is not computable using lambda calculus then Church's thesis suggests that it is impossible to compute it at all, so adding features to T-SQL wouldn't allow T-SQL to do it. Besides, we know how to get the same effect as chaining computed solumns (in at least three different ways - nested views, chained CTEs, repeated expressions, and probably a few more ways).
Oh Tom, you had at me at lambda calculus! 🙂
Life was so much simpler in grad school, when we could deal with theory - simple, clean. And then we went out into the world, only to be surrounded by the unwashed.
Thanks for reminding me of the good old days.
Now my day is spent trying to get around a back-end to an ERP system that's a direct transfer from a flat file implementation - no primary keys, no triggers (all business logic handled by the front end!), repeating groups, semantically overloaded fields, etc. It's enough to make you want to hit the programmers over the head with something that is NP hard.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
January 31, 2013 at 10:08 pm
L' Eomot Inversé (1/31/2013)
Dineshbabu (1/31/2013)
L' Eomot Inversé (1/31/2013)
I wonder why MS has this restriction?Hi tom,
I don't think there will be a real need of chaining computed columns. Whatever experssion may be, we can incorporate in the single column itself.
This is just my assumption.
Tom, Please let me know if there are any real time requiremnt which really need chaining of computed columns, so that I can rectify my assumption.
It's quite clear that T-SQL is Turing Complete except in so far as it can have only finite resources, so there can be no argument that any functionality is missing that prevents certain things from being done - it may make it harder to do them, but it can't make them impossible in principle. If the storage required to do something is more than SQL Server can handle, or the time it would take to do it would be longer that the remaining life of the universe, adding new features to T-SQL won't fix that. If it isn't possible to do something because it is not computable using lambda calculus then Church's thesis suggests that it is impossible to compute it at all, so adding features to T-SQL wouldn't allow T-SQL to do it. Besides, we know how to get the same effect as chaining computed solumns (in at least three different ways - nested views, chained CTEs, repeated expressions, and probably a few more ways).
So many people will say there is no need for such a feature.
From my point of view, those people are wrong. I started in IT doing work on language design, and I learned early on (as does everyone who works in that field) that technical completeness or the ability to do things using complicated constructs or in a way that necessitates having multiple copies of the same source reduces development effectiveness (productivity, quality of delivered product, and cost of maintenance and enhancement). So language design should not exclude a feature that would remove complexity or reduce repetition in source code unless the sonst of the feature would be enough to offset those gains. Chained computed columns would do those things, so the question is whether the added complexity of the T-SQL system (documentation, optimiser, data engine, parser, etc) is great enough to offset the value of the reduced source complexity of things coded in SQL.
Some people will claim that the gains are very small, by producing trivial examples , for example where the most complex expression for a computed value contains a single operator with two arguments and the maximum chaining depth would be two. Real life is unlikely to be like that - the expressions might be quite long and complex, and the chaining depth might be rather higher. To evaluate whether the feature would be worth providing, it would be neccessary to get information from people who would want to use it to find how many had cases where the real complexity/verbosity imposed by the absence of the feature is a significant problem. Until someone carries out such a survey (maybe MS have already done so, I don't know) and publishes the results it's impossible to say whether it would be worth providing the feature.
Hi Tom,
While posting my reply itself I expected you will hit my head with such a breif explaination and make me to rectify my assumption.
Yes Tom, I accept most of the functions in programming langauges was introduced to reduce code redundancy, and to make the code simple and effective.
--
Dineshbabu
Desire to learn new things..
February 4, 2013 at 11:44 am
bitbucket-25253 (1/30/2013)
Thanks for a clear, concise QOD .. and good to be reminded of the basics,
+1
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
February 4, 2013 at 2:18 pm
Good Basic One.
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply