Time to read: ~3.5 minutes
When the answer isn’t just SARGabilty…
When the answer isn’t just SARGabilty…
Time to read: ~3.5 minutes
Ah T-SQL Tuesday, is it that time again? And the 94th one at that! Wow!
Time to read: ~4 minutes
Update: 2017-09-20 Thanks to Rick Fraser for pointing out I showed a $ServerConnection but hadn’t defined it in the function or separately or at all! Thanks Rick!
An ode to a knowledge seeker
Time to read: ~ 2 minutes
tl;dr : Give it an alias! ( select ‘0’ AS [Zero]; )
I realised I hadn’t done a blog post this week and didn’t think I had anything planned, so here is a random PowerShell/SQL Server encounter on Twitter. Hope you enjoy
Recently a question came up on the #sqlhelp hashtag on Twitter asking about a problem that a user was having with using .Contains with an array.
Normally when I see a question regarding PowerShell, Arrays, and Contains I keep an eye on them, not to answer them but to read the responses and learn from them.
However, this one caught my eye for two reasons; it had an image with
Invoke-Sqlcmd in it , and it was on the #sqlhelp hashtag. So I said let’s see if I can help out here.
… was if you have a table like below…
and you are running the following PowerShell command to check if the results contain a value…
$String = "abc" $Array = @(Invoke-Sqlcmd -ServerInstance "SQLServer" -Database "Database" -Query "SELECT code FROM dbo.users") $Array.Contains($string)
It will return
Now we know that the
FALSE is false because we know that the string is in there!
This code is proven to work with arrays as stated here by the “Hey, Scripting Guy!”s so this was getting filed under “WTF PowerShell”
… and I’ve done the same so I can’t blame them, was they failed to use
Get-Member; they made assumptions (bad idea)
If they had run
$array | gm, they would have seen that the $array is not an array but a
System.Data.DataRow, and we’ve seen them before.
$array | gm and seeing that there was no method called
So I was going down the route of using a mix of
This wouldn’t have been great though as we would have to iterate over every single row and seeing if the value existed. I assumed that the reason the questioner wanted to use
.Contains was to get around iterating over every single row, so this wasn’t going to work either.
The Questioner should have used
$Array | gm and I, knowing the next step, should have used
$Array.code | gm
It’s nice to see the way that NULLs are treated differently in PowerShell. 🙂
If we drop down from the DataRow into the property of the DataRow, it becomes a string! Perfect because the string contains the method
…and we have our
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 🙂
SQL New Blogger: Time to investigate: 10 mins Time to test: 10 mins Time to write: 10 mins
Now this isn’t a problem of itself, it can be easily achieved by using a
CREATE TABLE statement and then using 2
ALTER TABLE statements to create the constraints.
The problem that I had with this was that, so far, I was going along and creating these tables & columns with the constraints created in-line and it just galled me to have to break this flow and create these constraints as
Checking the examples in the new Microsoft Docs didn’t show any examples that I could find of creating both constraints together on the same column so I experimented and found out that you can!
Notice 2 things here:
FOR <column name>on the default constraint because SQL Server can tell the constraint is to work on the column it is currently defining.
Great, I can keep my constraints inline!
Documentation is useful but they do not cover every situation. Have a test environment; Hypothesize, test, and verify. You never know what you’d find.