Paul White
@SQL_Kiwi
Followers
5K
Following
63K
Media
343
Statuses
14K
I live in Aotearoa New Zealand and write about SQL Server internals at https://t.co/nuZwOWQzHy Microsoft MVP 2011-2022 DBA Stack Exchange Mod 2015-2025
Paraparaumu, New Zealand
Joined April 2010
New article: How to Survive a Rollback (when logging) How to ensure log messages are always written to the database, even when a containing transaction rolls back. https://t.co/9z0iWsbKsu
sql.kiwi
A method to ensure routine log messages are always written to the database, even when a containing transaction rolls back.
0
4
14
I don't really understand why anyone is excited about nested CTEs, but if you want to try it on SQL 2025 CTP2 box, enable TF 15320 (session or global). Currently only documented for 'Fabric' whatever that is
learn.microsoft.com
Nested common table expressions (CTEs) can simplify complex queries by deconstructing ordinarily complex queries into reusable blocks.
1
2
12
Looks like 'expression indexes' are coming to SQL Server 2025. This is valid syntax on CTP 2 when trace flag 17152 is enabled: CREATE INDEX ix ON MyTable (n * 2); It creates a computed column for you, named e.g. $expression_index_B18729F8A924473FA0DF2CB95D9B2E01.
3
4
18
Interesting undocumented USE HINTs in SQL Server 2025 CTP 2: * DISABLE_PERFORMANCE_SORT_FOR_DML sets DMLRequestSort to false like TF 8795 * DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION * PARALLEL_DML_FOR_VECTOR_INDEX_BUILD * DISABLE_SCALABLE_SUBTREE
0
3
8
TRUNCATE TABLE WITH PARTITIONS causes data corruption with non-aligned partitions https://t.co/reVkb8SaVe
1
1
7
This is a really funny SQL Server bug https://t.co/f0wIDabehq DECLARE @T table (i smallint NOT NULL PRIMARY KEY); INSERT @T (i) VALUES (256); SELECT TRY_CONVERT(tinyint, T.i) FROM @T AS T; SET STATISTICS XML ON; -- Oops! SELECT T.i FROM @T AS T WHERE TRY_CONVERT(tinyint,
1
4
13
New article: 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. https://t.co/OCyrGy8isF
sql.kiwi
Using session context to record debugging information within a SQL Server user-defined function.
1
4
15
New article: Accessing a SQL Server Table Variable in a Different Scope Two ways to access a SQL Server table variable outside its declaration scope. https://t.co/MGSPBuMplY
sql.kiwi
Two ways to access a SQL Server table variable outside its declaration scope.
0
4
13
Since 2008, XACT_STATE includes system transactions, so it works correctly in triggers. But: -- XACT_STATE = 0 PRINT 'XACT_STATE = ' + CONVERT(varchar(11), XACT_STATE()); -- XACT_STATE = 1 PRINT CONCAT('XACT_STATE = ', XACT_STATE()); 😲
0
1
7
You can't use PRINT or RAISERROR in a SQL Server function to emit debugging data. A workaround: Call sp_set_session_context to save info inside the function. Read the data using SESSION_CONTEXT after the function call.
5
5
38
New article: 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. https://t.co/9pZE22feiH
sql.kiwi
Prevent unnecessary procedure compilations and plan cache bloat when sharing SQL Server temp tables
0
5
14
New article: 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). https://t.co/9wWvpOQzTf
sql.kiwi
SQL Server cursor variables can be used to share data between stored procedures as input or output (but not both), despite documentation hints otherwise.
0
1
10
Follow-up article showing how to use this technique with updates: Updating in Batches using an API Cursor https://t.co/XetYuaD2MT
sql.kiwi
How to split a large update operation into batches using a SQL Server API cursor.
0
3
6
Follow-up article showing how to use this technique with updates: Updating in Batches using an API Cursor https://t.co/XetYuaD2MT
sql.kiwi
How to split a large update operation into batches using a SQL Server API cursor.
0
3
6
New SQL Server article: Deleting in Batches using an API Cursor API cursors are quite different from T-SQL cursors. They can operate on more than one row at a time, which makes them particularly suited to batched operations, even on an unindexed heap. https://t.co/ISzVPa6Cb1
sql.kiwi
How to split a large delete operation into batches using a SQL Server API cursor.
2
7
29
New article: What Was the TOP PERCENT Bug and How Was It Fixed? https://t.co/mpmAp3mv7a
sql.kiwi
Details of an incorrect results bug with TOP PERCENT when used with correlated parameters, and how Microsoft chose to address it.
3
3
12