Aaron Bertrand 27 November 2023

When an update doesn’t update

0

3

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

3

Aaron Bertrand 20 November 2023

Strategies for queries against bit columns

0

1

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

1

T-SQL Programming

Aaron Bertrand 06 November 2023

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

0

8

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

8

Database Administration

Aaron Bertrand 16 October 2023

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

0

62

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

62

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