Search code examples
sqlsql-servert-sqlscriptingansi-sql

Sql Server Management Studio - Programmatically Creating Scripts based on DAL; Why use SET ANSI_NULLS ON before each table

I'm looking to generate the SQL scripts for table creation programmatically based on class definitions in my DAL similar to SQL Server Management Studio.

So, when I right click on a table in MS SMS, and tell it to script table as > Create to > new query window, it generates some very understandable code. However, I'm not sure why they generate a few of the following lines:

  • SET ANSI_NULLS ON
  • SET QUOTED_IDENTIFIER ON
  • SET ANSI_PADDING ON
    • {TABLE DEF GOES HERE}
  • SET ANSI_PADDING OFF

I have looked these statements in the product documentation, and understand what they are for, but am not sure why they are generated around each table declaration.

Solution

  • These settings are stored with the table or code definition. If you set these in a stored proc, then they are ignored at runtime.

    So, if you script the table, you need to script the settings too. Especially if you have computed columns etc too.