Sign in

Hannah Vernon 🇨🇦

@hannahvernon.com
1.1K followers 715 following 430 posts
PostsRepliesMedia
Hannah Vernon 🇨🇦 @hannahvernon.com · 6h
Undocumented Wait Types Added in SQL Server 2022 SQL Server 2022 added 353 wait types. I grouped them by family and found 339 without a public description.
sqlserverscience.com
Undocumented Wait Types Added in SQL Server 2022
SQL Server 2022 added 353 wait types. I grouped them by family and found 339 without a public description.
000
Hannah Vernon 🇨🇦 @hannahvernon.com · 29/09/2026
Undocumented DMVs Added in SQL Server 2022 A closer look at undocumented SQL Server 2022 DMVs and functions for request phases, XTP hash indexes, CDC, governance and retired internals.
sqlserverscience.com
Undocumented DMVs Added in SQL Server 2022
A closer look at undocumented SQL Server 2022 DMVs and functions for request phases, XTP hash indexes, CDC, governance and retired internals.
000
Hannah Vernon 🇨🇦 @hannahvernon.com · 28/09/2026
The 11 Configuration Options Added in SQL Server 2022, and Their Documentation I checked the 11 SQL Server 2022 configuration options added after SQL Server 2019 against sys.configurations and Microsoft Learn.
sqlserverscience.com
The 11 Configuration Options Added in SQL Server 2022, and Their Documentation
I checked the 11 SQL Server 2022 configuration options added after SQL Server 2019 against sys.configurations and Microsoft Learn.
110
Hannah Vernon 🇨🇦 @hannahvernon.com · 27/09/2026
I Could Not Find Documentation for Most of What SQL Server 2022 Added 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.
sqlserverscience.com
I Could Not Find Documentation for Most of What SQL Server 2022 Added
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.
120
Hannah Vernon 🇨🇦 @hannahvernon.com · 26/09/2026
INTERSECT and EXCEPT: Where NULL Finally Behaves INTERSECT and EXCEPT treat NULL as equal, the one place NULL comparison behaves as expected. Use them for NULL-safe row comparison.
sqlserverscience.com
INTERSECT and EXCEPT: Where NULL Finally Behaves
INTERSECT and EXCEPT treat NULL as equal, the one place NULL comparison behaves as expected. Use them for NULL-safe row comparison.
010
Hannah Vernon 🇨🇦 @hannahvernon.com · 25/09/2026
One NULL in a Unique Key, and None Across a Join A UNIQUE constraint allows one NULL but rejects a second, and an equi-join drops NULL keys. Filtered indexes and NULL-safe joins.
sqlserverscience.com
One NULL in a Unique Key, and None Across a Join
A UNIQUE constraint allows one NULL but rejects a second, and an equi-join drops NULL keys. Filtered indexes and NULL-safe joins.
010
Hannah Vernon 🇨🇦 @hannahvernon.com · 24/09/2026
ISNULL vs COALESCE: Not as Interchangeable as They Look ISNULL and COALESCE differ on result type, truncation, nullability, and how often they evaluate their input. When to use each.
sqlserverscience.com
ISNULL vs COALESCE: Not as Interchangeable as They Look
ISNULL and COALESCE differ on result type, truncation, nullability, and how often they evaluate their input. When to use each.
010
Hannah Vernon 🇨🇦 @hannahvernon.com · 23/09/2026
One NULL Turns the Whole Concatenation Into NULL With +, one NULL operand makes the whole concatenation NULL and a built-up label vanishes. Use CONCAT, COALESCE, or CONCAT_WS instead.
sqlserverscience.com
One NULL Turns the Whole Concatenation Into NULL
With +, one NULL operand makes the whole concatenation NULL and a built-up label vanishes. Use CONCAT, COALESCE, or CONCAT_WS instead.
020
Hannah Vernon 🇨🇦 @hannahvernon.com · 22/09/2026
COUNT, AVG, and the NULLs Your Aggregates Ignore COUNT(column), AVG, and COUNT(DISTINCT) ignore NULL but COUNT(*) does not, so totals stop matching. How to pick the right aggregate.
sqlserverscience.com
COUNT, AVG, and the NULLs Your Aggregates Ignore
COUNT(column), AVG, and COUNT(DISTINCT) ignore NULL but COUNT(*) does not, so totals stop matching. How to pick the right aggregate.
010
Hannah Vernon 🇨🇦 @hannahvernon.com · 21/09/2026
The CHECK Constraint That Lets NULL Through A CHECK constraint accepts a row when its predicate is UNKNOWN, so CHECK (quantity >= 0) still allows NULL. How to close the gap.
sqlserverscience.com
The CHECK Constraint That Lets NULL Through
A CHECK constraint accepts a row when its predicate is UNKNOWN, so CHECK (quantity >= 0) still allows NULL. How to close the gap.
022
Hannah Vernon 🇨🇦 @hannahvernon.com · 20/09/2026
The Inequality Filter That Drops Your NULL Rows 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.
sqlserverscience.com
The Inequality Filter That Drops Your NULL Rows
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.
000
Hannah Vernon 🇨🇦 @hannahvernon.com · 19/09/2026
NOT IN With a NULL Returns Nothing 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.
sqlserverscience.com
NOT IN With a NULL Returns Nothing
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.
011
Reposted by Hannah Vernon 🇨🇦
Dave @expletivedeleted.bsky.social · 18/09/2026
Dimwitted Trumpanzee scum will dismiss this as entirely justified and not at all fascistic.
011
Hannah Vernon 🇨🇦 @hannahvernon.com · 18/09/2026
The MERGE That Would Not Update: A NULL in Your Change Detection 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.
sqlserverscience.com
The MERGE That Would Not Update: A NULL in Your Change Detection
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.
021
Hannah Vernon 🇨🇦 @hannahvernon.com · 17/09/2026
Can You Delete That Certificate? The Store Will Not Tell You. 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…
sqlserverscience.com
Can You Delete That Certificate? The Store Will Not Tell You.
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.
020
Hannah Vernon 🇨🇦 @hannahvernon.com · 16/09/2026
Your Agent Forgets Everything. I Measured What I Forgot to Write Down. 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…
sqlserverscience.com
Your Agent Forgets Everything. I Measured What I Forgot to Write Down.
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.
000
Hannah Vernon 🇨🇦 @hannahvernon.com · 15/09/2026
A Plan Analysis Skill That Refuses to Let You Cite Cost 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…
sqlserverscience.com
A Plan Analysis Skill That Refuses to Let You Cite Cost
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.
021
Hannah Vernon 🇨🇦 @hannahvernon.com · 14/09/2026
Why I Keep a T-SQL Style Guide, and What Testing It Cost Me 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…
sqlserverscience.com
Why I Keep a T-SQL Style Guide, and What Testing It Cost Me
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.
000
Hannah Vernon 🇨🇦 @hannahvernon.com · 13/09/2026
The SET Succeeds and the Catalog Read Succeeds, Then Msg 3951 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…
sqlserverscience.com
The SET Succeeds and the Catalog Read Succeeds, Then Msg 3951
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.
000
Hannah Vernon 🇨🇦 @hannahvernon.com · 12/09/2026
NOLOCK Returned a Balance That Never Existed 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…
sqlserverscience.com
NOLOCK Returned a Balance That Never Existed
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:
010
Hannah Vernon 🇨🇦 @hannahvernon.com · 11/09/2026
A Nested COMMIT Does Not Commit Anything 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…
sqlserverscience.com
A Nested COMMIT Does Not Commit Anything
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;
010
Hannah Vernon 🇨🇦 @hannahvernon.com · 10/09/2026
COUNT Under the Hood 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…
sqlserverscience.com
COUNT Under the Hood
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.
020
Hannah Vernon 🇨🇦 @hannahvernon.com · 09/09/2026
COALESCE in a WHERE Clause Costs You the Row Estimate 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:…
sqlserverscience.com
COALESCE in a WHERE Clause Costs You the Row Estimate
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 .
000
Reposted by Hannah Vernon 🇨🇦
asura @asura.dev · 08/09/2026
I will never take you seriously if you feature Logitech knocking off a Streamdeck and calling it an AI coding tool The shark is jumped
share.google
The $100 Keypad That Puts Each AI Coding Tool Behind Its Own Button - Yanko Design
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...
1304
Hannah Vernon 🇨🇦 @hannahvernon.com · 08/09/2026
Double-Hyphen Comments Can Comment Out Your WHERE Clause -- 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…
sqlserverscience.com
Double-Hyphen Comments Can Comment Out Your WHERE Clause
-- 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:
020
Hannah Vernon 🇨🇦 @hannahvernon.com · 07/09/2026
One NULL in the List and NOT IN Returns Nothing 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…
sqlserverscience.com
One NULL in the List and NOT IN Returns Nothing
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);
020
Hannah Vernon 🇨🇦 @hannahvernon.com · 06/09/2026
ISNULL Truncates Your Replacement Value; COALESCE Doesn’t 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…
sqlserverscience.com
ISNULL Truncates Your Replacement Value; COALESCE Doesn’t
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…
010
Hannah Vernon 🇨🇦 @hannahvernon.com · 05/09/2026
Proving the Restore, Part 14: Getting the Script Out of the Procedure 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…
sqlserverscience.com
Proving the Restore, Part 14: Getting the Script Out of the Procedure
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…
001
Hannah Vernon 🇨🇦 @hannahvernon.com · 04/09/2026
Proving the Restore, Part 13: Two Vocabularies for the Same Backup 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…
sqlserverscience.com
Proving the Restore, Part 13: Two Vocabularies for the Same Backup
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.
000
Hannah Vernon 🇨🇦 @hannahvernon.com · 03/09/2026
Proving the Restore, Part 12: The Files That Take No Extension 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…
sqlserverscience.com
Proving the Restore, Part 12: The Files That Take No Extension
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…
000
Hannah Vernon 🇨🇦 @hannahvernon.com · 02/09/2026
Proving the Restore, Part 11: Generating the Restore Script 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…
sqlserverscience.com
Proving the Restore, Part 11: Generating the Restore Script
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.
000
Hannah Vernon 🇨🇦 @hannahvernon.com · 01/09/2026
Proving the Restore, Part 10: Can You Reach That Point in Time? 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.…
sqlserverscience.com
Proving the Restore, Part 10: Can You Reach That Point in Time?
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…
110
Hannah Vernon 🇨🇦 @hannahvernon.com · 31/08/2026
Proving the Restore, Part 9: Backups From a Different Database 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…
sqlserverscience.com
Proving the Restore, Part 9: Backups From a Different Database
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.
000
Hannah Vernon 🇨🇦 @hannahvernon.com · 30/08/2026
Proving the Restore, Part 8: Proving the Log Chain Has No Gaps 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.…
sqlserverscience.com
Proving the Restore, Part 8: Proving the Log Chain Has No Gaps
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.
000
Hannah Vernon 🇨🇦 @hannahvernon.com · 29/08/2026
Proving the Restore, Part 7: One File, Hundreds of Backup Sets 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…
sqlserverscience.com
Proving the Restore, Part 7: One File, Hundreds of Backup Sets
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…
000
Hannah Vernon 🇨🇦 @hannahvernon.com · 28/08/2026
Proving the Restore, Part 6: The Differential Base Has to Match 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…
sqlserverscience.com
Proving the Restore, Part 6: The Differential Base Has to Match
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.
000
Hannah Vernon 🇨🇦 @hannahvernon.com · 27/08/2026
Proving the Restore, Part 5: Striped Backups Are All or Nothing 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 =…
sqlserverscience.com
Proving the Restore, Part 5: Striped Backups Are All or Nothing
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.
000
Hannah Vernon 🇨🇦 @hannahvernon.com · 26/08/2026
Proving the Restore, Part 4: Finding Backup Files Without xp_cmdshell 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…
sqlserverscience.com
Proving the Restore, Part 4: Finding Backup Files Without xp_cmdshell
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].
000
Hannah Vernon 🇨🇦 @hannahvernon.com · 25/08/2026
Proving the Restore, Part 3: The Error That Blames the Wrong Statement 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…
sqlserverscience.com
Proving the Restore, Part 3: The Error That Blames the Wrong Statement
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.
000
Hannah Vernon 🇨🇦 @hannahvernon.com · 24/08/2026
Proving the Restore, Part 2: HEADERONLY Changes Shape Between Versions 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…
sqlserverscience.com
Proving the Restore, Part 2: HEADERONLY Changes Shape Between Versions
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.
000
Hannah Vernon 🇨🇦 @hannahvernon.com · 23/08/2026
Proving the Restore, Part 1: The Backup Job Said Success 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…
sqlserverscience.com
Proving the Restore, Part 1: The Backup Job Said Success
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.
000
Hannah Vernon 🇨🇦 @hannahvernon.com · 22/08/2026
Acronyms, Collisions, and the Cost of Not Asking 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…
sqlserverscience.com
Acronyms, Collisions, and the Cost of Not Asking
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…
000
Hannah Vernon 🇨🇦 @hannahvernon.com · 21/08/2026
Forwarded Records: the Back-Pointer and the Trip Home 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…
sqlserverscience.com
Forwarded Records: the Back-Pointer and the Trip Home
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.
000
Hannah Vernon 🇨🇦 @hannahvernon.com · 20/08/2026
The Blast Radius Was Four Rows 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…
sqlserverscience.com
The Blast Radius Was Four Rows
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.
010
Hannah Vernon 🇨🇦 @hannahvernon.com · 19/08/2026
How Much Transaction Log Does REORGANIZE Really Generate? I Measured It 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…
sqlserverscience.com
How Much Transaction Log Does REORGANIZE Really Generate? I Measured It
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.
000
Hannah Vernon 🇨🇦 @hannahvernon.com · 18/08/2026
sql-login-syncer: Copy SQL Server Logins Without Losing SIDs or Passwords 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…
sqlserverscience.com
sql-login-syncer: Copy SQL Server Logins Without Losing SIDs or Passwords
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.
110
Hannah Vernon 🇨🇦 @hannahvernon.com · 17/08/2026
Logins, SIDs, and Kerberos from First Principles, Part 8: Auditing Logins with Service Broker 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…
sqlserverscience.com
Logins, SIDs, and Kerberos from First Principles, Part 8: Auditing Logins with Service Broker
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.
000
Hannah Vernon 🇨🇦 @hannahvernon.com · 16/08/2026
Logins, SIDs, and Kerberos from First Principles, Part 7: Is Your Kerberos Still Using RC4? 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…
sqlserverscience.com
Logins, SIDs, and Kerberos from First Principles, Part 7: Is Your Kerberos Still Using RC4?
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.
000
Hannah Vernon 🇨🇦 @hannahvernon.com · 15/08/2026
Logins, SIDs, and Kerberos from First Principles, Part 6: SPNs and the Silent NTLM Fallback 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…
sqlserverscience.com
Logins, SIDs, and Kerberos from First Principles, Part 6: SPNs and the Silent NTLM Fallback
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.
000
Hannah Vernon 🇨🇦 @hannahvernon.com · 14/08/2026
Logins, SIDs, and Kerberos from First Principles, Part 5: Windows Groups and the Invisible Members 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…
sqlserverscience.com
Logins, SIDs, and Kerberos from First Principles, Part 5: Windows Groups and the Invisible Members
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.
000