SQL Server 2022 added 353 wait types. I grouped them by family and found 339 without a public description.

A closer look at undocumented SQL Server 2022 DMVs and functions for request phases, XTP hash indexes, CDC, governance and retired internals.

I checked the 11 SQL Server 2022 configuration options added after SQL Server 2019 against sys.configurations and Microsoft Learn.

I diffed the SQL Server 2019 and 2022 system catalogs over the DAC and checked 558 new objects against Microsoft Learn. I found no documentation for 477.

INTERSECT and EXCEPT treat NULL as equal, the one place NULL comparison behaves as expected. Use them for NULL-safe row comparison.

A UNIQUE constraint allows one NULL but rejects a second, and an equi-join drops NULL keys. Filtered indexes and NULL-safe joins.

ISNULL and COALESCE differ on result type, truncation, nullability, and how often they evaluate their input. When to use each.

With +, one NULL operand makes the whole concatenation NULL and a built-up label vanishes. Use CONCAT, COALESCE, or CONCAT_WS instead.

COUNT(column), AVG, and COUNT(DISTINCT) ignore NULL but COUNT(*) does not, so totals stop matching. How to pick the right aggregate.

A CHECK constraint accepts a row when its predicate is UNKNOWN, so CHECK (quantity >= 0) still allows NULL. How to close the gap.

A WHERE inequality drops rows where the column is NULL, because the comparison is UNKNOWN. When to add OR col IS NULL, and when not to.

One NULL in a NOT IN subquery makes the whole predicate UNKNOWN and returns zero rows. Why it happens and how NOT EXISTS fixes it.

A MERGE upsert skips a NULL-to-value change and updates nothing, with no error. The three-valued-logic cause and two NULL-safe fixes.

There is a certificate in LocalMachine\My on one of your SQL Servers. Nobody knows what it is for. The subject is unhelpful, it was issued before you started, and the person who installed it has left. Can you delete it? The certificate store cannot answer that. It lists what exists, not what uses it, and there is no "last accessed" column.

An agent session ends and the context is gone. Everything it learned about your environment, every approach it tried and discarded, every decision you talked through together, and whatever it was about to do next. The usual answer is "the commits will tell you." They will not. A commit log records what changed. It does not record what was tried and rejected, why an approach was abandoned, which of your servers got touched outside version control, or what the next step was going to be.

I have been testing the rules in my own T-SQL style guide against real instances, which means reading a lot of execution plans. Partway through, I started using Erik Darling's sqlserver-query-plans plugin[1] for the reading. It changed one of the posts, and not by finding something I had missed. It stopped me reaching a conclusion I was already comfortable with.

I keep a file of T-SQL conventions. Square brackets on identifiers, COALESCE over ISNULL, block comments, NOT EXISTS over NOT IN, and a dozen more. It is not there because consistent code is prettier. It is there so I do not re-decide the same question every time it comes up. Decisions, not preferences Most style rules do not matter much in isolation.

Snapshot isolation is the answer to most of the problems WITH (NOLOCK) gets used for, which I covered in NOLOCK Returned a Balance That Never Existed. Readers stop waiting for writers, and unlike read uncommitted, everything they read was actually committed. Adopting it has two prerequisites that are easy to get wrong, and both fail in ways that point somewhere other than the cause.

WITH (NOLOCK) gets added to queries for one reason: something was slow, or something was blocked, and the hint made the query run. It does make blocking stop. What it gives up in exchange is arguably far more important. A balance that was never real Two connections against the same instance. An account table with one row, holding 100.00. Connection A opens a transaction and changes the balance, then stops without committing:

A procedure that opens its own transaction is fine on its own. Call it from another procedure that already opened one and the arithmetic stops matching your intent. SQL Server does not have nested transactions. It has a counter. The counter @@TRANCOUNT reports how many BEGIN TRANSACTION statements are outstanding on the current connection.[1] SELECT = @@TRANCOUNT; BEGIN TRANSACTION; SELECT = @@TRANCOUNT; BEGIN TRANSACTION; SELECT = @@TRANCOUNT; COMMIT TRANSACTION; SELECT = @@TRANCOUNT; ROLLBACK TRANSACTION; SELECT = @@TRANCOUNT;

COUNT() and COUNT_BIG() do the same thing: they return the total number of rows in your result set, or the number of rows-per-group with GROUP BY. Only COUNT_BIG() works when there are more than 2,147,483,647 rows in the table or group. This is the rule in my style guide I break most often, usually because COUNT(1) is what my hands type.

