Saturday, November 22, 2014


This is a cool little feature I found the other day doing some research on a SQL Server Central article. Someone asked if they could essentailly check for syntax errors in dynamically generated maintenance scripts. The first thought I had was to simply wrap each statement in a try/catch and an explicit transaction, but he also said that these were index maintenance statements and it wasn't feasible to be doing all the "heavy lifting" of rebuilding and reorganizing indices just to validate the SQL.

Enter the obscure NOEXEC setting. As the name suggests, when set ON, it will parse/validate sql but not execute it. While this is admittedly a pretty fringe case you'd need this for, it's none the less a very elegant solution to problems like the aforementioned.

set noexec on

alter index IXN__myIndex
    on dbo.myTable

set noexec off