I know what these settings do and when to use them, what I'd really like to know is why this problem even exists.
Recently, I came across an ugly situation–make that, yet another ugly situation–involving the settings of what I'm goint to call the ANSI Seven:
ansi_nulls ON
ansi_padding ON
ansi_warnings ON
arithabort ON
concat_null_yields_null ON
numeric_roundabort OFF
quoted_identifier ON
I've listed them above with what I believe to be the most commonly recommend default settings. (Of interest, SET ANSI_DEFAULTS will set all of them ON or OFF, without special treatment for numeric_roundabort, but I digress.)
These impact code, queries, query plans, and a whole host of stuff as can be found by searching for any of them throughout SO. There are so many hits, they obviously have caused pain and anguish to hundreds, if not thousands, of developers for decades. (I hit issues with them long before SO was there to help.)
What I would like to know is: why?
Why is this an issue? Where did these settings come from? Who thought it was a good idea? Most importantly, why are these seven pretty much always lumped together?
Does anyone have any insights on this? Links to articles, blogs, even Learning Channel Documentaries would be great.
Best Answer
SQL Server came from Sybase, which had different default settings for these kind of things. Gradually, MS introduced SET settings to allow us to control behavoiur. A big push to follow ANSI standard was with the 6.0 release in 1995. I believe they even hired Joe Celko with the work to be ANSI SQL certified (yes, there was a certification process back in the days - anybody ever used SET FIPS_FLAGGER? :-)). This was when the specific SET ANSI setting was inroduced - to allow us to "be ANSI" with only one setting. But this is all a mess with the combo of database settings (which are largely ignored) along with different APIs and tools that has different combo of these settings.