A client with a huge partitioned database could not add filegroup and got Msg 0, a severe error. Here is how we found the real, much deeper issue.
SQL SERVER – Puzzle – Strange Behavior With Extra Comma in Create Table Statement
My favorite icebreaker puzzle: a strange behavior with an extra comma in CREATE TABLE statement. Why does SQL Server accept it for temp and regular tables?
SQL SERVER – Nested Triggers – Trigger Calling Another Trigger Error
A reader asked what happens when one trigger calls another. Here is a simple example with two AFTER INSERT triggers that shows how nested triggers behave.
How to Drop All the User Created Statistics by SQL Server? – Interview Question of the Week #158
SQL Server creates statistics on its own, but DBAs and the Tuning Advisor add more. Here is a script to drop all user Created Statistics safely.
Clear Procedure Cache for One Database in SQL Server
To clear procedure cache for one database, run ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE inside that database. This post tests it on two demo databases, shows that the other database keeps its plans, and shows how to remove a single plan by its plan handle. It also covers the cost and the lighter alternatives.
SQL SERVER – Simple Script to Match Session ID to OS Thread ID
How is a SQL Server Session ID mapped to an OS Thread ID? Here is a simple script to match Session ID to OS Thread ID, with the scheduler and task state.
SQL SERVER – Authentication in SQL Server (Windows and Mixed Mode) – GDPR Series
Authentication in SQL Server identifies the caller through supported identity choices. My GDPR series considered those choices alongside access controls.