Tables using ANSI_NULLS

Question

Post reply

Tables using ANSI_NULLS

Mike Scalise

SSCrazy Eights

Points: 8145
More actions
October 26, 2018 at 1:44 pm

#416342

Hi,
I have a database that's comprised of over 10,000 tables. Approximately 250 tables were created with ansi_nulls set to "on" whereas the remainder had ansi_nulls set to "off". I checked the tables in several other databases, and they were all created with ansi_nulls "on". Why would a vendor mix and match like that?
SELECT name, uses_ansi_nulls FROM sys.tables ORDER BY uses_ansi_nulls, name
And why do I care? Well, I'm trying to create some indexed views, but I'm not meeting all of the prerequisites...

"Cannot create index. Object 'TABLENAME' was created with the following SET options off: 'ANSI_NULLS'."
Thanks in advance.
Mike
Mike Scalise, PMP
https://www.michaelscalise.com

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246987 More actions · Answer 1

Why would a vendor mix and match like that?

Incompetence? Lack of care?
I doubt there's a technical reason.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Mike Scalise SSCrazy Eights Points: 8145 More actions · Answer 2

Phil Parkin - Friday, October 26, 2018 1:48 PM
Why would a vendor mix and match like that?

Incompetence? Lack of care?
I doubt there's a technical reason.

That's what I'm thinking too. I'm going to put a ticket in, but I'm fearful that no one will provide me a sound reason for doing that. If I get a good, technical reason, I'll update this.

Thanks,

Mike

Mike Scalise, PMP
https://www.michaelscalise.com

Sue_H SSC Guru Points: 90860 More actions · Answer 3

Mike Scalise - Friday, October 26, 2018 1:54 PM
Phil Parkin - Friday, October 26, 2018 1:48 PM
Why would a vendor mix and match like that?

Incompetence? Lack of care?
I doubt there's a technical reason.
That's what I'm thinking too. I'm going to put a ticket in, but I'm fearful that no one will provide me a sound reason for doing that. If I get a good, technical reason, I'll update this.
Thanks,
Mike

I think it's a lack of awareness, different developers having different settings and being totally oblivious to how it makes a difference.
I've seen it happen before and most have had no idea of the issue until they get the error. You can find posts on forums where people hit the error and didn't realize objects were created with the setting.
I think people just don't know more than anything else.

Sue

Mike Scalise SSCrazy Eights Points: 8145 More actions · Answer 4

Sue_H - Friday, October 26, 2018 2:04 PM
Mike Scalise - Friday, October 26, 2018 1:54 PM
Phil Parkin - Friday, October 26, 2018 1:48 PM
Why would a vendor mix and match like that?

Incompetence? Lack of care?
I doubt there's a technical reason.
That's what I'm thinking too. I'm going to put a ticket in, but I'm fearful that no one will provide me a sound reason for doing that. If I get a good, technical reason, I'll update this.
Thanks,
Mike
I think it's a lack of awareness, different developers having different settings and being totally oblivious to how it makes a difference.
I've seen it happen before and most have had no idea of the issue until they get the error. You can find posts on forums where people hit the error and didn't realize objects were created with the setting.
I think people just don't know more than anything else.
Sue

