Last week, I asked for your worst database horror stories. Hoowee, did you deliver. Make yourself a nice cup of cocoa, gather around the campfire, and listen up for these terrible tales. Illustrations courtesy of Swear Trek (not safe for work.) The mistake we've all made Russ did an update without a where clause, but…
At SQL Intersection in Orlando last week, a few of us were sitting around a table talking shop. One thing led to another, and as so often happens at conferences, we started talking about our worst scars from database misfortunes. That got me thinking - I could kinda bring that fun experience to y'all here…
When I was thinking about this site's 17th birthday, I started wondering: back in 2002, what bug might I have been working with in SQL Server?
I started reading back to the list of bugs fixed in SQL Server 2000 Service Pack 1, and it hit me...these bugs aren't all that different than the ones we're facing today.
Raise Your Hand If
You've ever wanted to play a prank on your co-workers, but just didn't have a any ideas that didn't involve exploding Hot Pockets.
Now you have something even less safe than molten cheese squirts!
Presented without comment:
[crayon-6af2b741229c8792100733/]
Next up, can you break up a query with spaces? Yep:
[crayon-6af2b741229d5817065263/]
Well if you can do that - can you break up a query across lines? Sure you can:
[crayon-6af2b741229d8830653873/]
And now, brace yourself: this one is so weird that I can't even embed it in the blog. I'm just going to show you a picture of it first:
Playing The Odds
Assuming that life carries on as usual for a couple more weeks, I'll have been a consultant here for three years.
In that time, I've had some really interesting conversations with people, mostly about why things are the way they are on their servers.
Oh, XML
When we first met, it was about 2011. I had to load files full of you into a table. It wasn't so bad.
You were well-formed, and didn't change from file to file. Even using SQL Server 2005, I could take you.
Today's Office Hours is brought to you by NigelDBA.com:
https://www.youtube.com/watch?v=ZWNxWbKo1Q4&feature=youtu.be
So, uh, you can use WAITFOR in a logon trigger:
[crayon-6af2b74123379784331614/]
You probably don't want it to be TOO long, lest their app report it as a connection timeout. You want it just slow enough to make them angry.
I mean, theoretically. I would certainly never do anything like this. And I'm certainly not as diabolical as Dave Dustin:
For this month's T-SQLTuesday, Aaron Bertrand asked what we're passionate about - but outside of the tech community, and asked for pictures. Seems like a great way to get to know about us.
From my personal blog and my Instagram feed, you probably already know that I'm into cars, travel, and food, so I wanted to pick something different to share with you.
Say you've got an application that insists on inserting data into the database, and...you don't want the data.
You want the application to THINK it inserted the data - you don't want to roll it back or return an error to the end user. You just don't want the data, and you don't want the hassle of deleting it later.
Do you ever wonder about the history of the systems you rely on every day?
They didn't teach us database history in school. Instead, we got calculus and art and interpretive dance. Fat lot of good that did us, right?
File this under bad idea thong
In the tradition of Klaus Aschenbrenner wanting to prevent people from writing SELECT * queries, I thought it might be nice to prevent people from running deletes or updates without a WHERE clause.
In a vaguely scientific way.
Even error messages:
[crayon-6af2b74125ed7525428717/]
Produces:
These sound better than anything I would ever write in a romantic letter:
I need to start this blog post with a disclaimer: you should not be doing this. Hell, *I* should not be doing this. But when SQL Server 2017 came out Monday, I noticed a few unusual SNAPSHOT_MATERIALIZATION entries in sys.messages and a few related undocumented system objects that hinted about a new feature Microsoft hadn't…
True story
You may find it hard to believe, but I recently had to fix a small bug in sp_BlitzCache and sp_BlitzQueryStore.
Since both stored procedures have similar functions, they also share some temp table names (mainly the ones for parsing down XML nodes to more manageable chunks).
Sometimes I need to reset stuff during performance training classes. I know some of you teach classes, too, and some of you just like doing crazy stuff. So here you go, a stored procedure to lose weight fast: DropIndexes for SQL Server 2016 & Newer [crayon-6af2b7412b4d0353821713/] DropIndexes for SQL Server 2008-2014 This one doesn't include…
Guess who
I'm going to give you three queries, and you have to guess what the output will be before you run them.
Here they are:
[crayon-6af2b7412c92d413939817/]
Go ahead
Feel free to sub in the compatibility level that your databases are actually in before running these on your own server.
SQL Server has three ways to get execution plans from the plan cache:
sys.dm_exec_query_plan - around since SQL Server 2005
sys.dm_exec_text_query_plan - added in 2005 SP2
sys.query_store_plan - new in SQL Server 2016
I hate that you can do this
Here's the thing: I'm mostly writing this because I didn't know you could do it.
But it's cool, because it'll reinforce some other concepts, and I'll show you why you shouldn't do it.
