To clear cached plans not used for a set time, list the plans by their last execution time or by the time they were built, then clear each one by its plan handle. This post builds a small procedure that previews the plans first and removes them only when you switch it on. It also explains why the plan handle needs its 0x prefix.
SQL SERVER Management Studio – Rebuild All Indexes on Table
SSMS can open a Rebuild All dialog for table indexes. An e-commerce customer discovered this shortcut during my health check.
Count NULLs in a Column in SQL Server: Five Ways Compared
To count NULLs in a column, filter with IS NULL and count the rows, or subtract COUNT(column) from COUNT(*). This post tests five ways on 100,000 rows and compares their page reads. It also counts NULLs in several columns at once and shows the NULL group in GROUP BY.
ANSI_WARNINGS OFF: Why SQL Server Truncates Data Silently
With ANSI_WARNINGS OFF, SQL Server cuts a string to fit its column and reports no error. This post runs the same insert with the setting on and off, and shows what else changes: variables, divide by zero and filtered indexes. It also shows how to check which setting a session uses.
SELECT INTO Table Variable: Why It Fails and What to Use
SELECT INTO table variable fails with Msg 102 because SELECT INTO creates its target table and a table variable must be declared first. This post shows two working fixes, a declared table variable and a temp table. It also compares them on rollback, dynamic SQL and row estimates.
How to Join Two Tables Without Using Join Keywords? – Interview Question of the Week #266
Question: How to Join Two Tables Without Using Join Keywords?
Answer: In my life, I have attended many interviews as an interviewer.
Smiley in SSMS: Show Emoji in Results and Database Names
To show a smiley in SSMS, type the emoji inside an N-prefixed string and run the query. This post explains how SQL Server stores each emoji as two characters, why two different emoji can compare as equal, and how to find them in a column. It also shows what happens when an emoji is part of a database name.