←
Paul White14,118 posts
Paul White avatar

Paul White

@SQL_Kiwi

I live in Aotearoa New Zealand and write about SQL Server internals at sql.kiwi Microsoft MVP 2011-2022 DBA Stack Exchange Mod 2015-2025

◷ Joined April 2010▧ 344 Photos and videos
Paul White avatar
Paul White@SQL_Kiwi·Jul 13

RIP Sam Neill. Great actor, great winemaker, great New Zealander. Difficult not to take these things unreasonably personally.

Post attachment
Paul White avatar
Paul White@SQL_Kiwi·25 May 2025

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 docs.microsoft.com/en-us/sql…

Paul White avatar
Paul White@SQL_Kiwi·22 May 2025

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.

Paul White avatar
Paul White@SQL_Kiwi·21 May 2025

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

Paul White avatar
Paul White@SQL_Kiwi·22 Apr 2025

This is a really funny SQL Server bug dbfiddle.uk/iUmhLPln 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, T.i) IS NULL;

Paul White avatar
Paul White@SQL_Kiwi·1 Apr 2025

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()); 😲

Paul White avatar
Paul White@SQL_Kiwi·31 Mar 2025

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.

Paul White avatar
Paul White@SQL_Kiwi·29 Mar 2025

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). sql.kiwi/2025/03/sharing-dat…