Recording Debugging Information Inside a SQL Server Function Call
We cannot use PRINT or RAISERROR statements within a T-SQL function body for debugging purposes. This article presents a workaround using session context.
Articles
Page 1 of 11
— showing articles
1 to
18
of 194
Accessing a SQL Server Table Variable in a Different Scope
Two ways to access a SQL Server table variable outside its declaration scope.
Avoid optional_spid Plan Cache Issues when Sharing Temp Tables
How to avoid unnecessary stored procedure compilation and plan cache pollution when using shared SQL Server temporary tables. Includes an alternate implementation using API cursors without row-by-row processing.
Sharing Data Between Procedures using a Cursor
Contrary to the product documentation, SQL Server cursor variables can be used to share data between stored procedures in the input or output direction (but not both).
Updating in Batches using an API Cursor
How to split a large update operation into batches using a SQL Server API cursor.
Deleting in Batches using an API Cursor
How to split a large delete operation into batches using a SQL Server API cursor.
What Was the TOP PERCENT Bug and How Was It Fixed?
Details of an incorrect results bug with TOP PERCENT when used with correlated parameters, and how Microsoft chose to address it.
MERGE Bug with Updates Through a View
A bug with MERGE that causes SQL Server to try to update a non-existent row, leading to database corruption or a retail assertion, stack dump, and connection termination.
How a Lazy Index Spool Works
Exploring the internal layout of a Lazy Index Spool's worktable, answering how it distinguishes a NULL result from an empty one.
The SQL Server "Segment Apply" Optimizer Transformations
The SQL Server Segment Apply optimizer feature and how it is implemented in GenGbApplySimple, GenGbApplyAgg, and BuildGbApply.
Interesting Things about Prepared Statements and sp_executesql
Internal and less well-known SQL Server details about prepared statements and sp_executesql
Parameter Sniffing with sp_prepare and sp_prepexec
SQL Server parameter sniffing when using sp_prepare or sp_prepexec in T-SQL.
Parameter Sensitive Plan Optimization Thresholds
Documenting some of the internal thresholds used when deciding whether to apply the Parameter Sensitive Plan Optimization in SQL Server 2022.
Can a SQL Server Execution Plan Operator Produce More Batches than Rows?
SQL Server batch mode operators produce batches containing between 64 and 900 rows. How then can a batch mode operator produce more batches than rows?
Glacially Slow SQL Server Resumable Index Operations
SQL Server resumable index operations may be very slow when resumed after the transaction log runs out of space.
Improved RCSI Ghost Cleanup in SQL Server 2022
An example of poor update performance under RCSI as compared with locking read committed. Details of an improvement in SQL Server 2022 that can help avoid the worst effects.
SQL Server Forced Plans Overwite the Query Hash
A 16-year old SQL Server bug that means 'forced plans' have the query plan hash in place of the expected query hash. Includes an explanation and discussion of the term 'morally equivalent plan'.
Why a Self-Join Requires Halloween Protection
I was asked recently why Halloween Protection was needed for data modification statements that include a self-join of the target table. This gives me a chance to explain, while also covering some interesting product bug history from the SQL Server 7 and 2000 days.