InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 15:06:38 GMT20http://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspx[quote][b]Thomas-282729 (12/15/2011)[/b][hr][quote][b]L' Eomot Inversé (12/15/2011)[/b][hr][quote]The problem that the question whether data is correct is a business problem. It can only be ascertained or tested by examining business rules and can only be ensured by enforcing business rules. [/quote]And we're back to the kitchen sink definition of "business logic".[/quote]I haven't a clue why you say that. I really haven't. If some logic is required by business rules, and would not be required if the business rules were different, it's business logic. If some logic is required regardless of what the business rules are, it's not business logic. If some logic is not required by any business rule but by a developer's sense of aesthetics it is not business logic. If some logic is required only by some prima donna's desire to achieve incredibly fast performance even though that fast performance isn't needed and even if the time taken to achieve it doesn't wreck the project the security and integrity flaws allowed just to get this unwanted performance will that's not business logic, it's what might be called anti-business logic. Is that clear enough? Do you see any kitchen sinks there? [quote]In effect, what you are saying is that if you can express the logic using declarative features, then it should be in the database but this definition may be too narrow. What about logic implemented through triggers? What about database products that implement the ASSERTION keyword where you can in effect create check constraints that span tables? What about database products that do not implement check constraints but may implement triggers?You seem to be stuck in the "how" the logic is implemented instead of the "where" it belongs or "why" it does or does not belong there. [/quote]What I said was that in the case of T-SQL the things that normally represent certain concepts of relational models are various things. As I expressed the idea in terms of relational concepts and gave a quick off-the-cuff description of what it covered in T-SQL it thought it would be clear that I don't in principal give a toss whether a domain constraint (a relational concept) is implemented in a database by a check constraint, or a type definition, a trigger, or an assertion, or something else because all I'm interested in is the underlying relational concept. The point is that the database has the job of ensuring that only some states of the data are permitted, and which states of the data are permitted is determined not by some magic theory unconnected to the business but by rules imposed by the business. These rules are normally (at the relational level) enforced by various constraints, and at the implementation level by a variety of mechanisms which, naturally, include constraints as well as other things. [quote]The database's purpose is to protect the correctness of the data itself but not necessarily a process that can easily change. Given that tables are sets of facts, it makes sense that the database is designed to ensure the correctness of those facts but not care about how those facts were gathered or what conventions were created to gather those facts. [/quote]No, the database can't ensure the correctness of facts. For example it can't ensure that if John Smith's salary is 80000 Euros per annum the value held in the database is 80000.00, since someone may mistakenly enter it as 80000.05. All it can do is enforce some rules about what states of the database are valid; since individual salaries change from time to time the exact values of individual salaries are not something the database can police. On the other hand, if there is a business rule that all annual salaries lie between 18000 Euros and 500004 Euros per annum it can enforce that business rule as a domain constraint (because in relational algebra that's what that kind of business rule is).[quote][quote]It should also be implemented in the application logic, including the front end, since it is preferable to have things work without the application having to cope with constraint violations reported by the database, and for the user interface to avoid as far as reasonably possible presenting the end user with options that would violate the business rules (and the extremely rare cases when avoidance isn't reasonably possible giving the user instant feedback if there's a problem, rather than waiting for the database to signal a problem).[/quote]Agreed. However, by your use of the phrase "application logic", you have interestingly differentiated it from "business logic". Is it not the case that logic in the application is also "business logic" by your definition?[/quote]I used the word "logic" in the sense "programming"; shouldn't have done that, as it conflicts with my use of it in "business logic".[quote][quote]Application logic other than the above in the database? Well, I don't like exposing the database to ad-hoc queries, nor indeed exposing the schemata to the applications (I want maximal decoupling for modularity). So I prefer the applications to be forced to call stored procedures and have no permissions to read or write any table or view other than by calling the provided stored procedures.[/quote]Agreed but hard to enforce. Typically what blows up this approach is ad-hoc reports.[/quote]It seems to me that we actually actually agree on everything that matters, except terminology.Actually I don't mind exposing the schema (with read-only access, of course) to people for the purpose of ad hoc reports, provided they are genuinely ad hoc and it is accepted that there's no guarantee that an ad-hoc report which worked yesterday will work tomorrow because there's no guarantee we won't change the schema to meet some business-critical objective. I've sold that idea successfully to top management a few times (it tends to be senior VPs and upwards who think they can insist on freezing the schema to make their pet report continue to work without anyone doing any work to maintain it; having the CEO and the COO on board tends to stop that kind of nonsense quite quickly).[quote]But perhaps the fundamental problem with the "no business logic in the database" mantra is that it's used by many DBAs, DB developers, and DB designers as an excuse for making no attempt to understand the business, resulting in catastophically poor outcomes of development projects because without understanding the business they can't understand what data states are forbidden by the business logic or what data volumes are implied by technicalities of teh business and as a result can't design schemata that will provide data integrity and also can't estimate within several orders of magnitude what sort of storage is needed.[/quote]In the case of this thread at least, it comes down to vocabulary. [/quote]Maybe it's a culture difference causing a vocabulary difference. British ideas and American ideas are often very different. Or maybe it's just that you have been lucky and not encountered the sort of lazy idiot I described, so you are happy to exclude those business rules which determine the permitted states of the database from your definition of business logic.[quote]To me, the logic used to ensure data correctness is different than the logic used to manage a business process is different than the logic used by one specific application that implements that logic and to lump multiple items into the bucket of "business logic" is equivalent to using the term "smurf" for any type of logic. [/quote]And to me, a refusal to recognise that a mechanism that enforces some business rules is necessarily an implementation of some business logic is contrary to the meanings of the words and flies in teh face of all commen sense. [quote]Databases last much longer than the applications used to initially write to them so their structure is critical to ensure lasting success. [/quote]Actually they don't in a shop where requirements change continually (most shops are like that) - they change as rapidly as the applications, because what drives change is new business requirements and new business requirements often entail new data. A well designed database evolves easily, rather than having to be torn up and redone because of new requirements, and so does a well designed application. Modularity of design - including schema invisibility - makes it much easier to change the schema to support new things without breaking the old things, and to change the application to provide new features without damaging the old features. Of course the presentation layer may have to be torn up and thrown away to take advantage of new technology - going from ASP to ASP.NET, or from Flash to WPF is for the presentation layer the same sort of upheaval as would be going from IDMS-X to SQLServer for the database; but such things are almost as rare for application and presentation as they are for database. [quote]Each tier should be considered a fiefdom where no tier trusts data from the tier above it which means each tier re-validates the data given to it. When a date value is passed from the presentation tier, the middle-tier (sometimes called the business layer or business tier) re-validates that it is a date and the database again re-validates it. Each tier above the database acts like an emissary ensuring that the data that is sent to the next tier down will not be rejected but ultimately, they do not trust each other.One guideline might be: if a rogue DBA can, without altering DRI or triggers but bypassing stored procedures, execute a query that alter data in such as way as to break other tiers of my application or create obviously wrong or inaccurate data, then the logic to protect that data and prevent that scenario belongs in the database at the most declarative level possible. [/quote]If protection of the data state can be done without writing more DML than a trigger or a scalar function to be used only by a check constraint, then it should be done in the database. If it can be done without any DML, it probably should be.[quote]The database is there to protect the data. Most people do not call that "business logic" because the phrase quickly becomes meaningless. Most developers I have encountered understand the phrase "business logic" to mean logic not related to database data correctness nor related to the presentation tier.[/quote]Well, you've encountered people who are different from some that I have met. And the database isn't just there to protect the data - that's just one of its functions, perhaps its primary function because it can't do any of the others reliably if it doesn't do that one. For example, it is also supposed to provide efficient access to the data (ie deliver the data access performance that the business needs), and enable the modification of existing data and the insertion of new data (just protecting it would be easy - no-one ever gets write access, and we keep lots of copies - but wouldn't be useful for most purposes).Fri, 16 Dec 2011 11:21:46 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspx[quote][b]L' Eomot Inversé (12/15/2011)[/b][hr][quote][b]Thomas-282729 (12/15/2011)[/b][hr]Perhaps it would help if you defined what *isn't* "business logic" that belongs within the confines of a solution for a business. As I said earlier, you are using "business logic" to be equivalent to the word "smurf". It can mean anything and everything that is even remotely related to any decision whatsoever in the business and that type of broad, vague definition does not help to understand what sorts of logic belong in the database and more importantly, what forms of logic do not belong in the database. [/quote]Yes, it is necessary to understand what business logic belongs in the database and what business logic doesn't. Understanding that doesn't however involve declaring that some things which clearly are business logic (for example the decision to so organise the sales team that each salesman can deal in only one product line, or equally the decision not to make the organisation like that, or the decision to leave the question open) are not business logic just because they have implications for constraints which belong in the database - changing the meaning of "business logic" just in order to allow the nonsense statement that the database must contain no business logic to appear to be true.[/quote]You are talking in circles here. Let's stop using the term "business logic" and instead define exactly what types of logic of which you speak because clearly our definition of the phrase is different. As far as I can tell to date, you have no bounds as to what defines and does not define "business logic".[quote][quote]If any and all logic is "business logic" then any form of logic makes sense to put in the database and that clearly is not the case.[/quote]Who has suggested that any and all logic is business logic other than yourself? And whence the enormous nonsequitur that if any and all logic were business than it would be appropriate to put all logic in the database - so far as I know, no-one has made the claim that all business logic should be in the database. Are you perhaps playing at putting up ludicrous strawmen to enable yourself to appear to logical in opposing a position which is nothing like those strawmen?[/quote]Actually, you did when you claimed that logic involving the data model, something that is clearly different than what most people define as "business logic" is just another form of the same. By lumping into the category of "business logic" things that most would not consider as such, you have left us to guess as to what *isn't* included in your definition of the phrase.[quote]I could take your new suggestion as meaning that you want to assert that since all logic is business logic the database should contain no logic of any description, but I don't actually believe that you want to assert that. If you are claiming that I have asserted that you are claiming something which clearly is not true (as should be clear from reading any of my posts). [/quote]Not my assertion but rather my interpretation of what you might mean by the term. Again, without clearly defining what you mean (which is clearly diffrerent than what I mean) by the term "business logic" and given your remarks about table constraints being "business logic", you have left us to guess as to what you do *not* think is business logic.[quote]To take an extreme case I am prepared to assert, for example, the logic used by Godel to demonstrating the consistency of the generalised continuum hypothesis with a commonly used axiomatisation of set theory and by Cohen to demonstrate its independence of those axioms could never under any circumstances be considered business logic.[/quote]Random and orthogonal.[quote][quote]Again, perhaps it would help to avoid the phrase "business logic" and instead think in terms of logic that relates to data correctness and logic that revolves around a business process. They both relate to a solution used for a business but only one generally belongs in the database.[/quote]The problem that the question whether data is correct is a business problem. It can only be ascertained or tested by examining business rules and can only be ensured by enforcing business rules. [/quote]And we're back to the kitchen sink definition of "business logic".[quote]The business logic embodied in those particular business rules should be in the database, and I don't see any point at all in claiming that that logic is not business logic. Indeed if it were not business logic, why would it differ from one business to the next, as it clearly does? [/quote]Again, then let's not use the term. Exactly what types of logic should **not** be in the database?[quote]Here's a simple rule for determing what business logic must be implemented in the database:If a business rule can be enforced on the data using a combination of key constraints and/or domain constraints (in SQL as opposed to in relation algebra that amounts to primary key constraints, uniqueness constraints, foreign key constraints, not null constraints, qualifications of a column's datatype as to precision, scale, size, or collation, and check constraints) with reasonable modification (normalisation) of schemata to achieve this, then that should be done; the logic of the business rules enforced in this manner should therfor be implemented in the database. [/quote] In effect, what you are saying is that if you can express the logic using declarative features, then it should be in the database but this definition may be too narrow. What about logic implemented through triggers? What about database products that implement the ASSERTION keyword where you can in effect create check constraints that span tables? What about database products that do not implement check constraints but may implement triggers?You seem to be stuck in the "how" the logic is implemented instead of the "where" it belongs or "why" it does or does not belong there. The database's purpose is to protect the correctness of the data itself but not necessarily a process that can easily change. Given that tables are sets of facts, it makes sense that the database is designed to ensure the correctness of those facts but not care about how those facts were gathered or what conventions were created to gather those facts.[quote]It should also be implemented in the application logic, including the front end, since it is preferable to have things work without the application having to cope with constraint violations reported by the database, and for the user interface to avoid as far as reasonably possible presenting the end user with options that would violate the business rules (and the extremely rare cases when avoidance isn't reasonably possible giving the user instant feedback if there's a problem, rather than waiting for the database to signal a problem).[/quote]Agreed. However, by your use of the phrase "application logic", you have interestingly differentiated it from "business logic". Is it not the case that logic in the application is also "business logic" by your definition?[quote]Application logic other than the above in the database? Well, I don't like exposing the database to ad-hoc queries, nor indeed exposing the schemata to the applications (I want maximal decoupling for modularity). So I prefer the applications to be forced to call stored procedures and have no permissions to read or write any table or view other than by calling the provided stored procedures.[/quote]Agreed but hard to enforce. Typically what blows up this approach is ad-hoc reports.<snip>[quote]But perhaps the fundamental problem with the "no business logic in the database" mantra is that it's used by many DBAs, DB developers, and DB designers as an excuse for making no attempt to understand the business, resulting in catastophically poor outcomes of development projects because without understanding the business they can't understand what data states are forbidden by the business logic or what data volumes are implied by technicalities of teh business and as a result can't design schemata that will provide data integrity and also can't estimate within several orders of magnitude what sort of storage is needed.[/quote]In the case of this thread at least, it comes down to vocabulary. To me, the logic used to ensure data correctness is different than the logic used to manage a business process is different than the logic used by one specific application that implements that logic and to lump multiple items into the bucket of "business logic" is equivalent to using the term "smurf" for any type of logic. Databases last much longer than the applications used to initially write to them so their structure is critical to ensure lasting success. Each tier should be considered a fiefdom where no tier trusts data from the tier above it which means each tier re-validates the data given to it. When a date value is passed from the presentation tier, the middle-tier (sometimes called the business layer or business tier) re-validates that it is a date and the database again re-validates it. Each tier above the database acts like an emissary ensuring that the data that is sent to the next tier down will not be rejected but ultimately, they do not trust each other.One guideline might be: if a rogue DBA can, without altering DRI or triggers but bypassing stored procedures, execute a query that alter data in such as way as to break other tiers of my application or create obviously wrong or inaccurate data, then the logic to protect that data and prevent that scenario belongs in the database at the most declarative level possible. The database is there to protect the data. Most people do not call that "business logic" because the phrase quickly becomes meaningless. Most developers I have encountered understand the phrase "business logic" to mean logic not related to database data correctness nor related to the presentation tier.Thu, 15 Dec 2011 23:49:55 GMTThomas-282729http://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspx[quote][b]Thomas-282729 (12/15/2011)[/b][hr]Perhaps it would help if you defined what *isn't* "business logic" that belongs within the confines of a solution for a business. As I said earlier, you are using "business logic" to be equivalent to the word "smurf". It can mean anything and everything that is even remotely related to any decision whatsoever in the business and that type of broad, vague definition does not help to understand what sorts of logic belong in the database and more importantly, what forms of logic do not belong in the database. [/quote]Yes, it is necessary to understand what business logic belongs in the database and what business logic doesn't. Understanding that doesn't however involve declaring that some things which clearly are business logic (for example the decision to so organise the sales team that each salesman can deal in only one product line, or equally the decision not to make the organisation like that, or the decision to leave the question open) are not business logic just because they have implications for constraints which belong in the database - changing the meaning of "business logic" just in order to allow the nonsense statement that the database must contain no business logic to appear to be true.[quote]If any and all logic is "business logic" then any form of logic makes sense to put in the database and that clearly is not the case.[/quote]Who has suggested that any and all logic is business logic other than yourself? And whence the enormous nonsequitur that if any and all logic were business than it would be appropriate to put all logic in the database - so far as I know, no-one has made the claim that all business logic should be in the database. Are you perhaps playing at putting up ludicrous strawmen to enable yourself to appear to logical in opposing a position which is nothing like those strawmen? I could take your new suggestion as meaning that you want to assert that since all logic is business logic the database should contain no logic of any description, but I don't actually believe that you want to assert that. If you are claiming that I have asserted that you are claiming something which clearly is not true (as should be clear from reading any of my posts). To take an extreme case I am prepared to assert, for example, the logic used by Godel to demonstrating the consistency of the generalised continuum hypothesis with a commonly used axiomatisation of set theory and by Cohen to demonstrate its independence of those axioms could never under any circumstances be considered business logic. [quote]Again, perhaps it would help to avoid the phrase "business logic" and instead think in terms of logic that relates to data correctness and logic that revolves around a business process. They both relate to a solution used for a business but only one generally belongs in the database.[/quote]The problem that the question whether data is correct is a business problem. It can only be ascertained or tested by examining business rules and can only be ensured by enforcing business rules. The business logic embodied in those particular business rules should be in the database, and I don't see any point at all in claiming that that logic is not business logic. Indeed if it were not business logic, why would it differ from one business to the next, as it clearly does? Here's a simple rule for determing what business logic must be implemented in the database:If a business rule can be enforced on the data using a combination of key constraints and/or domain constraints (in SQL as opposed to in relation algebra that amounts to primary key constraints, uniqueness constraints, foreign key constraints, not null constraints, qualifications of a column's datatype as to precision, scale, size, or collation, and check constraints) with reasonable modification (normalisation) of schemata to achieve this, then that should be done; the logic of the business rules enforced in this manner should therfor be implemented in the database. It should also be implemented in the application logic, including the front end, since it is preferable to have things work without the application having to cope with constraint violations reported by the database, and for the user interface to avoid as far as reasonably possible presenting the end user with options that would violate the business rules (and the extremely rare cases when avoidance isn't reasonably possible giving the user instant feedback if there's a problem, rather than waiting for the database to signal a problem).Application logic other than the above in the database? Well, I don't like exposing the database to ad-hoc queries, nor indeed exposing the schemata to the applications (I want maximal decoupling for modularity). So I prefer the applications to be forced to call stored procedures and have no permissions to read or write any table or view other than by calling the provided stored procedures. Definitely that should be so organised that it puts as little extra application logic (application logic over and above that which enforces data integrity) into the database (in the form of stored procedures) as can reasonably be achieved, but it almost always will put extra application logic into the database - you don't want to do the joins at a layer above the database, and I guess you would claim that application logic embedded in SQL code produced by the application as opposed to in SQL code contained within the database is not in the database. But I won't claim it's essential to go that route: although it's certainly not possible to build an equally secure system any other way not all applications require that much security, and using parametrised SQL queries in an above database data-access layer is fully secure against injection attacks, which is enough for most people; and such a data-access layer only breaches modularity by exposing the schema if it is controlled as part of the app as opposed to as if it were part of the database.But perhaps the fundamental problem with the "no business logic in the database" mantra is that it's used by many DBAs, DB developers, and DB designers as an excuse for making no attempt to understand the business, resulting in catastophically poor outcomes of development projects because without understanding the business they can't understand what data states are forbidden by the business logic or what data volumes are implied by technicalities of teh business and as a result can't design schemata that will provide data integrity and also can't estimate within several orders of magnitude what sort of storage is needed.Thu, 15 Dec 2011 14:11:23 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspx[quote][b]L' Eomot Inversé (12/14/2011)[/b][hr][quote][b]Thomas-282729 (12/13/2011)[/b][hr]I do not consider check constraints to be "business logic" even if it is a rule dictated by the business. [/quote]Amazing. Rules dictated by the business are not business logic. That is a pretty heavy abuse of the language, I think.We agree clearly that there is some business logic which doesn't belong in the database; but I can't get my head around the idea that things that enforce business rules aren't business logic. To me it just doesn't make sense to claim that enforcement of a rule derived from the business, possibly from an overiding policy decision at the highest level in the enterprise, is not business logic.[/quote]Perhaps it would help if you defined what *isn't* "business logic" that belongs within the confines of a solution for a business. As I said earlier, you are using "business logic" to be equivalent to the word "smurf". It can mean anything and everything that is even remotely related to any decision whatsoever in the business and that type of broad, vague definition does not help to understand what sorts of logic belong in the database and more importantly, what forms of logic do not belong in the database. If any and all logic is "business logic" then any form of logic makes sense to put in the database and that clearly is not the case. Again, perhaps it would help to avoid the phrase "business logic" and instead think in terms of logic that relates to data correctness and logic that revolves around a business process. They both relate to a solution used for a business but only one generally belongs in the database.Thu, 15 Dec 2011 10:27:36 GMTThomas-282729http://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspx[quote][b]Thomas-282729 (12/13/2011)[/b][hr]I do not consider check constraints to be "business logic" even if it is a rule dictated by the business. [/quote]Amazing. Rules dictated by the business are not business logic. That is a pretty heavy abuse of the language, I think.We agree clearly that there is some business logic which doesn't belong in the database; but I can't get my head around the idea that things that enforce business rules aren't business logic. To me it just doesn't make sense to claim that enforcement of a rule derived from the business, possibly from an overiding policy decision at the highest level in the enterprise, is not business logic.Wed, 14 Dec 2011 08:20:26 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspxLet's step back for a second. The point I have made multiple times is that if you have to resort to T-SQL cursors, that is logic that almost assuredly does not belong in the database. The declarative features in SQL Server are such that there shouldn't be situations (i.e., they should be extraordinarily rare)where you must resort to cursors and have that logic in the database. The only reason you consider the term "business logic" vague is that you are using it mean everything and the kitchen sink. As far as I can tell, by your reckoning, any constraint of any sort is business logic. I do not consider check constraints to be "business logic" even if it is a rule dictated by the business. Perhaps another term is needed to differentiate rules used to ensure the correctness of the data as opposed to rules related to facilitating of a business process. I'm calling the former data integrity and the later "business logic". There is no absolute demarcation between the two, however, a good point of reference is that if you must resort to using iterative solutions within a set based system (the database), the odds are good it does not belong there.Thus, if one differentiates forms of business logic, between logic for data correctness and logic for business processes, the yes, the later does not belong in the database IMO. My statements throughout are consistent unless one extrapolates the term "business logic" to mean any decision point anywhere in the system no matter how small in which case the term itself is meaningless and I doubt that is the case with the other posters.Tue, 13 Dec 2011 22:56:16 GMTThomas-282729http://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspxComing back to this after much delay, because I came across it as a reult of someone's reference to RBarry's article and I think it's an important issue.[quote][b]Thomas-282729 (7/24/2010)[/b][hr]Much like Alex's article, I think you are confusing data integrity with business logic which are both needed for any database driven solution. Check constraints and uniqueness constraints relate to the integrity of the data. [/quote]No, they are clearly business logic. If I have a business rule that says "any salesman can be selling exactly one product class" that generates a uniqueness constraint on the two columns SalesPersonID and ProcuctClassId in whatever table tells me which is the product class sold by each salesman. If my business rules allows each salesman to sell products belonging to several product classes, such a constraint can not be imposed - the business rule that the constraint represents does not exist, so the constraint must not exist. You are proposing an utterly false distinction here - any question of "data integrity" boils down to a question of what states of the data are permitted by the business rules; we impose constraints, and normalise, in order to ensure that states not permitted by the business rules are unreachable, and that is the sole meaning of the phrase "data integrity": states which shoule be unreachable states (whose unreachability is determined by the business rules) must not be reachable. So data integrity rules are a particular subset of the business rules, not something separate from business rules, and declaring that the database must not implement business rules amounts to declaring that you are not permitted, in the database, to ensure data integrity - which is, in my view, absolute claptrap.[quote]Rules such as "in march, sales from reps with a certain sale volume threshold and for products of certain types should have an interest rate discount based the users credit score accounting for a series of values some of which come from services" are difficult to implement and properly test in a database.[/quote]So there are some business rules that should probably not be in the database; how did you achieve the amazing leap from there to "there are no business rules that should be in the database"?[quote]I never said that absolutely no business logic should be in the database. Instead, I suggested that if you are required to implement an iterative solution in the database using cursors, it does not belong in the database.[ The database is not the end-all-be-all repository for business logic. There are some forms of logic that simply do not belong in the database. Sometimes the answer for how to do something in T-SQL is not to do it T-SQL.[/quote]That seems to be a long step away from what you said earlier. It's something I agree with - I'm not interested for example in handling something in T-SQL that would be more appropiately handled in Fortran. But I still regard any statement that the database is not the place for business logic as nonsense, since there is oftrn a large amount of business logic (even if you want to call it something else, like "data integrity logic", and pretend it isn't business logic despite the very concept of data integrity being meaningless unless it is defined as banning states forbidden by business logic) that absolutely must be implemented in the database if we are to have clean bug-free systems.Tue, 13 Dec 2011 20:35:23 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspxHeh... Tom is definitely one of the good guys.Part of his last statement in his mantra is actually part of the first statement in mine...[i]"you might want to seriously think about why it is you need to do it!"[/i]:-PSat, 14 Aug 2010 11:18:46 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspxSQL Developers definitely think differently! In my mind's eye, it's more of a 3D approach, while RBAR is pretty "flat".BTW, the need for set-based thought processes isn't peculiar to SQL Server. [url=http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:760210800346068768#tom2263448900346081743]Tom Kyte has a "mantra"[/url] that, though tailored for Oracle, can be modified to apply SQL Server development. His mantra:[b]o You should do it in a single SQL statement if at all possible.[/b] o If you cannot do it in a single SQL Statement, then do it in PL/SQL. o If you cannot do it in PL/SQL, try a Java Stored Procedure. o If you cannot do it in Java, do it in a C external procedure. o If you cannot do it in a C external routine, you might want to seriously think about why it is you need to do it!Wed, 11 Aug 2010 10:45:00 GMTganotedphttp://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspx[quote][b]Mike C (7/23/2010)[/b][hr]I'm in total agreement with you 99.9% of the time on just about every topic Jeff, and you know that well. The fact that people spend a "lot of time" tweaking indexes to improve performance is not the same as spending time tweaking indexes to "change the content of your end result"; at least not in my mind. The two concepts to me seem disconnected.I'm not particularly interested in convincing you that indexes (or lack of them) should not change the content of your results. Or number of processors, etc. If you believe the content of your output should be determined by your indexes, index hints, etc. then go for it.At any rate, I respect your opinion and since you feel I'm "bad mouthing folks", I'm outta here and done with this thread.Out here.Mike C[/quote]Man... didn't mean for it to come to that especially since you're absolutely correct... we do agree on 99.9% of everything. Sorry, Mike... my fault for being a bit over-zealous here because of the really terrible bad-mouthing I've taken in the past on the subject. Didn't mean to snap at one of the good guys.Sun, 25 Jul 2010 12:58:31 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspx[quote][b]Tom.Thomson (7/24/2010)[/b][hr][quote][b]Thomas-282729 (7/24/2010)[/b][hr][quote]With due respect to Alex, it is not as elastic as you think. A good barometer for whether a given piece of logic belongs in the database is whether it can be written without cursors and without special CLR routines. If it cannot, the odds are high that it does not belong in the database. A database, specifically a OLTP database, is best at storing data, enforcing data integrity and serving data. It is not designed for complicated calculations. It is not made to be a reporting tool (as opposed to simply serving data to a reporting tool). Just as Excel can be misused as a database, a database can be misused as a repository for complicated calculations. It is a testament to T-SQL that the language is sufficiently powerful to be misused for these purposes.[/quote]The relational calculus with propositional calculus, which is what a language supporting the relational model is supposed to be is Turing Complete; so it is sufficiently powerful to be used for anything that is computable. SQL in general (and T-SQL in particular) although it only pays lip service to the idea of supporting relational algebra with predicate calculus, is indeed Turing complete. That isn't at all remarkable - there have been very very few programming languages which weren't and it isn't any sort of testament to T-SQL. The term "business logic" is sufficiently vague in meaning that it can include many things. Any reasonable use of the term includes things which clearly are properties of the business data model - for example check constraints and uniqueness constraints. Forbidding the database to implement business logic would forbid database designers from normalising their schemata: after all progressing from 2NF to 3NF is the process of ensuring that all simple functional dependencies are enforced by the schema and its keys and constraints, and functional dependencies are clearly about business logic. So I have to disagree strongly with the idea that there should be no business logic in the database.[/quote]Much like Alex's article, I think you are confusing data integrity with business logic which are both needed for any database driven solution. Check constraints and uniqueness constraints relate to the integrity of the data. Rules such as "in march, sales from reps with a certain sale volume threshold and for products of certain types should have an interest rate discount based the users credit score accounting for a series of values some of which come from services" are difficult to implement and properly test in a database. Yes, the term "business logic" is vague. DRI in and of itself is arguably a form of business logic. Each set of tools for supporting business logic have their strengths and weaknesses. Databases are great at set-based logic. They are poor at complex calculations or calculations which require iterative solutions. For example, it is very difficult to create multi-threaded solutions in a database. They are simply not designed for that. Further, databases are substantially more expensive to scale out than middle-tier libraries or services.I never said that absolutely no business logic should be in the database. Instead, I suggested that if you are required to implement an iterative solution in the database using cursors, it does not belong in the database. The database is not the end-all-be-all repository for business logic. There are some forms of logic that simply do not belong in the database. Sometimes the answer for how to do something in T-SQL is not to do it T-SQL.Sat, 24 Jul 2010 17:20:04 GMTThomas-282729http://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspx[quote][b]Thomas-282729 (7/24/2010)[/b][hr][quote]With due respect to Alex, it is not as elastic as you think. A good barometer for whether a given piece of logic belongs in the database is whether it can be written without cursors and without special CLR routines. If it cannot, the odds are high that it does not belong in the database. A database, specifically a OLTP database, is best at storing data, enforcing data integrity and serving data. It is not designed for complicated calculations. It is not made to be a reporting tool (as opposed to simply serving data to a reporting tool). Just as Excel can be misused as a database, a database can be misused as a repository for complicated calculations. It is a testament to T-SQL that the language is sufficiently powerful to be misused for these purposes.[/quote]The relational calculus with propositional calculus and transactional control, which is what a language supporting the relational model is supposed to be, is Turing Complete as soon as you add the "while <boolean expression> <compound statement>" construct; so it is sufficiently powerful to be used for anything that is computable. With its while loops SQL in general (and T-SQL in particular) although it only pays lip service to the idea of supporting relational algebra with predicate calculus, is indeed Turing complete. That isn't at all remarkable - there have been very very few programming languages which weren't and it isn't any sort of testament to T-SQL. The term "business logic" is sufficiently vague in meaning that it can include many things. Any reasonable use of the term includes things which clearly are properties of the business data model - for example check constraints and uniqueness constraints. Forbidding the database to implement business logic would forbid database designers from normalising their schemata: after all progressing from 2NF to 3NF is the process of ensuring that all simple functional dependencies are enforced by the schema and its keys and constraints, and functional dependencies are clearly about business logic. So I have to disagree strongly with the idea that there should be no business logic in the database.edit: first version didn't mention while loop - I was being a bit sloppy.Sat, 24 Jul 2010 14:59:06 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspx[quote][b]ganotedp (7/20/2010)[/b][hr]"Business Logic"?!? That's a highly, um, "elastic" concept. I'm with Alex Papadimoulis on the topic:http://thedailywtf.com/Articles/The-Mythical-Business-Layer.aspx[/quote]With due respect to Alex, it is not as elastic as you think. A good barometer for whether a given piece of logic belongs in the database is whether it can be written without cursors and without special CLR routines. If it cannot, the odds are high that it does not belong in the database. A database, specifically a OLTP database, is best at storing data, enforcing data integrity and serving data. It is not designed for complicated calculations. It is not made to be a reporting tool (as opposed to simply serving data to a reporting tool). Just as Excel can be misused as a database, a database can be misused as a repository for complicated calculations. It is a testament to T-SQL that the language is sufficiently powerful to be misused for these purposes.Sat, 24 Jul 2010 13:52:10 GMTThomas-282729http://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspx[quote][b]Mike C (7/22/2010)[/b][hr]Let's walk through this briefly and see what steps would be required to implement the original query to perform it in logical order steps:select left( 'abc', T1.v )from T1where T1.v >= 0;Now let's say we have 10 million rows in T1 and 5 million of them match the where clause (T1.v >= 0). Here's what we need to do:1. Scan the 10 million row table (look at every single row) and compare to 0.2. Dump the 5 million rows that match the WHERE clause into a temp table or other intermediate storage.3. Scan the 5 million rows that match the WHERE clause and perform the projection against them (left( 'abc', T1.v )).We just scanned 15 million rows and stored 5 million rows in temporary storage to fulfill this query. Now let's look at one way we can optimize this:1. Scan the 10 million row table.2. Execute the WHERE clause and the projection at the same time on each row since both clauses are using the same columns.[/quote]I notice that you refer to a projection followed by a transformation (or mapping - people use both terms) as a projection; that's potentially dangerous thinking when discussing optimisation, since transformations (mappings) can be very expensive, while projections are fairly cheap.In your "optimized" version we have 10 million invocations of left instead of 5 million. Seems to me to be too many! Surely the optimizer can handle transformations at the same time as restrictions (or would "select sum(T1.v*5) from T1 where T1.v>=0" create a table in interim storage to store the restriction's result in, and then scan that table to compute the multiplication and do the sum - if so it's inferior to an optimizer I saw a fresh young graduate throw together in a few months back in the 80s)? If it can do that in a single scan without a temporary table for intermediate results why should it not do the example using left that way too?What I expect the system to do is:scan the 10 million row table, executing the projection and the restriction [the where clause] on each row (you can't execute the where clause without doing the projection, since it examines c, the only column in the projection), and executing the transformation and passing the transformed row to the output if (only if) the row is included by the where clause.No extra scans, no temporary tables, and no unneccessary transformations. And no violation of the declarative semantics of relational algebra.Anyway, I expect the semantics to be such that the result of the select is not modified by rows which do not "qualify for the select" ("qualify for the select" is Microsoft's terminology, not mine). Maybe that's because I expect to see at least lip service paid to relational algebra. I can't make a similar argument for the "quirky update", and I would prefer to see SQL add some concepts from functional algebra with good clear semantics to the select and update statements, and then T-SQL could adopt those to replace the current quirky update . But I don't see it happening.My worry about the passage I quoted from the BoL documentation is twofold: (a) it's wrong (outrageously wrong); (b) it may indicate a tendency in Microsdoft to believe that clever tricks in the optimiser justify overriding the semantics of SQL, and if so that's very dangerous.Fri, 23 Jul 2010 13:24:24 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspx[quote][b]UMG Developer (7/23/2010)[/b][hr]I don't understand, that is a basic SELECT statement with no quirky update or aggregation, so since you have one record in the table you will get one record returned. (Well if the syntax errors are fixed first.) If you have 2 records in the table you would get two records back with a single column that contained a value of 3. (Unless one of the records contains a NULL in either column in which case the corresponding row will return NULL for the value of the C column.)[/quote]That of copurse was precisely my point - we know what theoutput from that will be so that passage in BoL that says that I can't know that the result has a single column containing "3" is nonsense, so whoever wrote that passager clearly wasn't thinking staright about what is guaranteed despite the optimzer's reordering of operations. The worry is that this sloppy thinking may at some point impact development in MS, resulting in some nasty bugs. Maybe I should have used an even simpler example, like this: if that BoL passage were correct we could not be sure that the result set from "select 1 as c" has the value 1 in the single field c of its single row.Fri, 23 Jul 2010 12:48:12 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspx[quote][b]Tom.Thomson (7/22/2010)[/b][hr][quote][b]Mike C (7/22/2010)[/b][hr][code]set transaction isolation level serializablebegin trancreate table #T (a int check (a=1) default (1), b int check (b=2) default(2))insert #T (TABLOCKX) values (1,2)select T.a+T.b as c from #T Tcommit tran[/code]I will get a result set of some rows with a single column, probably named c. It's not really clear that in the row(s) I get in that result set the single column c will have type int. It's far from clear that I'll get just one row. in fact the BoL page says that the select statement doesn't specify that. It is explicitly stated in that BoL page that this select statement is NOT guaranteed to return me the value 3 in column of the single (if I do get just one) row, since there the select statement specifies no relation between the values in the source tables and the values in the result set, except perhaps as affected by where and having clauses, if that is conceivable (unlikely, except maybe for aggregates): certainly not by the select clause or its output list, and it's the output list that matters here. [/quote]I don't understand, that is a basic SELECT statement with no quirky update or aggregation, so since you have one record in the table you will get one record returned. (Well if the syntax errors are fixed first.) If you have 2 records in the table you would get two records back with a single column that contained a value of 3. (Unless one of the records contains a NULL in either column in which case the corresponding row will return NULL for the value of the C column.)Fri, 23 Jul 2010 09:45:09 GMTUMG Developerhttp://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspx[quote][b]Jeff Moden (7/23/2010)[/b][hr][quote][b]Mike C (7/22/2010)[/b][hr][quote][b]Jeff Moden (7/22/2010)[/b][hr][quote][b]Mike C (7/22/2010)[/b][hr][quote][b]Jeff Moden (7/22/2010)[/b][hr][quote][b]Mike C (7/22/2010)[/b][hr]Obviously the so-called "buggy" execution plan requires the developer to be more careful, but gracefully handling invalid function parameter and divide-by-zero errors are not new problems in programming and shouldn't come as a surprise to most devs. [/quote]Hmmm... sounds just like the rules for a quirky update. ;-)[/quote]What's that? If you divide two numbers make sure the dividend is not zero? :-). Most devs learned that rule in grade school :)[/quote]Nah... I was talking about the developer being careful. ;-)[/quote]Ahhh, now I understand ;-) I suppose, like the quirky update, with the right combination of query hints, options, hardware, and indexes you could probably find a way to force the query optimizer to execute the WHERE clause first. At least until the optimizer comes up with a new plan. Knowing that the physical order of execution within a query is not guaranteed would make me hesitant to trust that solution though. [/quote]No... you don't understand, Mike. People jump through all sorts of hoops with indexes and whether or not to use include and whether or not to use covering indexes. A great amount of time is spent explaining clustered indexes and the fact that non clustered indexes are appended with the clustered index and how to defrag and rebuild indexes. Why? To trick the optimizer into doing things faster and that's all I've done with the quirky update. You say it's not quaranteed but even a SELECT with a WHERE clause isn't guaranteed. SQL SERVER 2000 had to have a hot fix after sp2 because if parallelism occurred during an UPDATE that had a WHERE clause with an IS NULL in it, the IS NULL was ignored and all rows were updated. I know this because it happened to the folks at work. NOTHING is guaranteed to work so use everything that you can. Or not... use a cursor if it makes you nervous. Just stop bad mouthing folks that have found things that work that you can't break if you follow some simple rules... guaranteed or not and whether they go against your grain or not.[/quote]I'm in total agreement with you 99.9% of the time on just about every topic Jeff, and you know that well. The fact that people spend a "lot of time" tweaking indexes to improve performance is not the same as spending time tweaking indexes to "change the content of your end result"; at least not in my mind. The two concepts to me seem disconnected.I'm not particularly interested in convincing you that indexes (or lack of them) should not change the content of your results. Or number of processors, etc. If you believe the content of your output should be determined by your indexes, index hints, etc. then go for it.At any rate, I respect your opinion and since you feel I'm "bad mouthing folks", I'm outta here and done with this thread.Out here.Mike CFri, 23 Jul 2010 06:44:10 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspx[quote][b]Mike C (7/22/2010)[/b][hr][quote][b]Jeff Moden (7/22/2010)[/b][hr][quote][b]Mike C (7/22/2010)[/b][hr][quote][b]Jeff Moden (7/22/2010)[/b][hr][quote][b]Mike C (7/22/2010)[/b][hr]Obviously the so-called "buggy" execution plan requires the developer to be more careful, but gracefully handling invalid function parameter and divide-by-zero errors are not new problems in programming and shouldn't come as a surprise to most devs. [/quote]Hmmm... sounds just like the rules for a quirky update. ;-)[/quote]What's that? If you divide two numbers make sure the dividend is not zero? :-). Most devs learned that rule in grade school :)[/quote]Nah... I was talking about the developer being careful. ;-)[/quote]Ahhh, now I understand ;-) I suppose, like the quirky update, with the right combination of query hints, options, hardware, and indexes you could probably find a way to force the query optimizer to execute the WHERE clause first. At least until the optimizer comes up with a new plan. Knowing that the physical order of execution within a query is not guaranteed would make me hesitant to trust that solution though. [/quote]No... you don't understand, Mike. People jump through all sorts of hoops with indexes and whether or not to use include and whether or not to use covering indexes. A great amount of time is spent explaining clustered indexes and the fact that non clustered indexes are appended with the clustered index and how to defrag and rebuild indexes. Why? To trick the optimizer into doing things faster and that's all I've done with the quirky update. You say it's not quaranteed but even a SELECT with a WHERE clause isn't guaranteed. SQL SERVER 2000 had to have a hot fix after sp2 because if parallelism occurred during an UPDATE that had a WHERE clause with an IS NULL in it, the IS NULL was ignored and all rows were updated. I know this because it happened to the folks at work. NOTHING is guaranteed to work so use everything that you can. Or not... use a cursor if it makes you nervous. Just stop bad mouthing folks that have found things that work that you can't break if you follow some simple rules... guaranteed or not and whether they go against your grain or not.Fri, 23 Jul 2010 06:15:01 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspxI very much like SQL even while over the time I encounter or read about quite a few quirks. The problem I have with the case I brought forward is that it complicates code even for basic simple functionality which is the opposite of what I like so much about SQL. The real life case was a bit more complex, but my example demonstrated the principle quite nicely.In its heart SQL is of course a data retrieval language and it does that excelent IMHO, often far beyond my expectations. But we frequently go out of this boundary and include all sorts of processing beyond simple test and include processing in select clauses as well. It is here that quirks show up that can spoil the party if you rightfully desire always perfectly working code. It can get complex and ugly (unreadable) real fast if you got many conditions to be met.The SQL principle of describing what you want and let the server figure out how to get those results to you is perfectly fine. But it could be so much better if those exceptions where handled more graceful and never spoiled the end result at all. With this I mean that an exception in a row that does not contribute to the end result must be kept hidden IMHO. This would boost the usefulness and robustness of the language many times over without complicating work for the query writer.Anyway, its an interesting sub-discussion and a bit off-topic here ;). I really have to make a simple as possible, working example case to demonstrate the issue. The code in question had run for over a year without any issue and suddenly there it was, broken for no obvious reason. Lucky for me, it was generic functionality and I am used to encapsulate this sort of logic in functions. I was able to fix this in one spot instead of re-writing and complicating many separate queries.Fri, 23 Jul 2010 03:36:51 GMTpeter-757102http://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspx[quote][b]Tom.Thomson (7/22/2010)[/b][hr][quote][b]Mike C (7/22/2010)[/b][hr]Sorry to hear you're leaving T-SQL :crying: Unfortunately you probably won't find much love in any dialect of SQL. The problem (?) is that the SQL standard doesn't define physical processing steps. All that is left to the vendors. The standard simply says here's the keywords, here's the definition of the keywords, here's what you put in and here's what you get out. [/quote]I think you are missing the point. The (silly) arithmetic example isn't what it's about, what it's about is the elimination of semantics from the T-SQL language. The BoL entry I quoted says quite clearly and plainly that the select statement + the state of the database when it is executed do NOT between them define what you get out. For example if I execute the following code[code]set transaction isolation level serializablebegin trancreate table #T (a int check (a=1) default (1), b int check (b=2) default(2))insert #T (TABLOCKX) values (1,2)select T.a+T.b as c from #T Tcommit tran[/code]I will get a result set of some rows with a single column, probably named c. It's not really clear that in the row(s) I get in that result set the single column c will have type int. It's far from clear that I'll get just one row. in fact the BoL page says that the select statement doesn't specify that. It is explicitly stated in that BoL page that this select statement is NOT guaranteed to return me the value 3 in column of the single (if I do get just one) row, since there the select statement specifies no relation between the values in the source tables and the values in the result set, except perhaps as affected by where and having clauses, if that is conceivable (unlikely, except maybe for aggregates): certainly not by the select clause or its output list, and it's the output list that matters here. When this kind of idiocy appears in BoL we all should be terrified: that may be the MS philopsophy behind which parts of the select statement's apparent semantics (enshrined in the ANSI/ISO SQL standards, I believe, but apparently not in T-SQL) which the optimser (for SQL, if not for T-SQL) is required not to destroy. Or it may be just that someone who hasn't a clue has been writing chunks of BoL - and from your comment on this issue (and on that raised by another which prompted me to comment) I think you've failed to understand all of (1) just what that quotation says and (2) what most of us believe the semantics of SQL is (per ANSI and per ISO, despite what MS sometimes asserts to the contrary on silly BoL pages like the one I quoted).I don't much care about the "quirky update". If one is adequately careful it works, provided one is careful to include adequate sanity checks in the code to ensure that it still works even if MS does something completely insane with the optimiser (as, according to Pete-<numbers> - sorry , I forgot which numbers - they have, although this particular insanity doesn't affect quirky update any more than it affects ordinary selects). I can't see any problem in using it (I'm a pessimist: SQL is such a defective language and no-one is going to fix it so MS will leave this rather silly extension in, with restrictions of course, instead of implementing a proper reduction operator; neither MS nor the standards bodies care about extended relational algebra operators, particularly not about the functional ones; but they will never be able to afford to destroy quirky update until they introduce some of those operators, so we can carry on using it with care). Of course "adequately careful" is fairly extreme - just as it is for fully specified, fully supported, and not even deprecated MS stuff (I've been bitten, sorry to sound sour about MS consistency).[/quote]I agree, I am completely missing your point. It seems like you have more than one point to make in there, but I'm not getting any of them. Let me see if I can try to understand your points:1. You're upset about code formatting by developers that makes the result of SQL unclear. Fortunately that's easily fixable, though it requires a commitment from the developers who write code. BTW, I believe the output from your code sample above will actually be an error message.[code]SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;BEGIN TRANSACTION;CREATE TABLE #T ( a INTEGER DEFAULT (1), b INTEGER DEFAULT (2), CONSTRAINT Allow_Nothing_But_1_In_a CHECK (a = 1), CONSTRAINT Allow_Nothing_But_2_In_b CHECK (b = 2));INSERT INTO #T (a, b) WITH (TABLOCKX) VALUES (1,2);SELECT (T.a + T.b) AS c -- This query returns 1 column; an INTEGER + an INTEGER yields an INTEGERFROM #T T;COMMIT TRANSACTION;[/code]2. You're upset about mistakes and problematic documentation. I often find mistakes in technical documentation -- spent nearly a week picking apart the SQLCMD utility documentation in BOL when 2005 was first released, for instance. Fortunately there are a couple of ways to constructively deal with that issue. [url]http://connect.microsoft.com[/url] and the Feedback button on the BOL and MSDN pages come to mind. There's also the web forums, which MS' documentation team often frequents soliciting feedback.3. You don't like SQL in general. That's perfectly understandable -- there are a lot of people who don't like SQL, and that's a personal choice. Of course every query language, programming language, scripting language, markup language, or any other form of computer-based language has idiosyncracies and issues. Whether it's data type casting (implicit, explicit), lack of features, etc., there will always be room for improvement. MS accepts feature requests on their Connect website also.Thu, 22 Jul 2010 21:16:55 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspx[quote][b]Mike C (7/22/2010)[/b][hr]Sorry to hear you're leaving T-SQL :crying: Unfortunately you probably won't find much love in any dialect of SQL. The problem (?) is that the SQL standard doesn't define physical processing steps. All that is left to the vendors. The standard simply says here's the keywords, here's the definition of the keywords, here's what you put in and here's what you get out. [/quote]I think you are missing the point. The (silly) arithmetic example isn't what it's about, what it's about is the elimination of semantics from the T-SQL language. The BoL entry I quoted says quite clearly and plainly that the select statement + the state of the database when it is executed do NOT between them define what you get out. For example if I execute the following code[code]set transaction isolation level serializablebegin trancreate table #T (a int check (a=1) default (1), b int check (b=2) default(2))insert #T (TABLOCKX) values (1,2)select T.a+T.b as c from #T Tcommit tran[/code]I will get a result set of some rows with a single column, probably named c. It's not really clear that in the row(s) I get in that result set the single column c will have type int. It's far from clear that I'll get just one row. in fact the BoL page says that the select statement doesn't specify that. It is explicitly stated in that BoL page that this select statement is NOT guaranteed to return me the value 3 in column of the single (if I do get just one) row, since there the select statement specifies no relation between the values in the source tables and the values in the result set, except perhaps as affected by where and having clauses, if that is conceivable (unlikely, except maybe for aggregates): certainly not by the select clause or its output list, and it's the output list that matters here. When this kind of idiocy appears in BoL we all should be terrified: that may be the MS philopsophy behind which parts of the select statement's apparent semantics (enshrined in the ANSI/ISO SQL standards, I believe, but apparently not in T-SQL) which the optimser (for SQL, if not for T-SQL) is required not to destroy. Or it may be just that someone who hasn't a clue has been writing chunks of BoL - and from your comment on this issue (and on that raised by another which prompted me to comment) I think you've failed to understand all of (1) just what that quotation says and (2) what most of us believe the semantics of SQL is (per ANSI and per ISO, despite what MS sometimes asserts to the contrary on silly BoL pages like the one I quoted).I don't much care about the "quirky update". If one is adequately careful it works, provided one is careful to include adequate sanity checks in the code to ensure that it still works even if MS does something completely insane with the optimiser (as, according to Pete-<numbers> - sorry , I forgot which numbers - they have, although this particular insanity doesn't affect quirky update any more than it affects ordinary selects). I can't see any problem in using it (I'm a pessimist: SQL is such a defective language and no-one is going to fix it so MS will leave this rather silly extension in, with restrictions of course, instead of implementing a proper reduction operator; neither MS nor the standards bodies care about extended relational algebra operators, particularly not about the functional ones; but they will never be able to afford to destroy quirky update until they introduce some of those operators, so we can carry on using it with care). Of course "adequately careful" is fairly extreme - just as it is for fully specified, fully supported, and not even deprecated MS stuff (I've been bitten, sorry to sound sour about MS consistency).Thu, 22 Jul 2010 19:16:31 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspx[quote][b]Jeff Moden (7/22/2010)[/b][hr][quote][b]Mike C (7/22/2010)[/b][hr][quote][b]Jeff Moden (7/22/2010)[/b][hr][quote][b]Mike C (7/22/2010)[/b][hr]Obviously the so-called "buggy" execution plan requires the developer to be more careful, but gracefully handling invalid function parameter and divide-by-zero errors are not new problems in programming and shouldn't come as a surprise to most devs. [/quote]Hmmm... sounds just like the rules for a quirky update. ;-)[/quote]What's that? If you divide two numbers make sure the dividend is not zero? :-). Most devs learned that rule in grade school :)[/quote]Nah... I was talking about the developer being careful. ;-)[/quote]Ahhh, now I understand ;-) I suppose, like the quirky update, with the right combination of query hints, options, hardware, and indexes you could probably find a way to force the query optimizer to execute the WHERE clause first. At least until the optimizer comes up with a new plan. Knowing that the physical order of execution within a query is not guaranteed would make me hesitant to trust that solution though. I think I'd rather go with the slightly simpler solution, like this:[code]select left( 'abc', NULLIF(T1.v, 0) )from T1where T1.v >= 0;[/code]Or this:[code]select T1.x / NULLIF(T1.y, 0) as ratio from T1 where T1.y <> 0[/code]But then again I tend to enjoy the simple things in life. :-DThu, 22 Jul 2010 16:58:21 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspx[quote][b]Mike C (7/22/2010)[/b][hr][quote][b]Jeff Moden (7/22/2010)[/b][hr][quote][b]Mike C (7/22/2010)[/b][hr]Obviously the so-called "buggy" execution plan requires the developer to be more careful, but gracefully handling invalid function parameter and divide-by-zero errors are not new problems in programming and shouldn't come as a surprise to most devs. [/quote]Hmmm... sounds just like the rules for a quirky update. ;-)[/quote]What's that? If you divide two numbers make sure the dividend is not zero? :-). Most devs learned that rule in grade school :)[/quote]Nah... I was talking about the developer being careful. ;-)Thu, 22 Jul 2010 16:38:17 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspx[quote][b]Jeff Moden (7/22/2010)[/b][hr][quote][b]Mike C (7/22/2010)[/b][hr]Obviously the so-called "buggy" execution plan requires the developer to be more careful, but gracefully handling invalid function parameter and divide-by-zero errors are not new problems in programming and shouldn't come as a surprise to most devs. [/quote]Hmmm... sounds just like the rules for a quirky update. ;-)[/quote]What's that? If you divide two numbers make sure the dividend is not zero? :-). Most devs learned that rule in grade school :)Thu, 22 Jul 2010 16:17:04 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspx[quote][b]peter-757102 (7/22/2010)[/b][hr][quote][b]Jeff Moden (7/22/2010)[/b][hr]Peter,I've not tried this particular example but I'm pretty sure that you don't actually need the index hint when you use an ORDER BY to get the correct order. The MAXDOP 1 is the important part.[/quote]Check the link Jeff, you see that it is required to make it function as expected as the data is retrieved from a non-clustered covering index instead of the clustered key. In itself not a problem, where it not that the concatenation is processed BEFORE the order by takes effect, resulting in just one tiny string instead of a concatenation of all strings.[/quote]Thanks, Peter. I'll check.Thu, 22 Jul 2010 15:57:49 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspx[quote][b]Mike C (7/22/2010)[/b][hr]Obviously the so-called "buggy" execution plan requires the developer to be more careful, but gracefully handling invalid function parameter and divide-by-zero errors are not new problems in programming and shouldn't come as a surprise to most devs. [/quote]Hmmm... sounds just like the rules for a quirky update. ;-)Thu, 22 Jul 2010 15:56:35 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspx[quote][b]Tom.Thomson (7/22/2010)[/b][hr][quote][b]peter-757102 (7/22/2010)[/b][hr]I find this sort of thing many more times disturbing/dangerous then using a quirky update deliberatly and with full knowledge and documentation. Why? Because I suspect hardly anyone will know they have to protect their expressions to such a silly extend and the world therefore must be full of non-working code. In fact I expect that ** nearly all ** string processing code in use today to be sensitive to this problem. I knew expression order in the where clause sometimes must be enforced, but never that this problem extended beyond the where clause and into the select clause. Nor have I ever read of this problem anywhere!I find this a total mess and hope it is just an SQL Server 2008 implementation bug that can be fixed and not standard behavior. Because if it is standard, I think the people responsible for setting the standard are in dire need of some practical re-education. And I express myself quite mildly here.[/quote]Are you sure this bug (if it's not a bug I will give up using T-SQL as it's not a useful language) is restricted to string processing code? Why not simple arithmetic too, so that I have to write[code]select case when T1.y <> 0.0 then T1.x/T1.y else null end as ratio from T1 where IsNull(T1.y,0.0) <> 0.0[/code]or something equally ridiculous?There is an interesting statement in BoL at [url]http://msdn.microsoft.com/en-us/library/ms190623.aspx[/url][quote][b]BoL[/b][hr]A SELECT statement defines only the following:* The format of the result set. This is specified mostly in the select list. However, other clauses such as ORDER BY and GROUP BY also affect the final form of the result set.* The tables that contain the source data. This is specified in the FROM clause.* How the tables are logically related for the purposes of the SELECT statement. This is defined in the join specifications, which may appear in the WHERE clause or in an ON clause following FROM.* The conditions that the rows in the source tables must satisfy to qualify for the SELECT statement. These are specified in the WHERE and HAVING clauses.[/quote](similar statements are included in BoL for SQLS 2008, SQLS2005, SQLS 2000)It's noteworthy that the select statement does not, according to this BoL entry, specify how the values in the result set relate to the values in the tables from which the results are derived! If that were true, the language would serve no useful function - but evidently someone at MS believes it to be true. Perhaps the bug you describe is related to this bizarre belief ("qualify for the SELECT statement" is open to all sorts of interpretation, isn't it?). But I hope it's just an accidental bug.[/quote]Sorry to hear you're leaving T-SQL :crying: Unfortunately you probably won't find much love in any dialect of SQL. The problem (?) is that the SQL standard doesn't define physical processing steps. All that is left to the vendors. The standard simply says here's the keywords, here's the definition of the keywords, here's what you put in and here's what you get out. It defines logical processing order, but not the physical steps required to get from A to B. Every vendor uses their own physical implementation optimizations to get results back quickly, and that often means minimizing scans. Let's walk through this briefly and see what steps would be required to implement the original query to perform it in logical order steps:[code]select left( 'abc', T1.v )from T1where T1.v >= 0;[/code]Now let's say we have 10 million rows in T1 and 5 million of them match the where clause (T1.v >= 0). Here's what we need to do:1. Scan the 10 million row table (look at every single row) and compare to 0.2. Dump the 5 million rows that match the WHERE clause into a temp table or other intermediate storage.3. Scan the 5 million rows that match the WHERE clause and perform the projection against them (left( 'abc', T1.v )).We just scanned 15 million rows and stored 5 million rows in temporary storage to fulfill this query. Now let's look at one way we can optimize this:1. Scan the 10 million row table.2. Execute the WHERE clause and the projection at the same time on each row since both clauses are using the same columns.Obviously the so-called "buggy" execution plan requires the developer to be more careful, but gracefully handling invalid function parameter and divide-by-zero errors are not new problems in programming and shouldn't come as a surprise to most devs. Obviously if you wanted to force the physical order of execution you can always forcefully serialize the intermediate results yourself:[code]CREATE TABLE #t( v INT);INSERT INTO #t (v)SELECT T1.vFROM T1WHERE T1.v >= 0;SELECT LEFT( 'abc', #t.v)FROM #t;[/code]Mike CThu, 22 Jul 2010 13:44:19 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspx[quote][b]peter-757102 (7/22/2010)[/b][hr]I find this sort of thing many more times disturbing/dangerous then using a quirky update deliberatly and with full knowledge and documentation. Why? Because I suspect hardly anyone will know they have to protect their expressions to such a silly extend and the world therefore must be full of non-working code. In fact I expect that ** nearly all ** string processing code in use today to be sensitive to this problem. I knew expression order in the where clause sometimes must be enforced, but never that this problem extended beyond the where clause and into the select clause. Nor have I ever read of this problem anywhere!I find this a total mess and hope it is just an SQL Server 2008 implementation bug that can be fixed and not standard behavior. Because if it is standard, I think the people responsible for setting the standard are in dire need of some practical re-education. And I express myself quite mildly here.[/quote]Are you sure this bug (if it's not a bug I will give up using T-SQL as it's not a useful language) is restricted to string processing code? Why not simple arithmetic too, so that I have to write[code]select case when T1.y <> 0.0 then T1.x/T1.y else null end as ratio from T1 where IsNull(T1.y,0.0) <> 0.0[/code]or something equally ridiculous?There is an interesting statement in BoL at [url]http://msdn.microsoft.com/en-us/library/ms190623.aspx[/url][quote][b]BoL[/b][hr]A SELECT statement defines only the following:* The format of the result set. This is specified mostly in the select list. However, other clauses such as ORDER BY and GROUP BY also affect the final form of the result set.* The tables that contain the source data. This is specified in the FROM clause.* How the tables are logically related for the purposes of the SELECT statement. This is defined in the join specifications, which may appear in the WHERE clause or in an ON clause following FROM.* The conditions that the rows in the source tables must satisfy to qualify for the SELECT statement. These are specified in the WHERE and HAVING clauses.[/quote](similar statements are included in BoL for SQLS 2008, SQLS2005, SQLS 2000)It's noteworthy that the select statement does not, according to this BoL entry, specify how the values in the result set relate to the values in the tables from which the results are derived! If that were true, the language would serve no useful function - but evidently someone at MS believes it to be true. Perhaps the bug you describe is related to this bizarre belief ("qualify for the SELECT statement" is open to all sorts of interpretation, isn't it?). But I hope it's just an accidental bug.Thu, 22 Jul 2010 12:53:31 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspx[quote][b]peter-757102 (7/22/2010)[/b][hr]Mike C,I understand you point, I hope you can see mine too. In fact I never used the querky update anywhere as I never needed it. But more demanding cases might and as such I don't write it off, it just does not have my preference.Read the following and you will realize that querky update it the last thing you should worry about!Recently I "discovered" some pretty insane behavior in what seems like perfectly fine code. This ** might ** not be new to you, but I was really surprised/annoyed. It was a case where expressions in the select clause were executed before all conditions in a matching where clause were met. I always used to assume there where stages of processing, where filtering was first performed to reduce the size of the working set to a minimum as this makes perfect sense. It turns out this is not always happening and operations in the select clause can error on data that functionally should never get processed due to filtering in the where clause. In such cases you need to write protection code (case when ... else ... end) in the select clause, duplicating the relevant where conditions from the where clause!!! Derrived tables and other fancy constructions will not work and you never know beforehand when such a case turns up!Something as simple as:[code="sql"]select left( 'abc', T1.v )from T1where T1.v >= 0;[/code]Might go wrong when [b]T1.v[/b] can be a negative number and you should therefore write the query as:[code="sql"]select case when T1.v < 0 then null else left( 'abc', T1.v ) endfrom T1where T1.v >= 0;[/code]I find this sort of thing many more times disturbing/dangerous then using a quirky update deliberatly and with full knowledge and documentation. Why? Because I suspect hardly anyone will know they have to protect their expressions to such a silly extend and the world therefore must be full of non-working code. In fact I expect that ** nearly all ** string processing code in use today to be sensitive to this problem. I knew expression order in the where clause sometimes must be enforced, but never that this problem extended beyond the where clause and into the select clause. Nor have I ever read of this problem anywhere!I find this a total mess and hope it is just an SQL Server 2008 implementation bug that can be fixed and not standard behavior. Because if it is standard, I think the people responsible for setting the standard are in dire need of some practical re-education. And I express myself quite mildly here.[/quote]Hi Peter,I understand your point. I just don't like leaving the quality of my results up to the optimizer is all :) My point is that cursors are simply a means to override the optimizer. If someone was intent on overriding the optimizer's ability to do it's job anyway then why not use the documented methods? I don't see any benefit to using this particular method, but I do see a lot of potential downside.As for your other issue -- there is a logical processing order, and the WHERE clause logically comes early in the processing, but that doesn't mean it's performed that way in real time. By definition all that has to happen is that no matter what order the operations in the query are performed, the results generated must be the same as if it were executed in the prescribed logical order (errors/exceptions aside). There are very few areas in SQL in which order of operation within a statement is explicitly guaranteed.In this case it sounds as if the optimizer decided that it would be more efficient to query the T1.v column once and use that one scan to fulfill both references to it in your query. It apparently thought this was more efficient than scanning the table once to fulfill the WHERE clause and then spooling an intermediate result set to temporary storage, only to scan those intermediate results again to fulfill the T1.v reference in your projection. Except where it's explicitly documented, relying on specific order of execution within a query is a recipe for disaster :w00t:ThanksMike CThu, 22 Jul 2010 12:36:32 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspx[quote][b]Jeff Moden (7/22/2010)[/b][hr][quote][b]Mike C (7/21/2010)[/b][hr]Well... I've seen multirow string concatenations return completely incorrect results before based on simple things (like table indexes) at the whim of the optimizer. No need to intentionally try to break it, just an honest index change on a table to try to optimize for a particular query and suddenly the results are completely wrong. I believe Itzik posted a very simple example a while ago.The main problem I have with the quirky update and multirow variable aggs is when the calculation depends on specific ordering, since ordering just isn't guaranteed... especially (but not limited to) when we throw parallel processing into the mix. That's why I wanted to look at your example a little deeper, to see if it depends on ordering of inputs to generate the correct output. It may not, in which case it may be perfectly safe without throwing itself on the mercy of the optimizer :)In all fairness, in your article on Running Totals you list several rules just to keep the quirky update from breaking; it seems you have to be hell bent on avoiding unintentional breaks to make it work correctly :) Check out Itzik's article (I'll see if I can find a link), you may think another new rule is in order :)[/quote]Yep.... kinda like a cursor or any other code. If you write it wrong, you get incorrect results. ;-) The rules are pretty simple for usage and I did include verification code the combination of which is still faster than a cursor. The key is, if you don't trust it, don't use it. I've been using if for many years and it's never gone haywire. If you follow the rules, it won't.[/quote]I agree with your comparison of this method to cursors. Like a cursor, this method requires you to override the optimizer's ability to put together an efficient plan to return your results. Unlike cursors, the result of the multirow concatenation method is subject to changes in indexes, hardware, the position of the sun in the sky, or any other internal or external factor that could cause the optimizer to produce a different plan :) ...Then you have to come up with new rules :)Personally I don't trust it because the result that is returned relies on the optimizer's judgment, and how badly you want to override the optimizer's ability to produce an efficient plan. I don't trust it because I don't believe the optimizer should have any control over what your end result actually is -- it should only have a vote in how to most efficiently deliver the result you asked for. ;-)Thu, 22 Jul 2010 12:24:18 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspxMike C,I understand you point, I hope you can see mine too. In fact I never used the querky update anywhere as I never needed it. But more demanding cases might and as such I don't write it off, it just does not have my preference.Read the following and you will realize that querky update it the last thing you should worry about!Recently I "discovered" some pretty insane behavior in what seems like perfectly fine code. This ** might ** not be new to you, but I was really surprised/annoyed. It was a case where expressions in the select clause were executed before all conditions in a matching where clause were met. I always used to assume there where stages of processing, where filtering was first performed to reduce the size of the working set to a minimum as this makes perfect sense. It turns out this is not always happening and operations in the select clause can error on data that functionally should never get processed due to filtering in the where clause. In such cases you need to write protection code (case when ... else ... end) in the select clause, duplicating the relevant where conditions from the where clause!!! Derrived tables and other fancy constructions will not work and you never know beforehand when such a case turns up!Something as simple as:[code="sql"]select left( 'abc', T1.v )from T1where T1.v >= 0;[/code]Might go wrong when [b]T1.v[/b] can be a negative number and you should therefore write the query as:[code="sql"]select case when T1.v < 0 then null else left( 'abc', T1.v ) endfrom T1where T1.v >= 0;[/code]I find this sort of thing many more times disturbing/dangerous then using a quirky update deliberatly and with full knowledge and documentation. Why? Because I suspect hardly anyone will know they have to protect their expressions to such a silly extend and the world therefore must be full of non-working code. In fact I expect that ** nearly all ** string processing code in use today to be sensitive to this problem. I knew expression order in the where clause sometimes must be enforced, but never that this problem extended beyond the where clause and into the select clause. Nor have I ever read of this problem anywhere!I find this a total mess and hope it is just an SQL Server 2008 implementation bug that can be fixed and not standard behavior. Because if it is standard, I think the people responsible for setting the standard are in dire need of some practical re-education. And I express myself quite mildly here.Thu, 22 Jul 2010 09:28:13 GMTpeter-757102http://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspx[quote][b]Jeff Moden (7/22/2010)[/b][hr]Peter,I've not tried this particular example but I'm pretty sure that you don't actually need the index hint when you use an ORDER BY to get the correct order. The MAXDOP 1 is the important part.[/quote]Check the link Jeff, you see that it is required to make it function as expected as the data is retrieved from a non-clustered covering index instead of the clustered key. In itself not a problem, where it not that the concatenation is processed BEFORE the order by takes effect, resulting in just one tiny string instead of a concatenation of all strings.Thu, 22 Jul 2010 08:52:19 GMTpeter-757102http://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspx[quote][b]peter-757102 (7/22/2010)[/b][hr][quote][b]Mike C (7/21/2010)[/b][hr][quote][b]Jeff Moden (7/21/2010)[/b][hr][quote][b]Mike C (7/20/2010)[/b][hr]I'll have to look a little closer at that one tomorrow. Is multirow variable aggregation as unsafe as multirow string concatenation?[/quote]Gosh, Mike, I'm not sure why people think that. I understand the "slowness" of multirow string concatenation but "unsafe"? Same goes for multirow variable aggregation. I'm just not sure why people feel that way. I've seen people intentionally break such things in the "quirky update" (for example) but not for straight forward calculations not hell bent on intentional breaks.[/quote]I took a peek at your code, and it doesn't seem to rely on a specific ordering so I think you're definitely good in that regard :)Here's Itzik's article on string concatenation using multirow variable assignment: [url]http://www.sqlmag.com/article/sql-server/multi-row-variable-assignment-and-order-by.aspx[/url]. I actually ran into this issue at a recent client site before running across Itzik's article and finding the root cause.[/quote]That article you linked, does not make a good case as it clearly not applying the technique properly. If I modify the test code to force no parallelism and force the use of the clustered index, his code works as "expected" again.[code="sql"]DECLARE @s AS VARCHAR(MAX);SET @s = '';SELECT @s = @s + col2 + ';'FROM dbo.T1 with ( index(1) )ORDER BY col1option( maxdop 1 );SELECT @s;GO[/code][/quote]The technique is still relying on the mercy of the optimizer. Of course you could throw maxdop 1 on these quirky updates every time, or you could just set up a dedicated single processor server to handle this one task. You're still relying on the optimizer to choose the clustered index which may not be optimal--and may not even exist! Correct results should not rely on the mercy of the optimizer, or on forcing the optimizer to do sub-optimal work. That's actually the point of not using cursors and loops, so why replace one method of forcing suboptimal performance with another?[quote]I do share however some of the feelings of relying on undocumented behavior. I make an exception on proven behavior where the code is explicit enough about the way processing needs to take place. Table hints are an example of this, its takes optimizer randomness out of the equation, which in some cases is desired.And while forcing an index does not necessarily mandate the order in which it is traversed, I have never spotted it being traversed in reverse. Even if this were possible, the order by clause would surely persuade the optimizer to traverse the forced index in the intended way.[/quote]Persuade is the key word here. Conditions today may cause the expected result to be returned; however, a change in conditions tomorrow could dramatically change the plan costing and affect the result. Plan cost should only affect the efficiency of retrieving the results, it shouldn't change the result itself.[quote]This does not mean however, that I be using such techniques wherever I can for the sake of using it. I would use it merely in special cases where there gain of using it warrants the "risk" of it breaking in a later incarnation of SQL server. And to some extend, even documentend behaviour carries this same risk! The difference being you might spot a change in a documented feature sooner as it will be advertised.[/quote]In risk management there is always some level of risk. Your risk of getting eaten by sharks is much lower if you don't swim in the shark tank.Thu, 22 Jul 2010 08:36:02 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspx[quote][b]peter-757102 (7/22/2010)[/b][hr][quote][b]Mike C (7/21/2010)[/b][hr][quote][b]Jeff Moden (7/21/2010)[/b][hr][quote][b]Mike C (7/20/2010)[/b][hr]I'll have to look a little closer at that one tomorrow. Is multirow variable aggregation as unsafe as multirow string concatenation?[/quote]Gosh, Mike, I'm not sure why people think that. I understand the "slowness" of multirow string concatenation but "unsafe"? Same goes for multirow variable aggregation. I'm just not sure why people feel that way. I've seen people intentionally break such things in the "quirky update" (for example) but not for straight forward calculations not hell bent on intentional breaks.[/quote]I took a peek at your code, and it doesn't seem to rely on a specific ordering so I think you're definitely good in that regard :)Here's Itzik's article on string concatenation using multirow variable assignment: [url]http://www.sqlmag.com/article/sql-server/multi-row-variable-assignment-and-order-by.aspx[/url]. I actually ran into this issue at a recent client site before running across Itzik's article and finding the root cause.[/quote]That article you linked, does not make a good case as it clearly not applying the technique properly. If I modify the test code to force no parallelism and force the use of the clustered index, his code works as "expected" again.[code="sql"]DECLARE @s AS VARCHAR(MAX);SET @s = '';SELECT @s = @s + col2 + ';'FROM dbo.T1 with ( index(1) )ORDER BY col1option( maxdop 1 );SELECT @s;GO[/code]I do share however some of the feelings of relying on undocumented behavior. I make an exception on proven behavior where the code is explicit enough about the way processing needs to take place. Table hints are an example of this, its takes optimizer randomness out of the equation, which in some cases is desired.And while forcing an index does not necessarily mandate the order in which it is traversed, I have never spotted it being traversed in reverse. Even if this were possible, the order by clause would surely persuade the optimizer to traverse the forced index in the intended way.This does not mean however, that I be using such techniques wherever I can for the sake of using it. I would use it merely in special cases where there gain of using it warrants the "risk" of it breaking in a later incarnation of SQL server. And to some extend, even documentend behaviour carries this same risk! The difference being you might spot a change in a documented feature sooner as it will be advertised.[/quote]Hi Peter,I've not tried this particular example but I'm pretty sure that you don't actually need the index hint when you use an ORDER BY to get the correct order. The MAXDOP 1 is the important part.Thu, 22 Jul 2010 08:04:21 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspx[quote][b]Mike C (7/21/2010)[/b][hr][quote][b]Jeff Moden (7/21/2010)[/b][hr][quote][b]Mike C (7/20/2010)[/b][hr]I'll have to look a little closer at that one tomorrow. Is multirow variable aggregation as unsafe as multirow string concatenation?[/quote]Gosh, Mike, I'm not sure why people think that. I understand the "slowness" of multirow string concatenation but "unsafe"? Same goes for multirow variable aggregation. I'm just not sure why people feel that way. I've seen people intentionally break such things in the "quirky update" (for example) but not for straight forward calculations not hell bent on intentional breaks.[/quote]Well... I've seen multirow string concatenations return completely incorrect results before based on simple things (like table indexes) at the whim of the optimizer. No need to intentionally try to break it, just an honest index change on a table to try to optimize for a particular query and suddenly the results are completely wrong. I believe Itzik posted a very simple example a while ago.The main problem I have with the quirky update and multirow variable aggs is when the calculation depends on specific ordering, since ordering just isn't guaranteed... especially (but not limited to) when we throw parallel processing into the mix. That's why I wanted to look at your example a little deeper, to see if it depends on ordering of inputs to generate the correct output. It may not, in which case it may be perfectly safe without throwing itself on the mercy of the optimizer :)In all fairness, in your article on Running Totals you list several rules just to keep the quirky update from breaking; it seems you have to be hell bent on avoiding unintentional breaks to make it work correctly :) Check out Itzik's article (I'll see if I can find a link), you may think another new rule is in order :)[/quote]Yep.... kinda like a cursor or any other code. If you write it wrong, you get incorrect results. ;-) The rules are pretty simple for usage and I did include verification code the combination of which is still faster than a cursor. The key is, if you don't trust it, don't use it. I've been using if for many years and it's never gone haywire. If you follow the rules, it won't.Thu, 22 Jul 2010 08:00:38 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspx[quote][b]Mike C (7/21/2010)[/b][hr][quote][b]Jeff Moden (7/21/2010)[/b][hr][quote][b]Mike C (7/20/2010)[/b][hr]I'll have to look a little closer at that one tomorrow. Is multirow variable aggregation as unsafe as multirow string concatenation?[/quote]Gosh, Mike, I'm not sure why people think that. I understand the "slowness" of multirow string concatenation but "unsafe"? Same goes for multirow variable aggregation. I'm just not sure why people feel that way. I've seen people intentionally break such things in the "quirky update" (for example) but not for straight forward calculations not hell bent on intentional breaks.[/quote]I took a peek at your code, and it doesn't seem to rely on a specific ordering so I think you're definitely good in that regard :)Here's Itzik's article on string concatenation using multirow variable assignment: [url]http://www.sqlmag.com/article/sql-server/multi-row-variable-assignment-and-order-by.aspx[/url]. I actually ran into this issue at a recent client site before running across Itzik's article and finding the root cause.[/quote]That article you linked, does not make a good case as it clearly not applying the technique properly. If I modify the test code to force no parallelism and force the use of the clustered index, his code works as "expected" again.[code="sql"]DECLARE @s AS VARCHAR(MAX);SET @s = '';SELECT @s = @s + col2 + ';'FROM dbo.T1 with ( index(1) )ORDER BY col1option( maxdop 1 );SELECT @s;GO[/code]I do share however some of the feelings of relying on undocumented behavior. I make an exception on proven behavior where the code is explicit enough about the way processing needs to take place. Table hints are an example of this, its takes optimizer randomness out of the equation, which in some cases is desired.And while forcing an index does not necessarily mandate the order in which it is traversed, I have never spotted it being traversed in reverse. Even if this were possible, the order by clause would surely persuade the optimizer to traverse the forced index in the intended way.This does not mean however, that I be using such techniques wherever I can for the sake of using it. I would use it merely in special cases where there gain of using it warrants the "risk" of it breaking in a later incarnation of SQL server. And to some extend, even documentend behaviour carries this same risk! The difference being you might spot a change in a documented feature sooner as it will be advertised.Thu, 22 Jul 2010 04:03:13 GMTpeter-757102http://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspx[quote][b]Jeff Moden (7/21/2010)[/b][hr][quote][b]Mike C (7/20/2010)[/b][hr]I'll have to look a little closer at that one tomorrow. Is multirow variable aggregation as unsafe as multirow string concatenation?[/quote]Gosh, Mike, I'm not sure why people think that. I understand the "slowness" of multirow string concatenation but "unsafe"? Same goes for multirow variable aggregation. I'm just not sure why people feel that way. I've seen people intentionally break such things in the "quirky update" (for example) but not for straight forward calculations not hell bent on intentional breaks.[/quote]I took a peek at your code, and it doesn't seem to rely on a specific ordering so I think you're definitely good in that regard :)Here's Itzik's article on string concatenation using multirow variable assignment: [url]http://www.sqlmag.com/article/sql-server/multi-row-variable-assignment-and-order-by.aspx[/url]. I actually ran into this issue at a recent client site before running across Itzik's article and finding the root cause.Wed, 21 Jul 2010 20:40:03 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspx[quote][b]Jeff Moden (7/21/2010)[/b][hr][quote][b]Mike C (7/20/2010)[/b][hr]I'll have to look a little closer at that one tomorrow. Is multirow variable aggregation as unsafe as multirow string concatenation?[/quote]Gosh, Mike, I'm not sure why people think that. I understand the "slowness" of multirow string concatenation but "unsafe"? Same goes for multirow variable aggregation. I'm just not sure why people feel that way. I've seen people intentionally break such things in the "quirky update" (for example) but not for straight forward calculations not hell bent on intentional breaks.[/quote]Well... I've seen multirow string concatenations return completely incorrect results before based on simple things (like table indexes) at the whim of the optimizer. No need to intentionally try to break it, just an honest index change on a table to try to optimize for a particular query and suddenly the results are completely wrong. I believe Itzik posted a very simple example a while ago.The main problem I have with the quirky update and multirow variable aggs is when the calculation depends on specific ordering, since ordering just isn't guaranteed... especially (but not limited to) when we throw parallel processing into the mix. That's why I wanted to look at your example a little deeper, to see if it depends on ordering of inputs to generate the correct output. It may not, in which case it may be perfectly safe without throwing itself on the mercy of the optimizer :)In all fairness, in your article on Running Totals you list several rules just to keep the quirky update from breaking; it seems you have to be hell bent on avoiding unintentional breaks to make it work correctly :) Check out Itzik's article (I'll see if I can find a link), you may think another new rule is in order :)Wed, 21 Jul 2010 20:23:31 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspx[quote][b]kjc-1057161 (7/21/2010)[/b][hr]Thanks Jeff! I've read your Tally tables articles before, but was having a complete blind spot as to how it applied here to this problem I'll run some checks on the outputs there, then I'll have to do some work on how it fits into the rest of the procedure (looping through accounts, and looping through periods), but this has been an enormous help![/quote]Thanks kjc... you might want to read the article on "Running Totals", as well. It may help you avoid more loops in a very high speed fashion. It's a long article for a short subject with lot's of proofs. The important part is, if you don't follow the rules exactly, you WILL get incorrect answers. If you can't follow the rules for some reason, then you'll probably need to stick to some form of loop. Here's the link...[url]http://www.sqlservercentral.com/articles/T-SQL/68467/[/url]Wed, 21 Jul 2010 06:26:20 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic704504-1226-1.aspx[quote][b]Mike C (7/20/2010)[/b][hr]I'll have to look a little closer at that one tomorrow. Is multirow variable aggregation as unsafe as multirow string concatenation?[/quote]Gosh, Mike, I'm not sure why people think that. I understand the "slowness" of multirow string concatenation but "unsafe"? Same goes for multirow variable aggregation. I'm just not sure why people feel that way. I've seen people intentionally break such things in the "quirky update" (for example) but not for straight forward calculations not hell bent on intentional breaks.Wed, 21 Jul 2010 06:21:02 GMTJeff Moden