Press "Enter" to skip to content

Category: T-SQL

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

Unpivoting Data via CROSS APPLY

Published 2023-11-09 by Kevin Feasel

Aaron Bertrand’s speaking my language:

I’ve written about UNPIVOT before – see Use SQL Server’s UNPIVOT operator to help normalize output and Use SQL Server’s UNPIVOT operator to dynamically normalize output. It’s a powerful language feature that lets you flip results sideways, sort of like the opposite of PIVOT. This is great if you have columns like Phone1 and Phone2 but want to collapse them into one column. One of the challenges is that if you have other columns you also want to collapse similarly, like Email1 and Email2, you need to add an additional UNPIVOT operator or use a different approach. Is there a way to do this in a less complicated way?

Click through for the answer. And a free Curated SQL protip: if you ever want linked here, write an article about a good use case for the APPLY operator. I’m a sucker for those.

Comments closed

Getting View Definitions

Published 2023-11-08 by Kevin Feasel

Chad Callihan finds the DDL for a view:

I recently faced a situation where I had to track down the definition of a particular SQL view for various databases. I didn’t want to click through each database in SSMS to gather the information. I thought I would write a query that I could use to save some clicks and gather what I needed for each database.

The first part of that task, querying for the view definition, may be a bit tricky. I would venture to guess it may not be in the first couple of places you would think to look. Let’s walk through how we can use a query to retrieve the definition of a view.

Just make sure that you have newline retention on or else your view definition is all going on one line.

Comments closed

Building Connect 4 with T-SQL

Published 2023-11-06 by Kevin Feasel

Tomaz Kastrun figures out a useful way to play a game while looking like you’re doing important work:

Connect 4 is a classical board game, consisting of a board of 6 rows and 7 columns and 42 tokens (of two colours, each player with 21). The first player to get four tokens in a row, column or diagonally, wins!

Simple game rules:
1. only one token can be added at the time
2. each player have their own colour (sign) of tokens
3. the game is ended, when the first player connects four tokens in a row, column or diagonally

Actually, I take that back: Tomaz has figured out a useful way for two people to play a game while looking like they’re doing important work. That’s twice as good.

Comments closed

Adding a Foreign Key while Creating a Table

Published 2023-10-31 by Kevin Feasel

Steve Jones points out one of the changes to T-SQL I really like:

This assumes I’ve added a table called dbo.Order with a PK of OrderID.

However, I can do this in the CREATE TABLE statement, like shown below. I add a new section after a column with the CONSTRAINT keyword. Then I name the constraint, which is always a good practice. I can then add the FK keyword, the column and the references that connects this child column to the parent column.

This came about in SQL Server 2014, along with In-Memory OLTP and the ability to create indexes inline with the table create script. It’s a minor quality of life thing but I do enjoy it.

Comments closed

Trying out Batch Mode on Rowstore

Published 2023-10-27 by Kevin Feasel

Etienne Lopes has some fun with a feature:

Before 2012, creating analytical queries (that usually scan many rows and have lots of aggregations) from big OLTP databases to feed real-time based reports used in decision making processes, could be quite challenging. Then ColumnStore Indexes arrived and they’ve been enhanced overtime, offering amazing gains both in performance and storage.

Unfortunately, regarding pure OLTP databases, there are many situations in which ColumnStore Indexes can’t (or won’t) be used. There are some great performance enhancements present in columnstore that’s for sure and today I’m going to speak about one that became automatically available since SQL Server 2019 for “traditional” RowStore tables. It’s called “Batch Mode on Rowstore” and it can really boost some of our analytical queries over the “traditional tables” without any effort from our side!

There are a series of specific rules you need to hit but if you hit them, I’ve noticed about a 3x performance gain with you doing nothing at all.

Comments closed

Packed/Batched Remote Procedure Calls in SQL Server

Published 2023-10-27 by Kevin Feasel

Bob Dorr digs in:

The SQL Server TDS protocol provides two main paths for query execution (Language and RPC events.)  You can trace these events using the Batch::Starting/Completed (Language) and RPC:Starting/Completed (RPC) XEvents.

Language events are text streams requiring full parsing and may be susceptible to injection attacks.  Language events also require educated guesses.  For example, should select 1 return a smallint, bigint?

Bob goes on to show an example of a simple call being susceptible to SQL injection, explains why Remote Procedure Calls (RPCs) are superior, and what packing (or batching) RPCs does for you.

Comments closed

CAST() and CONVERT() for Dates

Published 2023-10-25 by Kevin Feasel

Chad Callihan converts a date:

CAST and CONVERT can both be used to switch a value to a new data type. They are similar, but certainly not identical. While CAST is considered ANSI SQL and will get you across the finish line, CONVERT can give you more flexibility when it comes to formatting date values. Let’s look at an example comparing the usage of CAST and CONVERT with dates.

Most of the time, I’ll use CAST() over CONVERT(), not so much because the former is ANSI compliant, but rather because I think it’s more intuitive to remember. Date formatting is one of the few occasions in which I usually prefer CONVERT() and that’s precisely because of the format options. Of course, if you want more custom formatting options, you can use FORMAT(), though that function uses .NET in the background and is remarkably slow. It’s fine if you’re formatting a few dates, but if you’re outputting millions of rows, you will certainly see a marked difference.

Comments closed