Optional filter parameters are everywhere in reporting procedures. Pass a value and you want that value; pass NULL and you want everything. There are two common ways to write it, and I have carried a rule for years that says use the first one: /* The OR pattern */ WHERE ( . = @office_number OR @office_number IS NULL ); /* The COALESCE pattern */ WHERE .

https://www.youtube.com/watch?v=XhwebU5rDa0 Every developer juggling AI agents right now knows the feeling of context switching until the whole workflow blurs together. Copilot suggests something in o...

-- and /* */ both comment out T-SQL. The difference is where they stop. /* */ stops where you close it. -- runs to the end of the line,[2] and if there is no end of the line, it does not stop. Building a statement in pieces Generated SQL is usually assembled from fragments. Here is the same statement built twice, once with each comment style, joined with a space rather than a line break:

NOT IN and NOT EXISTS read like the same thing. Find the rows that are not in that other set. They agree until the other set contains a NULL, at which point NOT IN returns no rows at all. Five items, two exclusions CREATE TABLE #items ( int NOT NULL ); CREATE TABLE #excluded ( int NULL ); INSERT INTO #items () VALUES (1), (2), (3), (4), (5); INSERT INTO #excluded () VALUES (2), (4);

ISNULL and COALESCE look like the same function wearing different names. Both take a value, both give you something else when that value is NULL. They differ in how they decide the type of what comes back, and that difference can shorten your data without raising anything. Five characters DECLARE @short varchar(5) = NULL; SELECT = ISNULL(@short, 'truncated-value'); result ------ trunc…

The generator from part 11 builds a restore script into a variable. Getting it back out so someone can read it before running it seems like the easy part. PRINT @script returns the beginning of it. How much of the beginning depends on the data type, and the part that doesn't arrive isn't reported as missing. The limits A message string passed to…

This series has used two sources for the same information. msdb records what the instance did. RESTORE HEADERONLY reads what's in the files. They describe the same backups and disagree on almost every name. Code that reads from both, which is most code that does anything useful here, ends up translating between them, and the translation is where mistakes get made.

The restore script generator from part 11 worked on most databases and failed on one. The full backup restored fine everywhere else; on this database the restore refused to start. The database had two FILESTREAM filegroups. The generated MOVE clauses had given them .mdf extensions, along with everything else that wasn't a log file. Not every database file is a file…

Everything so far establishes that a set of backups is coherent: complete stripes, a matching differential base, an unbroken log chain, one lineage, and a reachable target time. Writing the restore statements from that point is mechanical, which makes it a good candidate for generating rather than typing. A weekly full plus a differential plus a hundred and twenty log backups is a hundred and twenty-two statements, and the cost of a typo in any of them is finding out partway through.

Someone asks you to restore a database to 14:30 yesterday, just before a bad deployment. The answer is either a time estimate or "the closest I can get is 14:15", and which one it is depends on backups that already exist or don't. That question can be answered from the headers in seconds, without starting anything. What STOPAT needs STOPAT…

Two backup files, same database name, same server name, LSNs that line up. They can still be from different databases. Database names get reused. A database is restored under a new name, then renamed back. A refresh from production overwrites a test database, and last week's backups from the old contents are still on the share. Names and dates won't separate those; the header carries identifiers that will.

Restoring a full backup and then a hundred log backups works only if the logs form an unbroken sequence. One missing backup in the middle and the restore stops there, with the database still in RESTORING state and no way forward. Finding that out during a recovery is expensive. The sequence can be checked in advance from the headers, and it comes down to comparing two numbers per backup.

Point RESTORE HEADERONLY at a full backup and you get one row. Point it at the file a fifteen-minute log backup job has been appending to since Sunday and you get several hundred. Each row is a separate backup set inside one physical file. Restoring from that file means naming which set you want, and the column that identifies it is…

A differential backup contains every extent changed since its base full backup. Not since the last differential, and not since whichever full is newest on disk, but since one specific full backup. Restore it onto a different full and SQL Server refuses. It's better to find that out now than during a recovery, when the full you kept and the differential you kept turn out never to have been a pair.

