Press "Enter" to skip to content

Category: T-SQL

Static and Dynamic Bulk Insert into SQL Server

Published 2025-10-09 by Kevin Feasel

Rick Dobson inserts some data:

There are numerous use cases for multi-file imports of CSV files into a SQL Server table:

  • Dynamic SQL Server bulk insert loads are especially appropriate for tasks that extract content from multiple files to a SQL Server table where the source file names change between successive import jobs.
  • Static bulk insert loads target scenarios where the source file names do not change between successive import jobs.

Read on for examples of how to implement each. Admittedly, bulk insert has rarely worked all that well in my experience, whether due to permissions mishaps, poor data integrity, or sudden changes in data types between file runs. But it does tend to work a lot better if you have a specified data interchange format and a standardized process to prepare the data and make it available on disk for insertion.

Leave a Comment

The Intricacies of COUNT()

Published 2025-10-09 by Kevin Feasel

Louis Davidson can easily get to 20:

I was reading LinkedIn posts the other day when I saw this blog about what was apparently an interview question about some forms of a COUNT aggregate function

This was apparently asked in an interview. What will each of these constructs do in a SQL statement:

COUNT(*) = ?
COUNT(1) = ?
COUNT(-1) = ?
COUNT(column) = ?
COUNT(NULL) = ?
COUNT() = ?

There’s one tricky bit in this set. Louis then takes it a bit further with CASE expressions and variables, so check out the post for the answers as well as those additional examples in T-SQL.

Leave a Comment

Performance of User-Defined Functions in Fabric Warehouses

Published 2025-10-03 by Kevin Feasel

Jared Westover shares some findings:

In Part One, we saw that simple scalar user-defined functions (UDFs) perform as well as inline code in a Fabric warehouse. But with a more complex UDF, does performance change? If it drops, is the code-reuse convenience worth the price?

I’m surprised that the performance profile was so good. I had assumed it would perform like T-SQL user-defined functions—namely, worse in general.

Leave a Comment

Row Expansion in T-SQL

Published 2025-10-02 by Kevin Feasel

Louis Davidson solves a problem:

On LinkedIn a few days ago, there was a question that I found interesting about what was purported to be an interview question. The gist was “say you have a set that looks like this:

OrderId Item Quantity
------- ---- ---------
O1      A1   5
O2      A2   1
O3      A3   3

and you need to expand it to be one row based on the value in Quantity

Admittedly, this kind of problem is fairly uncommon in the business world, though this is exactly the sort of thing that a tally table can solve, and that’s what Louis uses to solve the problem. Louis also gets brownie points for praising CROSS APPLY along with tally tables.

Leave a Comment

Use Cases for Window Functions

Published 2025-10-01 by Kevin Feasel

I have a new video:

In this video, I take you through a variety of use cases for window functions, showing how you can solve common (and sometimes uncommon) business problems efficiently and effectively.

This video builds off of the prior two videos. Those prior two videos showed what the different window functions are and how they work. This one focuses primarily on solving business problems in sometimes-clever ways.

Leave a Comment

CONTINUE and BREAK in T-SQL WHILE Loops

Published 2025-09-30 by Kevin Feasel

Erik Darling tries to count and gets kind of far along. The key part of the video is the use of CONTINUE and BREAK as control logic in WHILE loops. CONTINUE is something I’ve very rarely used, though it’s pretty useful. BREAK, however, is something I’m quite familiar with, especially in conjunction with record counts. This way, you can perform small updates (e.g., update 1000 records) with a filter that tells you which records still need updated, and when @@ROWCOUNT is below 1000, you know you’re done with the updates.

Leave a Comment

Time Zone Conversions in SQL Server

Published 2025-09-25 by Kevin Feasel

Louis Davidson knows what time it is:

Recently a topic came up at work, and I remember seeing it on Reddit, so you know, that definitely is a sign that I had to write a blog on it.

For a lot of people, we typically standardize on a single time zone for our life, and in turn our data. Maybe it is the time zone of our headquarters, our house, and sometimes it is the standard time zone UTC. It is often suggested that every organization should use UTC, and that is a great suggestion for many things.

However…if you store all your times as UTC, this becomes a nightmare for some kinds of reporting.

Click through for a primer on the capabilities of the DATETIMEOFFSET data type and some of the functionality you can use with it. And Louis hits on one important note around daylight savings time changes: the date changes over the years. Another is that what’s in the Windows or Linux time zone database isn’t always historically accurate. For example, in 1974 and 1975, the US extended DST and began it in January and Feburary, respectively. But if you use Louis’s query, it claims DST started in April, which would have followed the 1966-1973 and 1976-2006 patterns but is historically inaccurate. You’ll find these sorts of things for a variety of countries because Daylight Savings Time adherence is kind of wild.

Leave a Comment

More Types of Window Functions in SQL Server

Published 2025-09-24 by Kevin Feasel

I continue a series on window functions:

In this video, I continue a dive into each category of window function, quickly reviewing the four categories of window function (plus ordered set functions). Then, I cover offset window functions, statistical window functions, and ordered set functions.

This video includes some of the window functions I use most often (LAG(), LEAD()), some of the window functions I use least often, and even a few ordered set functions to boot. Combined, it’s about 45 minutes of content between this video and the prior.

Leave a Comment

Grouping Options in T-SQL

Published 2025-09-24 by Kevin Feasel

Chad Callihan rolls up the data:

When learning T-SQL, I’d wager that learning GROUP BY comes up early in the process. What may not be mentioned are the variations that can be added to a GROUP BY clause. Are you familiar with GROUP BY GROUPING SETS, GROUP BY ROLLUP, and GROUP BY CUBE? If you’ve never seen these used, or if you have and want a refresher, read on as we look at an example of each.

Of the three, CUBE is the one that I’ve used the least. I’ve found good instances where ROLLUP gives me exactly what I want for reporting purposes, and GROUPING SETS is powerful enough that I’ve made use of it a fair number of times. But CUBE just returns back too many combinations for what I’ve needed.

Leave a Comment