Press "Enter" to skip to content

Category: Syntax

OFFSET and FETCH in SQL Server

Published 2021-04-22 by Kevin Feasel

Steve Jones explains how the OFFSET-FETCH process works:

The other day I saw an article on the OFFSET clause in a SELECT. I had seen this come out and looked at it briefly in SQL Server 2012, but hadn’t done much with it.

NOTE: if you use this, be sure you read about potential performance problems and solutions.

Read on for more info. This is something I really wanted to work better than it does, as paging is really awkward in SQL Server. OFFSET-FETCH is great syntactically, but doesn’t do much for performance. The best solution I’ve seen is to take the results of a paging operation and store them in a dedicated paging table, allowing the user to query by page in that small table quickly. But at that point, OFFSET-FETCH isn’t really much less complicated than TOP(NumberOfRows) WHERE NumberOfRows >= NumberOfRows * (NumberOfPages – 1) ORDER BY NumberOfRows.

Comments closed

Alternatives to GREATEST() and LEAST()

Published 2021-04-19 by Kevin Feasel

Mike Scalise has an alternative to using the GREATEST() and LEAST() functions in SQL Server:

As of 4/14/21, Microsoft has officially announced that the GREATEST and LEAST functions are in Azure SQL Database and SQL Managed Instance. Unofficially, it seems they had silently included these functions in at least (no pun intended) SQL Managed Instance several months prior. In any case, here we are today with official Microsoft documentation on GREATEST and LEAST. This is all great news. What’s also great is that, in their statement, Microsoft stated they would be including these two functions in the next version of SQL Server.

But what about all of us on SQL Server 2019 and prior? Fortunately, there’s a way to mimic these two functions in your queries using a correlated subquery in the SELECT clause.

Click through for examples. This is a bit different from getting the largest or smallest value in a window, which you can do with MIN(val) OVER () or MAX(val) OVER (). But I’m looking forward to seeing GREATEST() and LEAST() in the box product.

Comments closed

Trickiness Around CASE Expressions

Published 2021-04-09 by Kevin Feasel

Edwin Sanchez takes us through a few issues you might run into with CASE expressions:

If you are trying to scan the subheadings to see what the issues are, I can’t blame you. Readers, including me, are impatient.

I trust that you already know the basics of SQL CASE, so, I won’t bore you with long introductions. Let’s dig into a deeper understanding of what’s happening under the hood.

Click through for the list.

Comments closed

Calculating Pagination Metadata in SQL

Published 2021-03-19 by Kevin Feasel

Lukas Eder has a single query which includes pagination data:

This is rather straightforward. It will give us page 2 out of N pages, with a page size of 10. But how do we calculate these values? How do we know we’re on page 2? How do we know the number of pages N? Can we calculate this without an extra round-trip e.g. to calculate the total number of actors:

-- Yuck, a second round-trip!
SELECT COUNT(*)
FROM actor

We can do it with a single SQL query and window functions, but before I explain how to do this, please consider reading this article on why OFFSET pagination is a bad thing for your performance

If you’re still convinced OFFSET pagination is what you need, as opposed to keyset pagination, let’s look at how to calculate the above meta data with SQL.

Click through for the query, as well as Lukas’s explanation of how it works. But also heed that warning about keyset pagination, as it’s usually a lot better.

Comments closed

Avoid the MERGE Operator in T-SQL

Published 2021-03-17 by Kevin Feasel

Cyndi Johnson has a warning for us:

The bugs alone are reasons enough to avoid using it, and also to refactor any existing MERGE statements you have in your code base. Those bugs include ones that have performance implications as well as ones that could lead to incorrect results. Long story short, Microsoft refuses to fix several of them, and some of the other major issues are “by design”!

If you still feel the desire to use them, please keep reading, and I hope by the end of this blog you will be convinced that

1. Less statements does not always lead to better code or better performance.
2. MERGE is like a VAMPIRE and once it is in your codebase, it’s just a matter of time before it drains your servers of their lifeblood.

The worst part about the second point is all the sparkling.

The worst part about the first point is that I was really excited with MERGE came out in 2008, as I wanted UPSERT for SQL Server. It, uh, didn’t work out so well.

Comments closed

COUNT(*) vs COUNT(1)

Published 2021-03-17 by Kevin Feasel

David Alcock tries an experiment:

The question whether to use COUNT(*) or COUNT(1) has been doing the rounds on the internet again, the question this time is whether or not COUNT(*) forces the optimiser to scan all rows or not.

I’m going to query Sales.Orders table of the WideWorldImporters sample database and use a where clause that should take advantage of a non-clustered index on CustomerID. 

David looks at COUNT(*) versus COUNT(1). The story’s slightly different with COUNT(ColumnName) when there are NULL values afoot.

Comments closed

Approximate Count Distinct

Published 2021-03-09 by Kevin Feasel

Deepthi Goguri looks at the APPROX_COUNT_DISTINCT() function in SQL Server 2019:

In the previous post, we learned about Scalar UDF Inliningfeature. In this post, let’s focus on the Approximate QP with Approx_Count_Distinct feature introduced as a part of SQL Server 2019 Intelligent Query Processing features. This feature is useful to get the approximate count of distinct values just like the  Count distinct function to get the distinct number of records but this new feature will take less amount of CPU and memory to process the query.

Click through for more info. The algorithm itself is also pretty interesting.

Comments closed

Deprecated Features to Ignore

Published 2021-02-23 by Kevin Feasel

Aaron Bertrand begins a series:

The issue in this specific case is that they spent that time contemplating a decision based on incomplete information. Are those I/Os reads or writes? If the user has an open transaction and has merely read a lot of data, there is far less impact in rolling that transaction back than if they have changed a lot of data. So, instead of sys.sysprocesses, let’s see what the more modern DMV, sys.dm_exec_sessions, can show us about this session:

On a slightly different topic, Mala Mahadevan looks at old but not-deprecated features, some of which you shouldn’t use:

I am part of a weekly talk show we run at the TriPASS user group, called ‘Shop Talk’. Shop Talk was the brainchild of Kevin Feasel, our key user group lead..we meet on a bi weekly basis and discuss random tech topics related to sql server. Some of these are questions from our audience, and some are just ideas for discussion that one of us come up with. I am constantly amazed and grateful for how much I learn by being part of this show – from my co hosts and from the very intelligent audience we are blessed with. Last week, we discussed Brent Ozar’s blog post on ‘What SQL Server Feature Do You Wish Would Go Away?’. The recording of our discussion (this topic starts around 26:00) is here.

And if you want a quick synopsis of the last link, I have the notes from each episode of Shop Talk, including the one Mala references.

Comments closed

DEFINE TABLE in DAX Queries

Published 2021-02-03 by Kevin Feasel

Marco Russo and Alberto Ferrari takes us through the DEFINE TABLE statement in DAX:

Introduced in December 2020, the DEFINE TABLE statement lets you define a calculated table local to a query. The table is not persisted in the model, it exists only for the lifetime of the query. Apart from that, it is a calculated table in every sense of the term albeit with some limitations.

The extension of DAX with the capability to define calculated tables local to a query is needed in order to support composite models (DirectQuery for Power BI datasets and Azure Analysis Services). There are no limitations on the use of the feature, so you can take advantage of local tables in any DAX query. We refer to calculated tables defined in a query as query calculated tables, or query tables for short.

Click through for an example of how it works.

Comments closed