I'm using DBDeploy.NET for change control management in my T-SQL codebase. DBDeploy works by the developer maintaining a numbered set of change scripts. When a deploy is triggered (via NAnt), DBDeploy looks at a changelog table and determines which patches need to be executed to bring the instance up to date.
The issue I have is with setting the required settings necessary to create an indexed view. QUOTED_IDENTIFIER, ANSI_NULLS, and ARITHABORT all need to be on. Yes, I can easily put these SET statements at the top of the change script that creates/alters the indexed view. But these settings are connection level. What if later I'm building a new instance from scratch? When I run DBDeploy on the new instance, these settings will bleed through to all subsequent change scripts, since all change scripts are effectively concatenated into a final SQL script to be executed on a single connection. What's worse are parse-time options like QUOTED_IDENTIFIER, which would be applied to all change scripts prior as well. So:
- I'm on SQL Server 2000. Is my interpretation of connection-level settings correct? I.e. using GO to break the script into batches does nothing to limit the scope of these SET options. What about later versions, where connection-level settings have been renamed batch-level?
- Is there any way to unSET the SET? As I understand it, connection-level settings are trinary - i.e. ON, OFF, and default, where default is interpreted based on the content of the SQL statement, instance settings, database settings, and persisted user settings. If I SET something to ON, I can't undo it simply undo it by setting it to OFF, because it would mask default, if that's what the setting was before.
- Is there any way to save the state of the connection-level setting before setting it, so I can manually restore it after?
The alternatives suck:
- I can wrap each create/alter statement for indexed views in dynamic SQL - with it's 4000/8000 char limitation on SS2K. That would limit the scope of SET statements quite well.
- I can institute a policy of fixing the SET options to be used at the project level, and requiring all devs to place those SET options at the top of each change script to enforce it, since there's no telling until deploy time precisely which change scripts will be applied.
- I can patch DBDeploy itself to always use a new connection for each change script, but that would require redesigning the way it handles undoing change scripts.
So what can be done, and what should I do?