Time to read: ~ 2 minutes
You think this will take me hours? Ha! Think again.
You think this will take me hours? Ha! Think again.
Time to read: ~ 2 minutes
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.
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
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.
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.
PowerShell is ußer-useful!
So I’ve talked before about keeping new lines when copying results to a different window in SQL Server and about copying new lines out of SQL Server into reports.
These topics have come about as they are both issues that I’ve had to deal with. Well, another of those issues is dealing with exporting special characters out of SQL Server using PowerShell.
We already have our table called “dbo.NewLineNotes” from before when we were trying to copy new lines out of SQL Server so we’re going to add another row.
Now personal experience for me centered around the German Eszett (“ß”) but you may encounter this with other characters.
-- Insert some special characters... INSERT INTO dbo.NewLineNotes (Notes) VALUES (N'This is a ß')
Now if you were to use the code from keeping new lines post…
and open up the csv file we would get…
Well with SQL Server, I normally break things down into the smallest parts and slowly build it up until it breaks. For this, it breaks when we get to
Export-CSV as everything before it works!
PowerShell is even easier for troubleshooting methodology as , and we’ve talked about it before,
Get-Help are there to help us!
We know that it’s
Export-CSV that is somehow screwing up our special character so the obvious next step…
help Export-CSV -Full;
And we can see a parameter just shine at us!
So we have to define an “Encoding” do we? I used “UTF8” and modified my query…
And special characters are no longer an issue for us 🙂
Straight away I want to apologise for the Nicolas Cage memes!
I just got back from the Reading User Group and I’m still in that post “User Group Glow”, also known as “Long Day Lethargy”, or “Twelve Hour Tiredness”.
They are great though! A chance to talk to other people in the SQL Server community, – a slight reminder that even if you work alone, people are still experiencing some of the same problems that you are (apparently everyone has to deal with multiple nested views, who knew!) – a chance to hear presentations on different topics, and pizza if you’re lucky (we were).
I realised during the session that the two presentations given during the User Group had a connection with a small issue with a table change I had been given with a developer.
Here’s what did not happen to me so you can watch out for it.
Raul Gonzalez ( blog | twitter ) was first up with this presentation “Database Design Matters, Seriously”, showing us the chaos that can occur from not giving some serious thought into how you design your database.
His session is not yet up on his blog as I’m writing this but it will be soon so keep an eye out for that!
Now he had a lot of good points but, for brevity’s sake, the main chaos theory points here are what happens if you don’t take advantage of
FOREIGN KEY CONSTRAINTS, and not specifying a columns
NULLABILITY (yes, that’s a word!). SQL Server is a powerful program with many performance optimizations provided for you, but it’s not omniscient; it can only use the information that you give it!
His points on
NULLABILITY (I mean, I think it’s a word) tied in nicely with the next presentation…
David Morrison ( blog | twitter ) followed up with his presentation on “Query Plan Deep Dives” (I had seen this at SQL Bits, but it’s a great session so I had no problems watching it again) and, as an aside, through his presentation he showed us the compound effects that can happen from not specifying a columns
NULLABILITY (it’s got letters so it’s word-like…)
Now his slides and scripts are up on his blog and they do a great job of walking you through them so check them out and you’ll see the compound effects they create!
Here’s a little teaser…
-- now I want all people who's email isn't in the email table SELECT /*C.FirstName , C.LastName ,*/ C.EmailAddress FROM dbo.Contact AS C WHERE C.EmailAddress NOT IN (SELECT E.EmailAddress FROM dbo.Emails AS E) GO
Which brings us back around to consequences or as I like to put it “How I Pissed Off A Dev By Refusing A Simple Request”.
To be quite honest, it was a simple request. A requirement came in to expand a column datatype up to varchar(100), so one of devs wrote up a simple script and passed it onto the DBAs to check as part of the change control procedure.
ALTER TABLE tablename ALTER COLUMN columnname varchar(100)
And I said no.
“WHY???!!!“, you may shout at me (he certainly did), but I’m going to say to you what I said to him. “Give me a chance to explain before you take my head off, alright?”
While there is nothing wrong with the above code syntactically (is that a word?) but I couldn’t approve it since that column was originally
NOT NULL and the above script would have stripped the column of that attribute! Business requirements dictated that it should not allow NULLS, and hey, who are we to argue with that 😐
Double checking to see if the column is NULL or NOT NULL allowed me to see a problem with that code, one that many people would consider simple enough to just allow it through at a quick glance. Which could have opened up problems further down the line if it had run…
Thanks to the User Group, I now know that it could have a knock on effect with our query plans as well!
ALTER TABLE tablename ALTER COLUMN columnname varchar(100) NOT NULL
There, that’s better!
DBAs get a lot of stick sometime, the “Default Blame Acceptors” or the “Don’t Bother Asking” but a lot of the time, it’s not that we want to say no, it’s just that we have to take into consideration a thousand little things that could snowball into 1 giant problem.
With the rise of DevOps, check out the latest T-SQL Tuesday, DBAs have gone from going
to somewhere along the lines of
“Not this second, let me check it out and see what we can do”
If pressed further, we may rely on the good, old “it depends” though. Hey, clichés are there for a reason; they work!
It just goes to show that, like the IT profession, DBAs are constantly evolving.
Continuosly learning, checking out new helping technologies, and going to User Groups are going to help us to deal with it.
Just remember, in the end,
P.S. I should probably mention that the Nicolas Cage memes are because of this blog post by Nate Johnson ( blog ) that I enjoyed so much that I had to do something in response. I’m not normally this crazy, I swear!
Where I compare scripts to BBQ because of course I would 😐
I have this personal opinion that one sign of a good DBA is their ability to automate things and, before the DBA world found PowerShell, the way to do this was with T-SQL.
For example, a T-SQL script to get permissions assigned to a database principal could also include a column to
REVOKE those permissions. This could be “automated” with some dynamic SQL.
SELECT dprin.name AS DatabasePrincipalName, OBJECT_NAME(dperm.major_id) AS ObjectName, dperm.permission_name AS PermissionName, N'REVOKE ' + dperm.permission_name + N' ON OBJECT::' + OBJECT_NAME(dperm.major_id) + N' FROM ' + dprin.name COLLATE Latin1_General_CI_AS AS RevokeMe FROM sys.database_permissions AS dperm INNER JOIN sys.database_principals AS dprin ON dperm.grantee_principal_id = dprin.principal_id WHERE dprin.name = 'public';
This is nice but what about if we are
paranoid forward-thinking enough to realize that this could cause us problems?
“How?” You ask. Well what happens if there existed another database, say [NeedsAllPermissions], with the same table name and the same login has permissions on it.
Are you going to revoke permissions from that database? It needs ALL of them! It says so in the name!
So in an effort to not shoot ourselves in the foot, we add in the database name to our revoke script.
SELECT dprin.name AS DatabasePrincipalName, OBJECT_NAME(dperm.major_id) AS ObjectName, dperm.permission_name AS PermissionName, N'USE ' + DB_NAME() + 'GO' + N'REVOKE ' + dperm.permission_name + N' ON OBJECT::' + OBJECT_NAME(dperm.major_id) + N' FROM ' + dprin.name COLLATE Latin1_General_CI_AS AS RevokeMe FROM sys.database_permissions AS dperm INNER JOIN sys.database_principals AS dprin ON dperm.grantee_principal_id = dprin.principal_id WHERE dprin.name = 'public';
Yes, we’re only using our database now!
Until the day comes when you actually want to revoke permissions to that user. So you run the above code, copy the RevokeMe column and paste it into the management window. and you get…
GO is a special little guy. It’s not exactly T-SQL. It’s a way of telling the SQL Server Management Studio (SSMS) to send everything before it, from the beginning of the script or the preceding
GO, to the SQL Server instance.
If you read the documents, the main point to take away is…
A Transact-SQL statement cannot occupy the same line as a GO command. However, the line can contain comments.
GO is a special little snowflake and needs to be on his own line then. Simple enough if you know that SQL Server converts
CHAR(10) into a New Line.
If you didn’t know that, well you know that now….P.S.
CHAR(13) is a carriage return 😉
So let’s update our script with some
CHAR(10) and see what happens then.
SELECT dprin.name AS DatabasePrincipalName, OBJECT_NAME(dperm.major_id) AS ObjectName, dperm.permission_name AS PermissionName, N'USE ' + DB_NAME() + CHAR(10) + 'GO' + CHAR(10) + N'REVOKE ' + dperm.permission_name + N' ON OBJECT::' + OBJECT_NAME(dperm.major_id) + N' FROM ' + dprin.name COLLATE Latin1_General_CI_AS AS RevokeMe FROM sys.database_permissions AS dperm INNER JOIN sys.database_principals AS dprin ON dperm.grantee_principal_id = dprin.principal_id WHERE dprin.name = 'public';
Now, when we paste the RevokeMe column to a new window, we get…
…absolutely no difference. 🙂
Here, around 500 words in, we get to the meat of our post. How do we keep new lines when copying in SQL Server?
Tools | Options | Query Results | Results to Grid | Retain CR/LF on copy or save
Two things need to be done here.
New window open, we run our script again, and this time, when we copy and paste the results, we get…
So if you are using T-SQL to create scripts, and you’re having this problem with
GO or just new lines in general, make sure that the “retain CR/LF on copy and save” checkbox is ticked.
Now, improve that script more, throw it in a stored procedure, and you never know, it may be semi-useful. 🙂
It’s T-SQL Tuesday time! T-SQL Tuesday time! T-SQL Tuesday, T-SQL Tuesday, T-SQL Tuesday and a baseball bat!
Ahem…sorry about that…
Technology has changed a lot in the past years, especially with cloud/globalization/automation. What an impact has this had on your job? Do you feel endangered? Or do you have more exciting features/toys to work with? Do you embrace the change and learn new skills? Or do you hide in your cubicle and fear the robot uprising?
Now my knowledge of these subjects are somewhat limited; I have yet to research the cloud beyond a rudimentary “poking it with a stick” so I leave that topic to people like Arun Sirpal ( blog | twitter ) and others.
Globalization is another new topic for me. I consider it to be along the lines of being able to fill in positions with people all around the globe (but I’m probably wrong with this). There is a veritable plethora of people online that are excelling at this, so I leave it up to you to seek them out.
Automation…well I have some dealings of this, so I was considering this to be my topic. Then I realised that I could write about something along the same lines. Out-sourcing, but not in the conventional sense. More like…
There’s never enough hours in the day for everything I need to do!
How many times have we heard a complaint similar to that? Especially now-a-days when DBAs are tasked to look after more and more servers and instances. I cannot remember the last time I heard of a DBA taking care of servers in the single digits.
The work of the DBA keeps increasing but the amount of time that we have remains the same. How do we combat this? How do we make it so we are not sprinting just to keep up?
The only answer I have to this problem is this.
Don’t try to re-invent the wheel…let someone else do it.
The SQL community has been hard at work creating awesome content for everyone to use.
A short list of the top of my head include:
This doesn’t include the tools available from companies like
And have you seen some of the scripts created by individual members of the SQL community?
Hmm, maybe this won’t be as short a list as I thought…
You know what this blog post needs? More links!
Can I stop now? NO!!! MOAR LINKS!!!
And with Microsoft’s advancements with SQL Server and PowerShell, porting it to work with Linux and with Azure, it is like Microsoft are actively trying to get you to automate; actively trying to make your life easier!
So yes, technology has changed but we, as a SQL community, have worked so that you can use these tools, automate away the drudgery, and embrace these changes.
As long as you embrace this “inline out-sourcing” that we have.
Now I could wax lyrical about all the different aspects of the community and how, with these resources, you shouldn’t need to re-invent the wheel but I’m not going to.
These people have poured hours of effort into these scripts all for you to use. They have poured their heart, souls, and RAM into these scripts just so that they can help out the community.
I would ask a favour of people reading this post though. For all the time, effort, and sacrifice that these people have put in…
It means more than you know.