Large databases are often backed up across several files at once. Instead of one 800 GB file, you get eight files of about 100 GB, written in parallel: BACKUP DATABASE TO DISK = N'D:\Backups\Sales_FULL_S00.bak' , DISK = N'D:\Backups\Sales_FULL_S01.bak' , DISK = N'D:\Backups\Sales_FULL_S02.bak' , DISK = N'D:\Backups\Sales_FULL_S03.bak' WITH CHECKSUM, COMPRESSION; The motivation is throughput. Several writers can saturate storage that one writer can't, and on the restore side several readers help the same way.

Everything so far assumed you already knew the path to a backup file. Automating any of it means the code has to find the files itself, and SQL Server gives you no documented way to list a directory. Start with what msdb knows Before reaching for anything exotic, msdb records the path of every backup this instance wrote:[1] SELECT [b].

A scheduled job that had run for months started failing after a server was upgraded. The message in the job history: Msg 3013, Level 16, State 1, Line 1 RESTORE HEADERONLY is terminating abnormally. Read that as a DBA and you go looking at the backup file: corruption, truncation, a share that went away, a service account that lost permission.

In part 1 I used msdb to find a database whose newest full backup had aged past a week. msdb tells you a backup happened. It doesn't tell you the file is still readable, or which database it really came from. For that you have to open the file, and RESTORE HEADERONLY is how. It reads the header of every backup set on a device and returns one row each, without restoring anything.

A maintenance window rebooted a server in the middle of its weekly full backup. The job was killed mid-write, the partial file was discarded, and the next scheduled run was seven days out. Differentials kept running each night. Log backups kept running every fifteen minutes. All of them reported success, correctly. Eight days later the newest usable full backup was eight days old, and the monitoring had reported green throughout.

I promised a follow-up to a post about tech vocabulary,[1] and this is it. That one argued that dramatic words and vague words fail the same way: they carry attitude where information should be. I ended on acronyms, said they deserved more room than a paragraph, and left them there. Here is the room. An acronym is a cache…

Note: a self-contained MCVE reproducing every result in this post is included at the end - see "The MCVE: run it yourself". Hugo Kornelis recently challenged a sentence in my post on how SQL Server stores forwarded records. The sentence claimed that a forwarded record carries a back-pointer to its stub, and that the engine uses it to move the row home if it shrinks.

A while back I read an incident report that described the "blast radius" of a "catastrophic" data issue. I kept reading, looking for the part where something exploded. The actual damage: four rows in a reporting table carried a stale value for about an hour, and one dashboard showed a wrong number until the next refresh. Nobody was blasted. There was no radius.

A while back I published the guard clauses my index rebuild script uses, including the classic advice: reorganize in the middle band of fragmentation, rebuild above it, because "reorganize only logs the pages it actually moves." In a LinkedIn discussion, Jeff Moden challenged that line, arguing that REORGANIZE generates far more transaction log than people expect, enough that it deserves to be the exception rather than the default.

The Logins, SIDs, and Kerberos series spent three posts building up to a practical conclusion: SQL Server maps database users to logins by SID, not by name, and any login you recreate from scratch gets a new SID. Part 3 showed the cure for migrations: a CREATE LOGIN statement that carries the original password hash and the original SID, generated entirely from catalog views.

The series so far has answered who can connect (part 5 showed even that requires asking Windows) and how they prove it (part 6 and part 7). The closing question is who actually does. Group-based access means the catalog cannot tell you; point-in-time audits age immediately; and when someone asks "is anything still using this login?" before a decommission, you need history, not a snapshot.

Part 6 got your connections onto Kerberos. This part asks an uncomfortable follow-up: encrypted with what? You probably have not thought about Kerberos encryption types recently. That is fair; the protocol mostly just works and has for decades. But CVE-2026-20833 changed the calculus. Microsoft disclosed a Kerberos information disclosure vulnerability in January 2026 that affects every supported version of Windows Server from 2008 SP2 through 2025.

The first five parts of this series were about who you are: SIDs, logins, groups. This one is about how you prove it. When a Windows principal connects to SQL Server with integrated authentication, one of two protocols does the proving: Kerberos or NTLM.[3] You almost never choose between them explicitly. The client and server negotiate, and when Kerberos is not possible, the connection silently falls back to NTLM: no error, no warning to the application, just a different (older, weaker, less capable) protocol.

Every post in this series so far has dealt with principals you can see: a login row in sys.server_principals with a name and a SID. Windows groups break that comfortable assumption. Grant a group a login[2] and every member of that group can connect, yet none of them appear in any SQL Server catalog view. The people connecting to your instance are, from the catalog's point of view, invisible.