That's a good point, Sue. It's so unfortunate too, because the inability to create indexed views is actually a pretty big hindrance when it comes to performance in reporting. Additionally, the collation is not one of the most common ones either, so you can imagine how that is when joining to tables from other databases with more common collations (from an actual query-writing perspective and due to the fact that indexes can't be leveraged)

Mike

Mike Scalise, PMP
https://www.michaelscalise.com

Sue_H SSC Guru Points: 90860 More actions · Answer 5

Mike Scalise - Friday, October 26, 2018 2:09 PM
Sue_H - Friday, October 26, 2018 2:04 PM
Mike Scalise - Friday, October 26, 2018 1:54 PM
Phil Parkin - Friday, October 26, 2018 1:48 PM
Why would a vendor mix and match like that?

Incompetence? Lack of care?
I doubt there's a technical reason.
That's what I'm thinking too. I'm going to put a ticket in, but I'm fearful that no one will provide me a sound reason for doing that. If I get a good, technical reason, I'll update this.
Thanks,
Mike
I think it's a lack of awareness, different developers having different settings and being totally oblivious to how it makes a difference.
I've seen it happen before and most have had no idea of the issue until they get the error. You can find posts on forums where people hit the error and didn't realize objects were created with the setting.
I think people just don't know more than anything else.
Sue
That's a good point, Sue. It's so unfortunate too, because the inability to create indexed views is actually a pretty big hindrance when it comes to performance in reporting. Additionally, the collation is not one of the most common ones either, so you can imagine how that is when joining to tables from other databases with more common collations (from an actual query-writing perspective and due to the fact that indexes can't be leveraged)
Mike

Totally agree and the collations being another great example. Too often people worry only about getting a task done rather than getting a task done right. Its easy to forget when you see folks up here who are passionate about SQL Server and want to know details, inner workings, etc. Some are just that motivated such as yourself and want to truly understand how things are working. Not everyone has type of motivation and I think it's incredibly hard for those on the more "passionate" side to understand that. But they exist - in and out of IT.
It will be interesting to see how that vendor reacts to that ticket. If nothing else, you've done your good deed for the month 🙂 They'll learn something new and I can bet that quite a few reading your post learned something new as well.

Sue

Mike Scalise SSCrazy Eights Points: 8145 More actions · Answer 6

Sue_H - Friday, October 26, 2018 3:18 PM
Mike Scalise - Friday, October 26, 2018 2:09 PM
Sue_H - Friday, October 26, 2018 2:04 PM
Mike Scalise - Friday, October 26, 2018 1:54 PM
Phil Parkin - Friday, October 26, 2018 1:48 PM
Why would a vendor mix and match like that?

Incompetence? Lack of care?
I doubt there's a technical reason.
That's what I'm thinking too. I'm going to put a ticket in, but I'm fearful that no one will provide me a sound reason for doing that. If I get a good, technical reason, I'll update this.
Thanks,
Mike
I think it's a lack of awareness, different developers having different settings and being totally oblivious to how it makes a difference.
I've seen it happen before and most have had no idea of the issue until they get the error. You can find posts on forums where people hit the error and didn't realize objects were created with the setting.
I think people just don't know more than anything else.
Sue
That's a good point, Sue. It's so unfortunate too, because the inability to create indexed views is actually a pretty big hindrance when it comes to performance in reporting. Additionally, the collation is not one of the most common ones either, so you can imagine how that is when joining to tables from other databases with more common collations (from an actual query-writing perspective and due to the fact that indexes can't be leveraged)
Mike
Totally agree and the collations being another great example. Too often people worry only about getting a task done rather than getting a task done right. Its easy to forget when you see folks up here who are passionate about SQL Server and want to know details, inner workings, etc. Some are just that motivated such as yourself and want to truly understand how things are working. Not everyone has type of motivation and I think it's incredibly hard for those on the more "passionate" side to understand that. But they exist - in and out of IT.
It will be interesting to see how that vendor reacts to that ticket. If nothing else, you've done your good deed for the month 🙂 They'll learn something new and I can bet that quite a few reading your post learned something new as well.
Sue

Yes! Exactly this. Thank you for understanding.

Mike Scalise, PMP
https://www.michaelscalise.com

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 7

Mike Scalise - Friday, October 26, 2018 1:54 PM
Phil Parkin - Friday, October 26, 2018 1:48 PM
Why would a vendor mix and match like that?

Incompetence? Lack of care?
I doubt there's a technical reason.
That's what I'm thinking too. I'm going to put a ticket in, but I'm fearful that no one will provide me a sound reason for doing that. If I get a good, technical reason, I'll update this.
Thanks,
Mike

The reason may be that it's because of whatever application they used to create the tables, which is the same reason why so many columns that should be INTEGER are defined as NUMERIC(18,0) and columns that should be right-sized VARCHAR(s) are defined as NVARCHAR(256) or 255.

The thing that a lot of people don't know is that SSMS is an application and it has one set of settings and there's another set for connections for other applications. That's a part of the "excitement" that went on in the world when MS announced that ANSI NULLs were no longer going to be optional and would be required at some future date. I'm thinking that excitement may reoccur when then move that notion from a deprecation to a fact.

Bottom line is that there are two sets of default connection settings.... one for SSMS and one for the everything else.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mike Scalise SSCrazy Eights Points: 8145 More actions · Answer 8

Jeff Moden - Sunday, October 28, 2018 3:02 PM
Mike Scalise - Friday, October 26, 2018 1:54 PM
Phil Parkin - Friday, October 26, 2018 1:48 PM
Why would a vendor mix and match like that?

Incompetence? Lack of care?
I doubt there's a technical reason.
That's what I'm thinking too. I'm going to put a ticket in, but I'm fearful that no one will provide me a sound reason for doing that. If I get a good, technical reason, I'll update this.
Thanks,
Mike
The reason may be that it's because of whatever application they used to create the tables, which is the same reason why so many columns that should be INTEGER are defined as NUMERIC(18,0) and columns that should be right-sized VARCHAR(s) are defined as NVARCHAR(256) or 255.
The thing that a lot of people don't know is that SSMS is an application and it has one set of settings and there's another set for connections for other applications. That's a part of the "excitement" that went on in the world when MS announced that ANSI NULLs were no longer going to be optional and would be required at some future date. I'm thinking that excitement may reoccur when then move that notion from a deprecation to a fact.
Bottom line is that there are two sets of default connection settings.... one for SSMS and one for the everything else.

Jeff,

Thanks for the response. That's a very good point! I had also read that MS was planning to make ANSI NULLs a requirement in the future. I just wish it were sooner than later. I feel like there are many settings/aspects of SQL Server whose future plans were announced a while back but have not yet been implemented (e.g., requiring semicolons at the end of T-SQL statements). I guess we just wait.

Mike

Mike Scalise, PMP
https://www.michaelscalise.com