February 28, 2018 at 6:46 am
Jeff Moden - Tuesday, February 27, 2018 5:55 PMdrew.allen - Tuesday, February 27, 2018 11:12 AMPhil Parkin - Tuesday, February 27, 2018 10:12 AMdrew.allen - Tuesday, February 27, 2018 9:59 AMPhil Parkin - Tuesday, February 27, 2018 9:02 AMNote that it uses a technique called the 'quirky update' to update the group number, which relies on there being a CLUSTERED PRIMARY KEY on the IDENTITY column.I would avoid using the "quirky update" if there is another performant solution available. There are a lot of factors that go into getting the quirky update right and it's too easy to miss something.
Drew
Your caution is sensible, but what did I miss?
I didn't say that YOU missed something. This was more a comment for anyone reading this thread that might be inclined to try the "quirky update".
Drew
You should learn how to use it properly before making such recommendations. 😉
I have to agree with Drew and advise against using the quirky update and additionally I'm sure knowing Drew's history here he isn't some random naysayer and he probably knows exactly how it works. Heck I even remember you using some sort of flag to test for the failure to run as predicted and I don't see any version of that test in the posted code. Phil how does that routine catch the case when quirky update fails to run as planned?
Jeff I notice that you didn't ask Drew about his reservations but instead declared that Drew has some sort of deficit in understanding. How are you so sure that Drew lacks the ability to understand how quirky update works? Knowing Drew's post history, I've seen no indication that the mechanics behind this are beyond his abilities. Can you describe what you know about Drew that would call into question his abilities with T-SQL that I might have missed?
February 28, 2018 at 7:05 am
patrickmcginnis59 10839 - Wednesday, February 28, 2018 6:46 AMI have to agree with Drew and advise against using the quirky update and additionally I'm sure knowing Drew's history here he isn't some random naysayer and he probably knows exactly how it works. Heck I even remember you using some sort of flag to test for the failure to run as predicted and I don't see any version of that test in the posted code. Phil how does that routine catch the case when quirky update fails to run as planned?
As far as I know, quirky update does not 'fail to run as planned' in any circumstances, as long as the 'rules' for using it are observed, as documented here.
If you know otherwise, please post details and I will revise my opinion.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 28, 2018 at 7:10 am
Phil Parkin - Wednesday, February 28, 2018 7:05 AMpatrickmcginnis59 10839 - Wednesday, February 28, 2018 6:46 AMI have to agree with Drew and advise against using the quirky update and additionally I'm sure knowing Drew's history here he isn't some random naysayer and he probably knows exactly how it works. Heck I even remember you using some sort of flag to test for the failure to run as predicted and I don't see any version of that test in the posted code. Phil how does that routine catch the case when quirky update fails to run as planned?As far as I know, quirky update does not 'fail to run as planned' in any circumstances, as long as the 'rules' for using it are observed, as documented here.
If you know otherwise, please post details and I will revise my opinion.
I went back and reread the article, there wasn't actually a flag, there was a test to see if it worked. My mistake!
edit: I would at least strongly advise to read the linked article. Like Drew says, theres too many things that you have to keep track of. Additionally, if theres another way of doing this I'd advise avoiding it also.
For instance, I also noticed you didn't have a clustered index on the table with the correct column you were running quirky on. Isn't that item number one in the article?
edit 2: I see you did include that requirement in your comment so that's a plus!
February 28, 2018 at 7:33 am
patrickmcginnis59 10839 - Wednesday, February 28, 2018 7:10 AMPhil Parkin - Wednesday, February 28, 2018 7:05 AMpatrickmcginnis59 10839 - Wednesday, February 28, 2018 6:46 AMI have to agree with Drew and advise against using the quirky update and additionally I'm sure knowing Drew's history here he isn't some random naysayer and he probably knows exactly how it works. Heck I even remember you using some sort of flag to test for the failure to run as predicted and I don't see any version of that test in the posted code. Phil how does that routine catch the case when quirky update fails to run as planned?As far as I know, quirky update does not 'fail to run as planned' in any circumstances, as long as the 'rules' for using it are observed, as documented here.
If you know otherwise, please post details and I will revise my opinion.I went back and reread the article, there wasn't actually a flag, there was a test to see if it worked. My mistake!
edit: I would at least strongly advise to read the linked article. Like Drew says, theres too many things that you have to keep track of. Additionally, if theres another way of doing this I'd advise avoiding it also.
For instance, I also noticed you didn't have a clustered index on the table with the correct column you were running quirky on. Isn't that item number one in the article?
edit 2: I see you did include that requirement in your comment so that's a plus!
Rule number 1 is as follows:
CLUSTERED INDEX MUST BE PRESENT IN THE CORRECT ORDER: You must have a clustered index on the table that is in the correct order to support the updates in the required procedural order. It does NOT have to be the primary key but it MUST be a clustered index.
My CREATE TABLE included the following
RowNo INT IDENTITY(1, 1) PRIMARY KEY NOT NULL
Which creates a clustered index on RowNo, which is the 'ordering column' for the update. I should have included the CLUSTERED keyword for clarity, though it is not necessary. So I think I did it right.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 28, 2018 at 7:45 am
Phil Parkin - Wednesday, February 28, 2018 7:33 AMpatrickmcginnis59 10839 - Wednesday, February 28, 2018 7:10 AMPhil Parkin - Wednesday, February 28, 2018 7:05 AMpatrickmcginnis59 10839 - Wednesday, February 28, 2018 6:46 AMI have to agree with Drew and advise against using the quirky update and additionally I'm sure knowing Drew's history here he isn't some random naysayer and he probably knows exactly how it works. Heck I even remember you using some sort of flag to test for the failure to run as predicted and I don't see any version of that test in the posted code. Phil how does that routine catch the case when quirky update fails to run as planned?As far as I know, quirky update does not 'fail to run as planned' in any circumstances, as long as the 'rules' for using it are observed, as documented here.
If you know otherwise, please post details and I will revise my opinion.I went back and reread the article, there wasn't actually a flag, there was a test to see if it worked. My mistake!
edit: I would at least strongly advise to read the linked article. Like Drew says, theres too many things that you have to keep track of. Additionally, if theres another way of doing this I'd advise avoiding it also.
For instance, I also noticed you didn't have a clustered index on the table with the correct column you were running quirky on. Isn't that item number one in the article?
edit 2: I see you did include that requirement in your comment so that's a plus!
Rule number 1 is as follows:
CLUSTERED INDEX MUST BE PRESENT IN THE CORRECT ORDER: You must have a clustered index on the table that is in the correct order to support the updates in the required procedural order. It does NOT have to be the primary key but it MUST be a clustered index.
My CREATE TABLE included the following
RowNo INT IDENTITY(1, 1) PRIMARY KEY NOT NULL
Which creates a clustered index on RowNo, which is the 'ordering column' for the update. I should have included the CLUSTERED keyword for clarity, though it is not necessary. So I think I did it right.
Yes you did, and I actually went looking for the index, so another mistake on my part, sorry about that!
February 28, 2018 at 7:48 am
patrickmcginnis59 10839 - Wednesday, February 28, 2018 6:46 AMJeff Moden - Tuesday, February 27, 2018 5:55 PMdrew.allen - Tuesday, February 27, 2018 11:12 AMPhil Parkin - Tuesday, February 27, 2018 10:12 AMdrew.allen - Tuesday, February 27, 2018 9:59 AMPhil Parkin - Tuesday, February 27, 2018 9:02 AMNote that it uses a technique called the 'quirky update' to update the group number, which relies on there being a CLUSTERED PRIMARY KEY on the IDENTITY column.I would avoid using the "quirky update" if there is another performant solution available. There are a lot of factors that go into getting the quirky update right and it's too easy to miss something.
Drew
Your caution is sensible, but what did I miss?
I didn't say that YOU missed something. This was more a comment for anyone reading this thread that might be inclined to try the "quirky update".
Drew
You should learn how to use it properly before making such recommendations. 😉
I have to agree with Drew and advise against using the quirky update and additionally I'm sure knowing Drew's history here he isn't some random naysayer and he probably knows exactly how it works. Heck I even remember you using some sort of flag to test for the failure to run as predicted and I don't see any version of that test in the posted code. Phil how does that routine catch the case when quirky update fails to run as planned?
Jeff I notice that you didn't ask Drew about his reservations but instead declared that Drew has some sort of deficit in understanding. How are you so sure that Drew lacks the ability to understand how quirky update works? Knowing Drew's post history, I've seen no indication that the mechanics behind this are beyond his abilities. Can you describe what you know about Drew that would call into question his abilities with T-SQL that I might have missed?
Because Drew is a smart person and would have stated the reasons if he'd actually done some testing. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
February 28, 2018 at 7:53 am
Jeff Moden - Tuesday, February 27, 2018 5:55 PMdrew.allen - Tuesday, February 27, 2018 11:12 AMPhil Parkin - Tuesday, February 27, 2018 10:12 AMdrew.allen - Tuesday, February 27, 2018 9:59 AMPhil Parkin - Tuesday, February 27, 2018 9:02 AMNote that it uses a technique called the 'quirky update' to update the group number, which relies on there being a CLUSTERED PRIMARY KEY on the IDENTITY column.I would avoid using the "quirky update" if there is another performant solution available. There are a lot of factors that go into getting the quirky update right and it's too easy to miss something.
Drew
Your caution is sensible, but what did I miss?
I didn't say that YOU missed something. This was more a comment for anyone reading this thread that might be inclined to try the "quirky update".
Drew
You should learn how to use it properly before making such recommendations. 😉
I do know how to run it properly. I just prefer not to use unsupported, undocumented hacks that are not guaranteed to work in future versions when I have a well supported, well documented performant alternative.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 28, 2018 at 8:09 am
Jeff Moden - Wednesday, February 28, 2018 7:48 AMpatrickmcginnis59 10839 - Wednesday, February 28, 2018 6:46 AMJeff Moden - Tuesday, February 27, 2018 5:55 PMdrew.allen - Tuesday, February 27, 2018 11:12 AMPhil Parkin - Tuesday, February 27, 2018 10:12 AMdrew.allen - Tuesday, February 27, 2018 9:59 AMPhil Parkin - Tuesday, February 27, 2018 9:02 AMNote that it uses a technique called the 'quirky update' to update the group number, which relies on there being a CLUSTERED PRIMARY KEY on the IDENTITY column.I would avoid using the "quirky update" if there is another performant solution available. There are a lot of factors that go into getting the quirky update right and it's too easy to miss something.
Drew
Your caution is sensible, but what did I miss?
I didn't say that YOU missed something. This was more a comment for anyone reading this thread that might be inclined to try the "quirky update".
Drew
You should learn how to use it properly before making such recommendations. 😉
I have to agree with Drew and advise against using the quirky update and additionally I'm sure knowing Drew's history here he isn't some random naysayer and he probably knows exactly how it works. Heck I even remember you using some sort of flag to test for the failure to run as predicted and I don't see any version of that test in the posted code. Phil how does that routine catch the case when quirky update fails to run as planned?
Jeff I notice that you didn't ask Drew about his reservations but instead declared that Drew has some sort of deficit in understanding. How are you so sure that Drew lacks the ability to understand how quirky update works? Knowing Drew's post history, I've seen no indication that the mechanics behind this are beyond his abilities. Can you describe what you know about Drew that would call into question his abilities with T-SQL that I might have missed?
Because Drew is a smart person and would have stated the reasons if he'd actually done some testing. 😉
I know in my case I think the code should be explicitly supported by Microsoft, and I think the reason it isn't is that the table has to spool in a particular order before this can work. Now on the other hand, there doesn't look to be any reason that SQL Server WOULD reorder the table when spooling but at a very fundamental level, we still are supposed to assume that SQL can reorder tables whenever it needs to. Its sort of a subjective thing, we go about our work in SQL Server making sure we never assume order in tables without an explicit order by, and I think that's why some folks express reservations, because quirky update contradicts this assumption. If the code actually could work with the "order by" offering a bit of a guarantee, I'd probably have less of an objection.
Heck, even in your original article you acknowledge folks concern.
Admittedly, the "Quirky Update" (update variables and columns at the same time) is a highly controversial method. Many folks simply don't trust it and about as many simply condemn it as a "hack that uses undocumented features". However, without exception and to date, if they follow the rules, none of them have been able to break it... once the necessary formulas have been made to work correctly, it always works. You may be among those that don't trust it and I certainly understand the healthy skepticism.
But that's a subjective thing, and I know how having subjective conversations are certainly easy to delve into disagreements so I want to also acknowledge that concern. Its sort of like the "goto" operation, that always works as specified, but there are genuinely subjective reasons why people don't like it.
February 28, 2018 at 9:06 am
Jeff Moden - Wednesday, February 28, 2018 7:48 AMBecause Drew is a smart person and would have stated the reasons if he'd actually done some testing. 😉
Thanks for the compliment, but I don't need to test to know that the "quirky update" is undocumented and unsupported. I don't want to take the risk that MS will change something that suddenly causes this undocumented, unsupported hack to fail when I could have implemented a very well-performing, documented, and supported alternative.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 28, 2018 at 3:21 pm
drew.allen - Wednesday, February 28, 2018 7:53 AMJeff Moden - Tuesday, February 27, 2018 5:55 PMdrew.allen - Tuesday, February 27, 2018 11:12 AMPhil Parkin - Tuesday, February 27, 2018 10:12 AMdrew.allen - Tuesday, February 27, 2018 9:59 AMPhil Parkin - Tuesday, February 27, 2018 9:02 AMNote that it uses a technique called the 'quirky update' to update the group number, which relies on there being a CLUSTERED PRIMARY KEY on the IDENTITY column.I would avoid using the "quirky update" if there is another performant solution available. There are a lot of factors that go into getting the quirky update right and it's too easy to miss something.
Drew
Your caution is sensible, but what did I miss?
I didn't say that YOU missed something. This was more a comment for anyone reading this thread that might be inclined to try the "quirky update".
Drew
You should learn how to use it properly before making such recommendations. 😉
I do know how to run it properly. I just prefer not to use unsupported, undocumented hacks that are not guaranteed to work in future versions when I have a well supported, well documented performant alternative.
Drew
Since they came out with the Lead/Lag stuff in 2012, I agree with the notion of not using the Quirky Update because of "supportability" issues and some pretty strict rules. Still, there are a lot of folks that aren't using 2012. It's also not entirely undocumented. The 3 part update IS covered in documentation for UPDATE... too bad that MS doesn't know how to use it and lists it as something that should only be used for one row. I guess that's partially what makes it "unsupported". That and Connor Cunningham said he wouldn't support it. Too bad... even with Lead/Lag, it can still be a useful tool.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply