Press "Enter" to skip to content

Category: T-SQL Tuesday

Last Observation Carried Forward in SQL Server 2022

Published 2023-11-16 by Kevin Feasel

Barney Lawrence shows off a nice enhancement to T-SQL in SQL Server 2022:

With SQL Server 2022 came a much requested additional feature added from the SQL standards – IGNORE_NULLS. You can probably guess what it does. Drop in IGNORE_NULLS after your function and you can blur the non null values over those gaps giving us results like this:

Read on for the pre-2022 version of the query and what it does, versus the version with IGNORE_NULLS specified. This small flag is extremely helpful in time series statistical analysis and I’m glad it’s in SQL Server now.

Comments closed

A Good Use of LEAD()

Published 2023-11-16 by Kevin Feasel

Chad Callihan gets the lead out:

Imagine we want to find gaps in post history for users. If a user is posting every day, we’re happy and can assume they’re happy. If they’re only posting once every few weeks, we want to investigate why that is.

Read on to see how you can do this with the LEAD() function.

Comments closed

Execution Plans for Window Functions

Published 2023-11-16 by Kevin Feasel

Hugo Kornelis digs in:

In his invitation, Steve specifically asks for examples where Window functions provided a neat solution to a real world problem. Well, sorry Steve, but I am not going to do that. But your invitation did inspire to me write about the execution plans for these window functions. And there is, in fact, so much to write about it, that this is just the first part.

So that makes this post not only a T-SQL Tuesday contribution, but also part 23 in my ongoing plansplaining series: blog posts where I take an in depth look at execution plans to explain how exactly they work, and point out often overlooked details. In this post, I will look at the basics of window functions, as they have existed for over 10 years now. I will point out a few interesting performance gotchas. And there are some links to feedback items that you can upvote (if you are so inclined) to pressure Microsoft to make some changes to the optimizer and the execution engine, to get some small but still welcome performance gains.

Read on to get an in-depth understanding of how those window functions actually operate once the database engine gets ahold of your query.

Comments closed

Operations Auditing with LAG() and LEAD()

Published 2023-11-15 by Kevin Feasel

Deborah Melkin builds a report query:

I’ve been doing a lot of work recently where I have long running processes where I need to have visibility on where we are during the process as well as being able to provide a report we can use to find out if there were any errors, how long each step took, and other useful information that we can use later. My solution was to create an audit log table where I simply added a row before and after each step, recording the information I had available. When the processes are done, I create a query to run over the table returning the values I want to see.

Deb also throws in the only ordered set function in SQL Server, STRING_AGG().

Comments closed

Using the WINDOW Operator in SQL Server 2022

Published 2023-11-15 by Kevin Feasel

Steve Jones cuts out the duplication:

I am the host for T-SQL Tuesday this month, and I hope that a lot of people like the topic. This idea actually came to me earlier this year when I happened to see someone ask about a T-SQL problem and get an answer using a Window function. This person mentioned they hadn’t used the window function before, and I wondered how many people haven’t even tried using the OVER() clause with a window function.

Read on to see how you can use WINDOW to replicate window frames, which can be quite helpful for larger queries. It doesn’t change the performance profile but does make the code more readable.

Comments closed

Good Use Cases for Window Functions

Published 2023-11-15 by Kevin Feasel

Aaron Bertrand build a list:

When I first used window functions back in SQL Server 2005, I was in awe. I had always used inefficient self-joins to calculate things like running totals, and these really didn’t scale well with size-of-data. I quickly realized you could also use them for ranks and moving averages without those cumbersome self-joins, elaborate sub-queries, or #temp tables. Those all have their place, but window functions can make them feel old-school and dirty.

I’d also recommend learning more about the APPLY operator as well, as it can, depending on the circumstances, be even more effective than window functions (combined with common table expressions) for some of the use cases.

Comments closed

The Power of LAG and LEAD

Published 2023-11-15 by Kevin Feasel

Rod Edwards shows off a great use case for LAG():

I often find myself using windows functions in order to group data in wierd and wonderful ways that a simple GROUP BY can’t do… however, the example below is one I came across quite recently on my travels, I was asked to have a look at some code for optimization opportunities. And in this case, thankfully there were some.

Read on for a lengthy cursor, followed by a much less lengthy (and much faster) window function.

Comments closed

T-SQL Tuesday 167 Roundup

Published 2023-10-18 by Kevin Feasel

Matthew McGiffen talks encryption:

Huge thanks to everyone who responded to my invitation to blog on Encryption and Data Protection for this month’s T-SQL Tuesday.

I got what I hoped for, which was a wide(ish) interpretation of the topic. Posts from those who are clearly advocates of encryption, and from those who have scepticism around the encryption approaches that some people take.

We’ve also got discussions about the importance of basic data governance, and the protection of intellectual property.

Read on for links to several posts on the topic of security.

Comments closed

Against Transparent Data Encryption in SQL Server

Published 2023-10-11 by Kevin Feasel

Andy Yun is not a fan:

Of all of the various data protection options available to us in SQL Server, I argue that Transparent Data Encryption (aka TDE) is worthless Security Theater.

TDE encrypts your data files “at rest.” This means one cannot crack open a hex editor and just start reading your raw data. And did you know that you can use a hex editor and start reading string values right out of your MDF files? Go search on that – that’s a separate tangent you can go explore.

Read on to understand the ways in which Andy finds fault with TDE.

Comments closed

An Overview of Transparent Data Encryption

Published 2023-10-11 by Kevin Feasel

Chad Callihan looks at one option for securing a SQL Server instance:

This month’s T-SQL Tuesday topic comes from Matthew McGiffen, who asks us to talk about encryption and protecting data in SQL Server. To read the full topic invite, click the T-SQL Tuesday logo to the right.

For this month’s invite, I thought I’d write about Transparent Data Encryption (TDE) and give a reminder about how it relates to tempdb.

Read on for Chad’s reminder.

Comments closed