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

Question

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.

Thank you,

Scott

Answer 1

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.