The SpDev blog

SSMS no longer has a T-SQL debugger. Here are the ways to debug a SQL Server stored procedure today: Visual Studio, RAISERROR WITH NOWAIT, logging that survives a rollback, and stepping through it in VS Code.

EXEC with named and positional parameters, defaults, OUTPUT parameters, return codes, INSERT...EXEC, WITH RESULT SETS, calling one procedure from another, and the errors you'll meet.

Search the code of every stored procedure, function, view and trigger for a table, column or phrase. Why INFORMATION_SCHEMA misses matches, how to search all databases, and how to find real dependencies.

A stored procedure template for SQL Server with SET options, parameter checks, a transaction and error handling that works, and the reasons behind each line.

Three ways a SQL Server stored procedure returns data: result sets, OUTPUT parameters and the return code. How each works, their traps, and which to use when.

What procedures can do that functions can't, and the other way round. Scalar, inline and multi-statement functions compared, with the performance traps of each, measured.

Views are saved queries you can select from; stored procedures are programs you execute. How they differ in parameters, performance, security and updates, with examples.

What a stored procedure is, how to create and run one, what it's good for and where it hurts, with examples you can run on SQL Server.

Estimated vs actual plans, how to get them in SSMS, VS Code and T-SQL, how to read the operators, and the eight things to look for when a query is slow.

An NVARCHAR parameter against a VARCHAR column can turn a 6-read seek into a 600-read scan. How to spot implicit conversions in the plan, and how to fix them in T-SQL and .NET.

Monitor SQL Server performance without buying anything: what's running now, who is blocking whom, performance counters, Query Store regressions and Extended Events, with queries you can run today.

Why a stored procedure is fast for one customer and slow for another, how to prove it's parameter sniffing, and the fixes that work, from a covering index to OPTION (RECOMPILE).

Ten checks that fix most slow SQL Server queries, from sargable predicates to catch-all searches, each with logical reads measured before and after.

How SQL Server indexes work, when the optimizer uses them and when it ignores them, key lookups, covering indexes and column order, with logical reads measured on real data.

A method for SQL Server performance tuning: start from waits, find the queries that cost the most with Query Store and DMVs, fix them one at a time, and prove each fix.

NOLOCK doesn't just skip waiting: it reads uncommitted data and can miss or double-count rows. A two-session demonstration, the errors it causes, and why READ_COMMITTED_SNAPSHOT is usually the better fix.

ISNULL and COALESCE both replace NULLs, but differ in return type, truncation, nullability and how often they evaluate a subquery. Each difference shown with a query you can run.

What APPLY does that a JOIN can't: top N rows per group, calling table-valued functions per row, unpivoting columns and reusing calculations. With reads compared against ROW_NUMBER.

What a common table expression is, how to write recursive CTEs for hierarchies and date series, MAXRECURSION, and why a CTE used twice is computed twice.

How T-SQL cursors and WHILE loops work, why row-by-row code is slow, the cursor options that matter, and set-based rewrites for the common cases, with timings.

How TRY...CATCH works in T-SQL, the errors it can't catch, THROW vs RAISERROR, doomed transactions and XACT_STATE, and a CATCH block you can copy.

Turn rows into columns with PIVOT, the conditional aggregation alternative that's often clearer, a dynamic PIVOT for unknown columns, and UNPIVOT back again.

Window functions in SQL Server with practical examples: numbering, ranking, top N per group, previous and next rows, running totals, and why the default frame gives different answers and runs slower.

Statistics, row estimates, rollback behavior, indexes, recompiles and scope: how #temp tables and @table variables differ in SQL Server 2019 and later, measured on real data.

How to write an upsert with MERGE in SQL Server, the errors you'll meet, why it needs HOLDLOCK under concurrency, and when separate UPDATE and INSERT statements are the better choice.

How SQL Server transactions really behave: why a failed statement doesn't roll back by itself, what XACT_ABORT changes, how nested transactions work, and the error 266 you get from procedures.

What parameter types ADO.NET, Dapper and EF Core really send for .NET strings, why NVARCHAR against a VARCHAR column scans the index, and how to fix it in each library. Verified with SQL Server's plan cache.

Working C# code to call a stored procedure, read several result sets, and get output parameters and return values with ADO.NET, Dapper and EF Core, plus the mistakes that return null.

Azure Data Studio was retired on 28 February 2026. How to set up VS Code for SQL Server work: the MSSQL extension, a local SQL Server in Docker, procedures in source control, debugging and profiling.

SQL Server Profiler is deprecated. How to capture the same trace with Extended Events in T-SQL, read the results with a query, and profile a single stored procedure line by line.