Press "Enter" to skip to content

Category: T-SQL

Multiple Aggregations with CASE

Published 2022-05-03 by Kevin Feasel

Chad Callihan shows off a good use of aggregate functions and the CASE statement:

Have you have been tasked with pulling multiple counts from the same table? Maybe you need to find how many records have a value for a column and how many are NULL. Or maybe you need to see how many records are true and how many are false.

It’s simple enough to run a query to count one set of criteria, run another query for the second set of criteria, and combine them when sending your results. Did you know you can get multiple counts with one query?

Here’s an example of how using COUNT and CASE can speed up your day.

Click through for an example. I mildly disagree with Chad’s conclusion that this is something you’ll rarely do—the more you work with reporting and analytical queries, the more you’ll appreciate this.

Comments closed

Simplifying T-SQL Code

Published 2022-04-28 by Kevin Feasel

Ed Pollack shares several good T-SQL tips to simplify code:

While trying to solve the data challenges an organization throws our way, it is often easy to get buried in large, complex queries. Despite efforts to write concise, easy to read, maintainable T-SQL, sometimes the need to just get a data set quickly results in scary code that is better off avoided rather than committed.

This article dives into a handful of query patterns that can be useful when attempting to simplify messy code and can both improve query performance while improving maintainability at the same time!

Click through to learn more. I use most of those tips to great effect (though don’t often have need to hit some of the DMVs Ed mentions).

Comments closed

Splitting Strings with Quoted Names

Published 2022-04-22 by Kevin Feasel

Daniel Hutmacher mixes separators with regular characters:

Suppose you have a delimited string input that you want to split into its parts. That’s what STRING_SPLIT() does:

DECLARE @source nvarchar(max)='Canada, Cape Verde, '+    'Central African Republic, Chad, Chile, China, Colombia, Comoros';

SELECT TRIM([value]) AS[Country]
FROM STRING_SPLIT(@source, ',');

Simple enough. But delimited lists are tricky, because the delimiter could exist in the name itself. Look for yourself what happens when we add the two Congos to the list:

Daniel has a clever solution to the problem.

Comments closed

Calculating Running Totals with Window Functions

Published 2022-04-22 by Kevin Feasel

Steve Jones shows off a good use case for window functions:

Recently I was looking at some data and wanted to analyze it by month. I have a goal that is set for each day and then an actual value. I wanted to know how I was tracking against the goal, as a running total. If my goal is 10 a day, then I ought to actually get to 10 the first day, 20 for the second day (10 + 10), etc.

Read on to see how Steve solved the problem.

Comments closed

Quotation Marks in Azure SQL DB vs Managed Instance

Published 2022-04-21 by Kevin Feasel

Michael Bourgon notices a difference between Azure SQL DB and Managed Instance/box product:

I was trying to get some Xquery parsing working, and ran into a weird error:

"CONDITIONAL failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'"

Weird.  Doubly so because this code has worked on-prem for a decade, and the Managed Instance is running it now.  

Read on for the solution.

Comments closed

Code Formatting Holy Wars

Published 2022-04-14 by Kevin Feasel

Tom Zika and I are on opposite sides:

So I’ll take it one step further.
I’ll never use semicolons unless I have to.

Tools like Redgate’s SQL Prompt can add semicolons automatically, but I still won’t do it.

My quick thoughts:

  • Semi-colons? Love them. The chaotic neutral part of me wants to see Microsoft make good on their deprecation notice of code lacking semi-colons just to watch the world burn.
  • Commas go at the end because we are not barbarians.
  • Aliases should be short and sufficiently meaningful within the context of the statement. Tom and I agree here.
  • PascalCase is the best case.
  • INNER JOIN instead of JOIN because, again, we are not barbarians. LEFT OUTER JOIN instead of LEFT JOIN because, well, you guessed it.

And at the end of the day, consistency and readability are the most important things…though I’ll fight for my aesthetics like I’m the third monkey in line for Noah’s Ark and brother, it’s starting to rain.

Comments closed

The Islands T-SQL Challenge: Sequencing Islands

Published 2022-04-13 by Kevin Feasel

Itzik Ben-Gan has an interesting challenge for us:

The challenge is as follows:

Assuming partitioning based on the column grp and ordering based on the column ord, compute sequential row numbers starting with 1 within each consecutive group of rows with the same value in the val column. Following is the desired result for the given small set of sample data:

Click through for an example of this, a pair of “classic” solutions, and Itzik’s solution.

Comments closed

Using Transactions in SQL Server

Published 2022-04-13 by Kevin Feasel

Kevin Chant has good advice:

When doing inserts, updates or delete statements using T-SQL always, and I mean always, run them inside a transaction.

You can do this in different ways in SQL Server. One of the most popular ways is to start an explicit transaction by using the begin transaction command. From there you can either commit the transaction if it is good or issue a rollback command to undo the transaction.

Read on for the why. This is excellent advice.

Comments closed

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