Peter Trast (4/13/2010)
So if I connect via command line, I can query the status of all of my ON/OFF options and they will all be off until I connect... SSMS or Redgate? Interesting. I will have to test that 🙂
I'm afraid I'm not sure what you mean by connecting from the command line... :unsure:
Tools/libraries/drivers typically SET various options when they connect - including ANSI_NULLS.
My point is that SQL Server's defaults (out of the box, as it were) are for all the ON/OFF options to be OFF at both server and database level. You can change this, but that's the default.
Instance-level defaults are set using sp_configure 'user options'.
Database-level defaults are set using ALTER DATABASE <sql_option>.
See:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi