Press "Enter" to skip to content

Category: T-SQL

Saving Space with 6NF in SQL Server

Published 2022-04-11 by Kevin Feasel

Aaron Bertrand has a two-parter. Part one sets up the problem:

We often build logging or other insert-only tables where we store large strings like URLs, host names, or error messages. It’s usually not until the table has terabytes of data that we realize there might have been a better way. If we are logging traffic or exceptions for our own application, it’s likely that we record the same URL, host name, or error message on millions of rows. What if we only had to write that URL or host name or message text once, the first time we saw it? In this tip, I want to share one idea for abstracting away recurring values, reducing storage, and making search queries faster (especially those with wildcards) without requiring immediate changes in the application layer.

Part two maximizes the savings:

In my previous tip, I showed how we can make a growing logging table leaner by moving large, repeating strings to their own dimension tables. The solution there involved an AFTER INSERT trigger and assumed that we could change the applications to recognize the new table structure in relatively short order.

Check out both posts for more details. If you’re confused about my calling this 6NF and Aaron mentioning dimension tables, the answer is that he’s talking about the end result and I’m describing the process.

Comments closed

KEEP PLAN: A Not-So-Useful Query Hint

Published 2022-04-11 by Kevin Feasel

Tom Zika digs into a query hint:

Like Dwarves of Moria, I delved too greedily and too deep. I found the answer in a Plan Caching and Recompilation in SQL Server 2012 whitepaper before trying it out on my own. Relevant excerpt:

KEEP PLAN

The KEEP PLAN query hint changes the recompilation thresholds for temporary tables, and makes them identical to those for permanent tables. Therefore, if changes to temporary tables are causing many recompilations, this query hint can be used.

But since it doesn’t show the proof, I decided to test it out anyway.

And it’s a good thing, too, as it turns out this isn’t quite how things work anymore.

Comments closed

PRECEDING and FOLLOWING in Window Functions

Published 2022-04-04 by Kevin Feasel

Reitse Eskens looks at a couple of useful window operators:

I was teaching a class today on more advanced SQL queries and we were discussing if you could use preceding and following in a single windowing function.

Windowing Functions

If you’re not familiar with windowing functions, they’re really cool. You can perform all kinds of trickery and magic with them. Let’s start with a simple example.
The dataset I’m using has flight data from the early 2000’s.

Read on for the example and for the answer to Reitse’s question.

Comments closed

Synonyms in SQL Server

Published 2022-04-04 by Kevin Feasel

Chad Callihan looks at synonyms in SQL Server:

Are you familiar with synonyms as they relate to SQL Server? I haven’t seen them used too much out in the wild but understand they can have benefits. Let’s take a look at what synonyms are in SQL Server and some common reasons for implementing them.

My recollection is that synonyms have some limitations which make them not as useful as they’d first appear. But the bigger reason I think we don’t see synonyms used very often is that they obscure information and make it tougher to understand what’s really happening. In that respect, it’s a bit similar to a trigger: useful but sometimes painful to debug because it obscures relevant information.

Comments closed

Performance Gains with LAG and LEAD

Published 2022-04-01 by Kevin Feasel

Ronen Ariely provides a solution:

However, the answer in this specific case was not optimal. Unfortunately in most cases in the forums, most people that come to ask a question, do not care about learning but only about the solution, even so in my opinion the road is just as important as the end point. The road (the learning) is what will help the person to solve the next issue and not just the current one – teach a man to fish and you feed him for a lifetime…

The op marked the answer he got and I assume that from his point of view the discussion ended, but I wanted to present the solution which might be tens time better in some cases, which is what I will do in this post…. so let’s start

I won’t dive too deeply into Ronen’s philosophical argument—you can definitely read about that in the post. I will say I am sympathetic to the argument at the margin and believe it’s worthwhile to know the superior solution.

Comments closed

Grouping Sets and Groupings

Published 2022-03-30 by Kevin Feasel

Kevin Wilkie has fun with grouping sets:

Let’s look at our dbo.Person1 table that we worked with earlier. Today, I want to find a count of all of the persons in each of the following categories: ZipCode, Gender, and Email Domains. And just for fun, let’s add in there where each of those categories cross – for example, Zipcode and Gender, ZipCode and Email Domain, etc…

Most people would think all kinds of awful thoughts at this point about all of the GROUP BY statements you’ll have to write. For anyone wondering – this is one way to do it. Notice all kinds of UNION statements and I’m sure someone is wondering if that’s truly all of the combinations. And we don’t want to go into the maintenance on this if things do happen to change…

And don’t forget about the GROUPING() function:

Let’s say our business partner asks us to determine which fields are aggregated together. Since we only have 2 fields and a grand total of 15 rows, we could determine this by eye. But, like all good developers, we want to do this programmatically.

Here’s where our friend – the GROUPING() function – comes into play.

GROUPING SETS is an extremely useful operator in the ANSI SQL standard. Definitely worth learning how to use.

Comments closed

Dealing with Shift Times

Published 2022-03-17 by Kevin Feasel

Kenneth Fisher knows what time it is:

One of the more interesting jobs I’ve had over the years was for a company that created emergency room software. It was pretty cool software and I learned a lot, both about writing queries in SQL Server and about how a software company can be run. One of the more interesting things in the various reports we created was the concept of shift calculations. In other words, what happened during a given shift.

I’ve had to do something similar (though it was for nurse scheduling rather than emergency rooms). Things get really tricky when you start dealing with 12-hour and 16-hour shifts, tracking overtime, and the like.

Comments closed

The Power of QUOTENAME

Published 2022-03-16 by Kevin Feasel

Kevin Wilkie unlocks the power of QUOTENAME():

When I first heard about QUOTENAME, I was like “This is rather useless. It just puts brackets around whatever. I can do it just as easily hard-coding the strings.”

Truly, I’m not completely wrong, but it’s a heck of a lot more fun to knock things out with the QUOTENAME function!

But there’s more that you can do with this function, as Kevin notes.

Comments closed

Matching Supply and Demand

Published 2022-03-09 by Kevin Feasel

Itzik Ben-Gan continues looking at interesting solutions to a tricky problem:

Last month I covered solutions based on a revised interval intersections approach compared to the classic one. The fastest of those solutions combined ideas from Kamil, Luca, and Daniel. It unified two queries with disjoint sargable predicates. It took the solution 1.34 seconds to complete against a 400K-row input. That’s not too shabby considering the solution based on the classic interval intersections approach took 931 seconds to complete against the same input. Also recall Joe came up with a brilliant solution that relies on the classic interval intersection approach but optimizes the matching logic by bucketizing intervals based on the largest interval length. With the same 400K-row input, it took Joe’s solution 0.9 seconds to complete. The tricky part about this solution is its performance degrades as the largest interval length increases.

This month I explore fascinating solutions that are faster than the Kamil/Luca/Daniel Revised Intersections solution and are neutral to interval length. The solutions in this article were created by Brian Walker, Peter Larsson, Paul White, and me.

These are some of the best solutions but the whole series has been quite interesting.

Comments closed

The Power of PIVOT and GROUPING SETS

Published 2022-03-08 by Kevin Feasel

Aaron Bertrand builds a report:

Without comprehensive reporting tools (or Excel), it can be cumbersome and frustrating to produce perfect report output from SQL Server SELECT statement or stored procedures. In modern versions, we have access to T-SQL functionality that far exceeds old-school ROLLUP and CUBE, like PIVOT, UNPIVOT, and GROUPING SETS. Let’s look at how to produce output we can easily plug into a simple front end and produce great-looking reports.

GROUPING SETS is one of my favorite under-utilized operators.

Comments closed