Press "Enter" to skip to content

Category: Syntax

Correlated Subqueries which Don’t

Daniel 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 closed

T-SQL Additions to Serverless SQL Pools

Jovan Popvic lays out some of the T-SQL syntax added to serverless SQL pools in Azure Synapse Analytics:

Serverless 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_AGGOFFSET/FETCHPIVOT/UNPIVOTSESSION_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).

Comments closed

A Use Case for Recursive CTEs

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 closed

Coalesce in SQL and R

John 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.

Comments closed

Thoughts on R’s New Pipe

John Mount has thoughts on the upcoming pipe operator in R:

There is a current active discussion on this prototype and some interesting points come up. Note the current proposal appears to disallow a |> f -> f(a), a currently popular transform.

1. This is a language feature presented as a soon-to-be-user-visible prototype, not an RFC.
2. Some are objecting to the term “pipe.”
3. Some call this sort of pipe function composition.
4. It is noticed that this sort of substitution is generally thought of as a “macro.”
5. There is a claim the proposed pipe seems to violate the beta-reduction rule of the lambda calculus: variables should be substitutable for values.

Read on for John’s take on this. I particularly appreciate his response to point number 2: other functional languages have pipes (in fact, |> is the F# pipe operator). Pipes are not unique to UNIX. John has a lot of interesting comments, so check them out.

Comments closed

First Thoughts on Amazon Babelfish

Ryan Booz shares some first thoughts on Amazon’s Babelfish offering:

The imputes for creating the tool is clear for AWS. Provide a way for customers to easily connect a SQL Server app to Aurora Postgres, saving big on licensing fees and reducing total cost of ownership. Assuming the tool is successful at some level, I’m sure it will provide a revenue boost for Amazon and some customers might (initially) feel a win. No harm, no foul on Amazon for leading the effort. Free markets, baby!

No matter how clever Babelfish is, however, I just can’t see how this is ultimately a win for SQL Server or PostgreSQL… or the developers that will ultimately need to support these “hybrid” apps.

I think Ryan makes good points and does hit upon the crux of the problem. I’d also say that there’s a secondary problem which Ryan hints at, but it is that a query may be sufficiently fast in one database variant but perform horridly in another. A classic example of this is a solution built on cursors in Oracle and then bringing that to T-SQL.

Comments closed

Aggregate Functions in SQL Server

Hugo Kornelis takes us through the concept of aggregate functions:

SQL Server currently supports three operators that can compute aggregations: Hash MatchStream Aggregate, and Window Aggregate. These operators all use the same basic principle of maintaining internal counters as rows are processed, so that the final value of those internal counters is the expected value.

Read on to see the full list, as well as how they operate.

Comments closed

JSON Basics with SQL Server

Steve Jones takes us through querying straightforward JSON data in SQL Server:

Recently I saw Jason Horner do a presentation on JSON at a user group meeting. I’ve lightly looked at JSON in some detail, and I decided to experiment with this.

All in all, I’ve been pretty happy with the syntax for JSON manipulation in T-SQL. I’m not the biggest user of JSON around, but when I’ve needed to slice or build JSON, even when I needed to build it in a certain way to emulate an old application, it has worked for me.

Comments closed

Reducing CTE Re-Scans with APPLY

Daniel Hutmacher shows another good use of the APPLY operator:

You can tell by the plan why this is an inefficient query: the SQL expression in the common table expression is executed once for every time that it’s referenced in the code.

Better living through CROSS APPLY

You could store the results of the CTE in a temp table, but where’s the fun in that? Instead, why not use the CTE once, and then return four rows for each row that the CTE spits out? That’s exactly what CROSS APPLY does.

Read the whole thing and appreciate that much more all the nice things you can do with APPLY.

Comments closed

GREATEST and LEAST in Azure SQL Database

Arun Sirpal shows off some missing functionality in SQL Server:

Being in the cloud does have many benefits, from lower administration to fast scaling but another “side effect” of operating in Azure SQL Database is the cloud first nature of changes. By this I basically mean new features always get pushed to Azure first before the classic on-premises version so some gems come to light.

Here is one for you. Have you ever wanted MySQL’s functinality to apply LEAST() and GREATEST() argument? Well, you can now, in Azure.

I can’t say that I would use this every day or anything, but I have felt the pain of not having it. There are workarounds, though nothing as convenient as syntax. Hopefully this shows up on-prem in the next version of SQL Server.

Comments closed