A Fine Slice Of SQL Server
Deepthi Goguri re-shares some helpful scripts:
For the past couple of years as a DBA, I migrated several databases and used many handy scripts that helped me made my work easier. These scripts may be simple but if you have a migration project involving several SQL Servers with some hundreds of databases, test and production database migrations becomes tedious. I would like to share some of then here which you might already known them very well.
Click through for three scripts.
Comments closedChad Callihan has a few close-to-one-liners:
The most common, short script I could think of may very well be one of the shortest T-SQL scripts that can be executed. When you want to know what version of SQL Server you are connected to, you can run this script:
Read on for that one-liner as well as a few other short scripts.
Comments closedMala Mahadevan has a few short-ish Query Store scripts for us:
I use Query Store a lot where I work – and I’d like to share queries I use on Query Store DMVs that I find incredibly useful.
My favorite is one below, which I use to see stored procedure duration. It comes with other information including plan id, start and end time – all of us help me see red flags right away if there is any query not performing as well as it should.
Click through for that script as well as two more.
Comments closedRob Farley has a quick script to find references in SQL Server:
As a consultant, the kind of work that I do from customer to customer can change a bit. Sometimes I’m reviewing people’s environments; sometimes I’m performance tuning; sometimes I’m developing code or reports or cubes; sometimes I’m writing T-SQL, but it’s often DAX or PowerShell.
Click through for a quick script to search modules for a particular string.
Comments closedDespite the many metric tons of blog posts warning people about this stuff, I still see many local variables and optimize for unknown hints. As a solution to parameter sniffing, it’s probably the best choice 1/1000th of the time. I still end up having to fix the other 999/1000 times, though.
In this post, I want to show you how using either optimize for unknown or local variables makes my job — and the job of anyone trying to fix this stuff — harder than it should be.
Click through for two methods, both of which end up being the wrong answer.
Comments closedChad Baldwin wants to check if rows exist before inserting:
I haven’t posted in a while, so I thought I would throw a quick one together to hopefully restart the habit of writing and posting on a regular basis.
One of my first blog posts covered how to only update rows that changed. In that post, I described a popular method that uses
EXISTSandEXCEPTto find rows that had changed while also implicitly handlingNULLvalues.
Click through for two types of technique, one for non-nullable data and one which can include NULL.
Comments closedKenneth Fisher takes us through a case of deleting rows:
Deleting rows from a table is a pretty simple task right? Not always. Foreign keys, while providing a ton of benefits, do make deletes a bit more complicated.
Click through for an example of this, as well as a quick discussion of cascading deletes, which sound really useful until you make a big mistake. The other problem with cascading deletes is, even if you do intend to delete everything noted, the process is a lot slower than what you can do in batches, and you’re liable to increase the size of your transaction log file to boot.
Comments closedKevin Wilkie is on the top shelf:
In the last blog post, we went over the extreme basics of using the TOP operator in SQL. We showed how to grab things like the TOP 10 of a certain item.
That ability will get you through a number of criteria that you will be asked to perform. But what if you’re asked to grab the top five percent of performers in your company? Or in a region? It’s kinda hard to do that if you only have what we know so far, right?
Read on for the answer.
Comments closedErik Darling has a bone to pick with STRING_AGG():
If you’re like me and you got excited by the induction of STRING_AGG into the T-SQL Lexicon because of all the code odd-balling it would replace, you were likely also promptly disappointed for a few reasons.
Read on for one post which covers all of those reasons. Even with that disappointment, I’m still happy with STRING_AGG() on the whole, myself. There are some extra steps it’d be nice to eliminate in certain circumstances, but 60% of the time, it works every time.
John McCormack lays out the parameters for this T-SQL Tuesday:
T-SQL Tuesday this month is going back to basics and its all about code. I’d like to know “What are your go to handy short scripts”?
What are those little short bits of code that you can’t live without? I’m talking about little snippets that are only a few lines, that you may even have memorised. It could be T-SQL, PowerShell, Python or anything else you use day to day.
Click through for two of John’s.
Comments closed