Words: 400
Time to read: ~ 2 minutes
Continue reading “You Cannot Rely Solely on Third-Party Tools”
Loving an instrument DESPITE it’s faults, not because it doesn’t have any…
Words: 400
Time to read: ~ 2 minutes
Continue reading “You Cannot Rely Solely on Third-Party Tools”
I’m going to point people to this that have “My Group By isn’t working” questions…
Did you know that the SQL language allows you to do amazing analysis of data such as aggregate functions?
SELECT t.session_id, t.request_id, SUM(t.user_objects_alloc_page_count) AS UserObjectAllocated, SUM(t.user_objects_dealloc_page_count) AS UserObjectDeallocated FROM sys.dm_db_task_space_usage AS t GROUP BY t.session_id, t.request_id;
But…if you forget to put in the GROUP BY
clause, as a ski instructor once said, you’re going to have a bad time!
So some eager yet lost scholar ventures into this land of aggregate functions, reads the error message and adds in a GROUP BY
clause.
SELECT t.session_id, t.request_id, SUM(t.user_objects_alloc_page_count) AS UserObjectAllocated, SUM(t.user_objects_dealloc_page_count) AS UserObjectDeallocated FROM sys.dm_db_task_space_usage AS t GROUP BY t.session_id;
Now don’t scoff, this happens. I mean the error message is still red, looks nearly identical to the original one encountered, and can cause a rage-inducing damnation of SQL Server error messages.
Trawling the bulletin boards, question sites, and forums – okay maybe a quick question online, it’s called poetic exaggeration people! – they eventually learn the folly of their ways and correct their mistake.
SELECT t.session_id, t.request_id, SUM(t.user_objects_alloc_page_count) AS UserObjectAllocated, SUM(t.user_objects_dealloc_page_count) AS UserObjectDeallocated FROM sys.dm_db_task_space_usage AS t GROUP BY t.session_id, t.request_id;
Now I consider myself lucky that work has invested in the RedGate tools, and right now, especially SQL Prompt.
I’m not going to talk about “Save and Recover Lost Tabs” – saved my ass many times.
I’m not going to talk about “Code Formatting” – saved my sanity many times.
I’m going to talk about “Autocomplete”.
A well-known secret with SQL Prompt’s autocomplete is the snippets feature. With this, you can increase your productivity by 75% from typing out G R O U P [space] B Y
and instead use gb
and hit tab.
Do not get me wrong, a 75% increase in productivity? I’ll take that!
That is a well-known secret though, and it’s slightly hard to get excited about a well-known secret.
However, what if I told you that SQL Prompt had another lesser-known secret that can increase your productivity and ensure that you do not forgot to add the necessary columns to your GROUP BY
clause?
Interested? Ah c’mon!
You sure you’re not interested?…. That’s better!
So first of all, let us increase the number of non-aggregated columns in our SELECT
to include database_id, is_remote_work, and exec_context_id. Including our session_id and request_id these are all columns that we are going to need to add to our GROUP BY
clause, because…well…business logic.
Only problem is ain’t nobody got time for that.
SQL Prompt knows this and adds the following little snippet after a GROUP BY
autocomplete.
Hitting tab on that includes everything in the SELECT
that is not part of an aggregate function, leaving us to concern ourselves with loftier things…
Now I don’t work for pizza RedGate, I’m not affiliated with them, and I don’t get any money off of them. In fact, I’d say that they’d happily pay me not to write about them but when I found this autocomplete feature, I got too happy not to share it!
So save yourself the trouble of typing everything out and spare yourself the pain of error messages.
Use this lesser-known secret and have more time for pizza.