I recently ran into a problem with the
QUOTED_IDENTIFIERS option in SQL Server, and it got me to thinking about these
I mean the fact that, on tables where there are filtered indexes or computed columns with indexes,
QUOTED_IDENTIFIER is required to be on to create any other indexes is just not intuitive. But if you can’t create indexes because of it then I’d argue that it’s pretty damn important! I also found out that this problem is not just limited to
QUOTED_IDENTIFIER but to
ANSI_WARNINGS as well.
Just check out the Microsoft Docs and what it has to say about it:
SET ARITHABORT must be ON when you are creating or changing indexes on computed columns or indexed views. If SET ARITHABORT is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail.
ANSI_WARNINGS it says:
SET ANSI_WARNINGS must be ON when you are creating or manipulating indexes on computed columns or indexed views. If SET ANSI_WARNINGS is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail.
It’s not just Indexes
So, like a dog when it sees a squirrel, when I found out about the problems with
ANSI_WARNINGS I got distracted and started checking out what else I could break with it. Reading through the docs, because I found that it does help even if I have to force myself to do it sometimes, I found a little gem that I wanted to try and replicate. So here’s a reason why you should care about setting
Default to on
At one stage or another if you’re working with SQL Server, you’ve probably encountered the dreaded “Divide By 0” error:
Msg 8134, Level 16, State 1, Line 4 Divide by zero error encountered.
If you want to check this out, then here’s the code below for our table:
USE Pantheon; -- Create our test table... CREATE TABLE dbo.ArithAborting ( id tinyint NULL ); GO
And our attempt at inserting that value into the table:
SET ARITHABORT ON; GO SET ANSI_WARNINGS ON; GO -- Check can we insert a "divide by 0"... BEGIN TRY INSERT INTO dbo.ArithAborting (id) SELECT 1/0; END TRY BEGIN CATCH PRINT 'NOPE!'; THROW; END CATCH;
And we get our good, old, dreaded friend:
We check our ArithAborting table and nothing is there, like we expected!
SELECT * FROM dbo.ArithAborting;
What about if we were to turn our
ANSI_WARNINGS off though, what happens then? Well that’s a simple thing to test, we just turn them off and run the script again:
--Turn ARITHABORT off; SET ARITHABORT OFF; GO SET ANSI_WARNINGS OFF; GO -- ...insert into our table... BEGIN TRY INSERT INTO dbo.ArithAborting (id) SELECT 1/0; END TRY BEGIN CATCH PRINT 'NOPE!'; THROW; END CATCH;
Now before I freak out and start thinking that I’ve finally divided by zero, let’s check the table:
What’s going on here? Checking the docs…
During expression evaluation when SET ARITHABORT is OFF, if an INSERT, DELETE or UPDATE statement encounters an arithmetic error, overflow, divide-by-zero, or a domain error, SQL Server inserts or updates a NULL value. If the target column is not nullable, the insert or update action fails and the user receives an error.
Do I like this?
If I have a terminating error in my script, I quite like the fact that SQL Server is looking out for me and won’t let me put in bad data, but if you have these options turned off, even if you wrap your code in an
TRY...CATCH block, it’s going to bypass it.
Plus if you are trying to divide by 0, please stop trying to break the universe. Thank you.