0

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.

Improve this question
2
  • I don't think this question fits the SE format well - it is very open ended. That being said, starting with ANSI itself its probably a good place to begin: blog.ansi.org/2018/10/… although it looks like getting the actual standards ain't free.
    – LowlyDBA - John M
    Jul 16 '20 at 15:59
  • Regarding why these are lumped together, these particular settings are required to be set properly in order use newer SQL Server features like indexed views, etc.
    – Dan Guzman
    Jul 16 '20 at 16:43
2

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.