SQL Prompt: For Your Group By Problems

I’m going to point people to this that have “My Group By isn’t working” questions…

The Joys of SQL:

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;
Works Written
0’s! Amazing!

The Pains of SQL:

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!

Need Group By
Pizza…French Fries…Pizza

The Repetitiveness of Questioners:

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;
Needs second column
French Fries….

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.

The Enlightenment of Questioners:

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;
Works Written
PIZZA!

The Euphoria of SQL Prompt:

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.

gb shortcut
Wait? I can order Pizza?

The Ecstasy of SQL Prompt:

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.

Shortcut shortcut
Whoa! Whoa! You can deliver Pizza to me?

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…

Final Works
Like whatever happened to Pizza in 30 mins or free?

Pizza:

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.

Gotta Love That LIKE

LIKE a function… now the song is stuck in your head!
… I’m not sorry…

Start: (‘abc%’)

Ever heard of “Osmosis”? You know, the…

process of gradual or unconscious assimilation of ideas, knowledge, etc.

For the longest time, that was how I thought people learned in SQL Server. You hang around a technology for long enough and the information about it slowly trickles into your brain.

I would hazard that the vast majority of people learn like this. They work with something long enough and slowly they develop, if not a mastery of the subject, then a familiarity with it.

That’s how I learned Transact-SQL anyway.
Working in a help desk, trouble-shooting stored procedures and ad hoc statements; cranking out reports left, right and center, slowly absorbing the differences between INNERLEFT, RIGHT, and FULL joins. Realizing that there is a vast difference between excluding results with a WHERE clause and with a HAVING clause.

Ahh good times!

However, now I’m in the mindset that if you really want to learn something then study it; purposefully and deliberately.

And with all the new features being released for SQL Server 2016, you would be amazed at what I can learn about features that were released in SQL Server 2008.

So here’s some little known facts I learned about LIKE

Middle: (‘%lmnop%’)

Safe to say, that we’ve all used LIKE, we’ve all seen LIKE, and we’re probably all going to continue to use LIKE.
But are we using it to the best of our ability?
I wasn’t.

So let’s test out this bad boy using the WideWorldImporters database, see if we can find everyone with the first name of Leyla.

Simple right? And because [Sales].[Customers] uses the full name, we have to use LIKE.

SELECT CustomerName
, CustomerID
FROM Sales.Customers
WHERE CustomerName LIKE 'Leyla%';
GO
leyla
LEYYYYla!!!!

Now a developer comes along and says “Wait a second, my sister is Leila”. So we try to cheat and add a wildcard in there.

SELECT CustomerName
, CustomerID
FROM Sales.Customers
WHERE CustomerName LIKE 'le%a%';
GO
leiyla
…you got me on my knees…

Leonardo!? Well I suppose he does count in this situation, but there’s 2 characters between the ‘e’ and the ‘a’ and I only wanted one.

Well you can specify only 1 wildcard with the LIKE function by using the underscore (‘_’), so let’s try that.

SELECT CustomerName
, CustomerID
FROM Sales.Customers
WHERE CustomerName LIKE 'Le_la%';
GO
lejla
…singing darlin’ please…

Yes, I cheated and inserted that extra name ‘Lejla’.

Call it Poetic Licence but I only used it to show that we still have options if this is not the results that we want. We are only interested in ‘Leyla’ and ‘Leila’.

‘Lejla’, while a lovely name I’m sure, is not what we require right this second. So what are we to do?

Well, did you know that LIKE has the range function as well? What’s range got to do with it? Well, what happens if we only put in a range of 2 characters?

SELECT CustomerName
, CustomerID
FROM Sales.Customers
WHERE CustomerName LIKE 'Le[iy]la%';
GO
better
….darlin’ won’t you hear my foolish cry!

There we go! Only the results that we want and none of that Lejla, Leonardo business.

Now you could argue with me (I encourage it actually. How else am I to learn?) and say that you would never do it this way. That it is much easier to do something along the lines of this:

SELECT CustomerName
, CustomerID
FROM Sales.Customers
WHERE CustomerName LIKE 'Leyla%'
OR CustomerName LIKE 'Leila%';
GO

I’ll admit that the above reads a lot easier, but it doesn’t scale very well though. What happens if we want to include the Leala, Lebla, Lecla,….all the way to Lenla’s? Are you going to write out 15 different clauses? 1 for each different character?

SELECT CustomerName
, CustomerID
FROM Sales.Customers
WHERE CustomerName LIKE 'Leyla%'
OR CustomerName LIKE 'Leila%'
OR ....
OR ...
OR ..
OR .
GO

Or are you going to go back to ranges and do a clean, efficient, single range?

SELECT CustomerName
, CustomerID
FROM Sales.Customers
WHERE CustomerName LIKE 'Le[a-ny]la%';
GO

Now I’d argue that that is a lot more readable than an endless list of OR clauses tacked on to the end of a script.

lejla
Oh wait, it’s Layla isn’t it? Not Leyla!

End: (‘%xyz’)

There is a lot more you can do with the LIKE function. Just because you may encounter this little guy every day does not mean that you know it.

Check out the documentation on MSDN. There’s information there like Pattern Matching with the ESCAPE Clause and different wildcard characters.

Don’t shy away from the fundamentals. Every little bit that you learn can and more than likely will be used to improve your skills and make you better.

Hopefully these little tidbits of knowledge will sink in…just like osmosis 🙂