Paul White

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

[Paul White @SQL_Kiwi]

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

[Paul White @SQL_Kiwi]

@Suriyakmaps No, it's fine

[Paul White @SQL_Kiwi]

@jasonhorner dbt?

[Paul White @SQL_Kiwi]

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 https://t.co/3ViPqCRDy0

[Paul White @SQL_Kiwi]

@sqL_handLe You're a very bad man!

Nested views = bad
Nested CTEs = good

🤯

[Paul White @SQL_Kiwi]

Same here. I was most surprised to see genuinely nested CTEs added. I suppose someone, somewhere must have an absolutely compelling use case. The only upside can I think of otherwise is it would stop people writing the frankly offensive ";WITH"

[Paul White @SQL_Kiwi]

@Citadel93 It doesn't really add anything new except easier syntax and more obvious feature parity with databases like Oracle, PostgreSQL, and DB2.

We've always been able to index suitable non-persisted computed columns.

[Paul White @SQL_Kiwi]

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 @SQL_Kiwi]

@jcoehoorn @kekline It seems to be exactly the same as creating a (non-persisted) computed column, then creating an index on that. A shorthand for that process.

[Paul White @SQL_Kiwi]

@sqL_handLe Seems to be syntax sugar for doing it all manually. Who knows though before it's finished, released, and documented

[Paul White @SQL_Kiwi]

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

[Darling Data @erikdarlingdata]

TRUNCATE TABLE WITH PARTITIONS causes data corruption with non-aligned partitions
https://t.co/reVkb8SaVe

[Paul White @SQL_Kiwi]

@Suriyakmaps New thread until it breaks please

[Paul White @SQL_Kiwi]

@mcflyamorim The one I keep up to date is https://t.co/BEjgZn8ifN

Nice to see Microsoft being careful with dynamic SQL again after the demise of dbcc clonedb 😀

[Paul White @SQL_Kiwi]

This is a really funny SQL Server bug

https://t.co/1CtW8Oq2EO

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 @SQL_Kiwi]

@sqlcrossjoin @erikdarlingdata Not me

[Paul White @SQL_Kiwi]

@erikdarlingdata A1 effort

[Paul White @SQL_Kiwi]

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

[Paul White @SQL_Kiwi]

FYI my rates for US residents have increased by 25% from today

[Paul White @SQL_Kiwi]

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