A Fine Slice Of SQL Server
Erik Darling shows us a table having an existential crisis:
This is one of the most frustrating things I’ve seen from the optimizer in quite a while.
Here are a couple tables, with a foreign key between them:
Read on to see how EXISTS and NOT EXISTS handle an empty child table.
Chad Callihan walks us through using AT TIME ZONE in SQL Server:
Dealing with time zones in general can be a headache. Thankfully, SQL Server 2016 included a new clause to make working with time zones more manageable. Let’s look at a couple examples:
I’m in Eastern Standard Time so we’ll start here. Keep in mind that when no offset information is included, SQL Server is going to assume that the date value is in the target time zone.
This works best when your dates are stored in UTC, but Chad does show how to convert between two other time zones. This does not perform all that well when you need to convert a lot of rows, but if you’re doing one or two conversions, it’ll do just fine.
Comments closedHugo Kornelis explains what the Concatenation operator does:
The Concatenation operator reads and returns all rows from all its inputs, in order, and without modification.
This operator is most commonly used to execute queries that use UNION or UNION ALL. In the former case, other operators are required to remove the duplicates, because Concatenation doesn’t provide that functionality. You may also find the Concatenation operator in queries on partitioned views.
Read on to see the algorithm and lots of details about the operator.
Comments closedAaron Bertrand tries something different:
There are features many of us shy away from, like cursors, triggers, and dynamic SQL. There is no question they each have their use cases, but when we see a trigger with a cursor inside dynamic SQL, it can make us cringe (triple whammy).
Plan guides and sp_prepare are in a similar boat: if you saw me using one of them, you’d raise an eyebrow; if you saw me using them together, you’d probably check my temperature. But, as with cursors, triggers, and dynamic SQL, they have their use cases. And I recently came across a scenario where using them together was beneficial.
Read on to see the method to this madness.
Comments closedSteve Jones answers a question:
Recently I ran across a question posted by a beginner on the Internet and thought this would be a good, basic topic to cover. The question was: how can I replace a value in a comma separated string in a table?
This post covers the basics of this task.
Incidentally, this is where I say hey, that sounds like a failure in normalization. If you need to care about individual values in a collection, your value is not atomic. But that’s a bit of a tangent.
Comments closedJack Vamvas takes us through a fairly recent quality of life improvement:
Question: Below is table and expected result. What is the query to achieve this result
Table : TestID LOT
7065161 4
7065212 1
7065212 4
7065203 1
7065203 2
7065203 3
Expected Result of queryID LOT
7065161 4
7065212 1_4
7065203 1_2_3
Click through to learn the easiest way to do this as of SQL Server 2017.
Comments closedDaniel Hutmacher gives us an eye test:
The developer wrote this pretty little query to show us which accounts are up for review (which in our case means they have a “30” flag).
SELECT account, balance, 'For review' AS [status]FROM #accounts WHERE account IN (SELECT account FROM #accountFlags WHERE flag=30) ORDER BY account;Did you spot it?
I did, but in fairness, I’ve been burned enough times by this that I check for it.
Comments closedServerless Synapse SQL pools in Azure Synapse Analytics have a new set of features that will enable you to analyze your Azure data more efficiently. The new Transact-SQL (T-SQL) language features that you can use in serverless SQL pools are STRING_AGG, OFFSET/FETCH, PIVOT/UNPIVOT, SESSION_CONTEXT, and CONTEXT_INFO.
Old T-SQL hands will likely know what all of this does, but click through if something looks unfamiliar. All of this is available in SQL Server 2017 and later (and everything but STRING_AGG() is available going back to 2008).
Jeffin Mathew takes us through a use case for recursive common table expressions:
An individual is working in HR and wants to find out which individual is managing who. This may be for several reasons such as, they need to ask the managers on the progress of their staff and if their appraisal is coming up or is due.
Another scenario may be that the company is enrolling more staff and wants to find out the capacity of the current staff or find individuals who have not yet got anyone to manage to give them the opportunity to do so.
Click through for the solution. Often times, we see recursive CTEs show up in hierarchical queries like this. When the number of records is small, they work really well. The issue comes with scale; that’s when a different table design becomes important.
Comments closedJohn MacKintosh gives us a primer on the COALESCE function in both SQL and R:
What does coalesce mean? In the English language, it is generally used to convey a coming together, or creating one whole body, mass or system. How does that help us when working with data? We spend a lot of time cleaning our data, surely the last thing we want to do is lump it all together?
Click through for detail on the nuances of COALESCE(). H/T R-Bloggers.