You can tell the optimizer a three-row table has 10 million rows and watch the plan change. Here is how, and how to undo it.
Magic Numbers in Code: Replacing Status Codes With a Lookup Table
Nobody remembers what the 3 means. Give each status code an approved name, let the database enforce it, and leave your data alone.
Temporal History Queries: Indexing the History Table for Speed
Your current table is tiny, but the AS OF question hides in the history table. See the reads before and after a key-first index, and what it costs.
Currency Rates by Effective Date: Designing the Lookup Table
Why did last quarter’s invoices change overnight? Probably because the rate table had no dates. Here is a lookup that remembers.
Building Full Folder Paths From a Parent-Child Table
Your folder table stores only a name and a parent, so the full path has to be walked. I show the query, and how to catch the rows it quietly leaves out.
Changing a User-Defined Table Type That Procedures Use
You need one more column in a table type, but procedures depend on it. Here is the order of moves that works, with the errors along the way.
Counting Partitions per Table and Spotting Empty Ones
A quick count said 8 partitions, but the table has 4. Here is where the extra rows came from and how to spot empty partitions.