Aaron Bertrand 15 April 2024

Snake draft sorting in SQL Server, part 2

0

5

In my previous post, I showed how to borrow a snake draft concept from fantasy football, or a packing technique from the shipping industry, to distribute different portions of a workload to run in parallel. In the previous example, we determined a distribution order for databases based on size – though you can rank by … Read more

0

5

Aaron Bertrand 12 April 2024

Snake draft sorting in SQL Server, part 1

0

127

I recently had a restore job where I needed to split the work up into multiple parallel processes (which I’ll refer to here as “threads”). I wanted to balance the work so that the duration was something significantly less than the sum of the restore times. Imagine a job that loops through and restores each … Read more

0

127

Aaron Bertrand 25 March 2024

Pagination and ordering by large values

0

4

T-SQL Programming

Pagination is a technique for limiting output. Think of Google search results, shopping the electronics category on Amazon, or browsing tagged questions on Stack Overflow. Nobody could consume all of the results in a single shot, and no site wants to spend the resources required to present them all to us, so we are offered … Read more

0

4

T-SQL Programming

Aaron Bertrand 11 March 2024

Get the most out of SQL Server Agent logs

0

12

Database Administration

If you haven’t migrated your workloads to a managed database platform yet, you’re probably still relying on SQL Server Agent for various maintenance and other scheduled tasks. Most of the time, these processes just work. But when it’s time to troubleshoot, it can be cumbersome to get to the root of some problems. In this … Read more

0

12

Database Administration

Aaron Bertrand 26 February 2024

Upgrade SQL Server on your workstation

0

2

Database Administration

Lately, I’ve seen many people struggling to upgrade their workstation to the latest version of SQL Server. The main source of the problem is usually the web installer/wrapper, which can fail for a variety of reasons that aren’t always made clear by the error messages. Today, I’m going to walk through one way you can … Read more

0

2

Database Administration

Aaron Bertrand 12 February 2024

Leave it better than you found it

0

11

Database Administration

This is for the folks who still have to log into remote machines and do work manually on the box. Yes, we still exist, and we will for as long as we’re still using physical servers in data centers and even IaaS. Not everyone has transitioned to server core and full-on PowerShell remoting for everything. … Read more

0

11

Database Administration

Aaron Bertrand 08 January 2024

Optimized locking in Azure SQL Database

0

20

Database Administration

I don’t think I’ve ever had a great impression of Azure SQL Database as a place for production applications. In its early days, it was missing several important features (like data compression). While that hole has been plugged, there are still other limitations and differences you should be aware of, like T-SQL differences, lack of … Read more

0

20

Database Administration

Aaron Bertrand 20 December 2023

Counting more efficiently

0

14

T-SQL Programming

Nearly a decade ago, I wrote a post called “Bad habits : Counting rows the hard way.” In that post, I talked about how we can use SQL Server’s metadata to instantly retrieve the row count for a table. Typically, people do the following, which has to read the entire table or index: To largely … Read more

0

14

T-SQL Programming

Aaron Bertrand 27 November 2023

When an update doesn’t update

0

7

Tell me if you’ve heard this one before: I changed data from my application, but when I checked the database, I couldn’t see the change! I’ve seen this. Loads. It can be quite perplexing for folks because they expect to see an error message if the insert, update, or delete failed. I put this post … Read more

0

7

Aaron Bertrand 20 November 2023

Strategies for queries against bit columns

0

2

T-SQL Programming

Recently someone posted a question where they couldn’t quite figure out how to construct a predicate based on a bit parameter. They tried to write a procedure like this, which wouldn’t parse, of course: I explained that you can’t have control-of-flow inside a SQL statement like that, at least not in T-SQL. And that the … Read more

0

2

T-SQL Programming

Aaron Bertrand 06 November 2023

Moving SQL Server To The Cloud: Modernizing Stack Overflow for Teams

0

9

Database Administration

Earlier this year, we migrated the entire Stack Overflow for Teams platform to Azure. This was a lengthy endeavour and Wouter de Kort, one of our core engineers, wrote about multiple technical aspects of the project in these posts: Journey to the cloud part I: Migrating Stack Overflow Teams to Azure Journey to the cloud … Read more

0

9

Database Administration

Aaron Bertrand 16 October 2023

Don’t use DISTINCT as a “join-fixer”

0

64

T-SQL Programming

I’ve quietly resolved performance issues by re-writing slow queries to avoid DISTINCT. Often, the DISTINCT is there only to serve as a “join-fixer,” and I can explain what that means using an example. Let’s say we have the following grossly simplified schema, representing customers, products, and product categories: And then we have tables for orders … Read more

0

64

T-SQL Programming

Aaron Bertrand 02 October 2023

Normalize strings to optimize space and searches

0

13

While this article is specifically geared to SQL Server, the concepts apply to any relational database platform. The Stack Exchange network logs a lot of web traffic – even compressed, we average well over a terabyte per month. And that is just a summarized cross-section of our overall raw log data, which we load into … Read more

0

13

Aaron Bertrand 14 August 2023

Working around schema drift in SQL Server

0

4

T-SQL Programming

At Stack Overflow, our environment has multiple implementations of a largely – but not 100% – identical schema. (By “schema,” I mostly mean the set of tables within a database.) I sometimes need to retrieve or update data across a large number of sites. Let’s say, pulling or removing information from the Users table, and … Read more

0

4

T-SQL Programming

Aaron Bertrand 15 May 2023

Changing log growth strategy in SQL Server 2022

0

6

When I first saw a bullet item stating SQL Server 2022 would support instant file initialization for log file growth, I was excited. When I later learned it only applies to automatic growths, and only those of 64 MB or less, I was a little less excited. With those limitations, I was skeptical this enhancement … Read more

0

6

Aaron Bertrand 20 April 2023

One use case for NOT using schema prefixes

0

6

T-SQL Programming

I’ve long been a huge advocate for always referencing objects with a schema prefix in SQL Server.… Read more

0

6

T-SQL Programming

Aaron Bertrand 30 March 2023

GENERATE_SERIES: My new go-to to build sets

0

4

T-SQL Programming

I have come across a lot of use cases for manufacturing rows on the fly, aside from the common goal of populating a large data set such as a numbers or calendar table. A few favorites include building sample data, pivoting an unknown number of columns, data extrapolation, and filling gaps in date or time … Read more

0

4

T-SQL Programming

Aaron Bertrand 10 March 2023

Build a Better WordPress Archives Page Using MySQL

0

2 Uncategorized

I recently wrote about a way to generate visual, interactive calendars in HTML by combining a calendar table with data from an events table (part 1 and part 2). This technique could be used to overlay any type of data onto calendars, but my specific goal was to make the monthly archives page on my … Read more

0

2 Uncategorized

Aaron Bertrand 23 February 2023

Find 40 Problems in this SQL Server Stored Procedure

0

35

T-SQL Programming

I’ve been at this for a while now, and have a very particular set of rules and coding conventions that I follow when writing and, more importantly, reviewing T-SQL code. I often perform code reviews and thought it would be fun to frame this exercise: a completely fictitious stored procedure hits my desk, I’ll reject … Read more

0

35

T-SQL Programming