A Fine Slice Of SQL Server
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.
Leave a CommentLukas 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!SELECTCOUNT(*)FROMactorWe 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
OFFSETpagination is a bad thing for your performanceIf you’re still convinced
OFFSETpagination 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 closedCyndi 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.
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.
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 closedErik Darling shows why you might not want to set ANSI_DEFAULTS ON:
Rather than just setting the required options, which was apparently a lot of typing, someone had just set all the ANSI defaults on.
Read on to learn what the problem is and an alternative to using ANSI_DEFAULTS.
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 closedMarco 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 closedGrant Fritchey strongly recommends against SELECT *:
Quite a few years ago, I wrote a post about SELECT * and performance. That post had a bit of a click-bait title (freely admitted). I wrote the post because there was a really bad checklist of performance tips making the rounds (pretty sure it’s still making the rounds). The checklist recommended a whole bunch of silly stuff. One silly thing it recommended was to simply substitute ALL columns (let me emphasize that again, name each and every column) instead of SELECT * because “it was faster”.
My post, linked above, showed that this statement was nonsense. Let’s be clear, I’m not a fan of SELECT *. Yes, it has some legitimate functionality. However, by and large, using SELECT * causes performance problems.
I’ll use SELECT * for one-off queries (well, something like SELECT TOP(100) * but same difference), but it’s a really bad practice to include that in application code for the reasons Grant mentions.
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.