A Fine Slice Of SQL Server
Andy Brownsword uses the QUOTENAME() function:
I’m a big fan of dynamic SQL in the right conditions. One key to crafting safe dynamic query of the use of the
QUOTENAMEfunction.
My personal preference is to use exec sp_executesql for parameterization instead, simply because it’s a lot harder to mess up.
Erik Darling shares some advice:
I’ll be brief here, and let you know exactly when I’ll use IN and NOT IN rather than anything else:
- When I have a list of literal values
That’s it. That’s all. If I have to go looking in another table for anything, I use either EXISTS or NOT EXISTS. The syntax just feels better to me, and I don’t have to worry about getting stupid errors about subqueries returning more than one value.
I’m typically a lot more flexible about using IN, though I do agree with NOT IN: that clause is usually more trouble than it’s worth.
Erik Darling gets an auto-link for talking about my favorite operator:
I end up converting a lot of derived joins, particularly those that use windowing functions, to use the apply syntax. Sometimes good indexes are in place to support that, other times they need to be created to avoid an Eager Index Spool.
One of the most common questions I get is when developers should consider using apply over other join syntax.
The short answer is that I start mentally picturing the apply syntax being useful when:
To learn when, you’re going to have to read the whole thing. And, if you want to learn even more about it, I have a talk on the topic that might be of interest.
Comments closedAndy Brownsword breaks out the stopwatch:
The
TOPclause limits the number of results which are returned from a query, in this instance we’re focussing on a single result. In contrast, when usingMAXwe’re applying a function to our data to select the largest value from our data.Let’s dive into some examples with the StackOverflow data, specifically the Votes table.
Read on for several scenarios and how the two perform. Things get a bit more complicated as you introduce other tables in joins and similar additional factors, but this gives you a good foundation for comparison.
Comments closedBen Johnston eats the elephant:
One of the first things you learn when working with SQL Server, and other transactional based SQL systems, is that set based operations perform best. If you are querying data, a cursor pulling individual rows doesn’t perform as well as a single query. It doesn’t matter if that cursor is on the client side or the server side. A set-based operation is more efficient, runs faster, locks less, and is generally better than submitting multiple queries.
This is also generally true with delete statements. This post covers the exceptions to that rule. Large delete statements impacting many rows and large amounts of data (millions of rows and many gigs of data) can actually have decreased performance. With transactional systems, such as SQL Server, each transaction follows the ACID standard. Part of that standard ensures that transactional statements either complete or roll back fully – partial transactions are not allowed. For a delete statement, that means that all of the rows specified by the delete are removed from the table, or none are removed and the data rolls back to the original state. The delete and rollback behavior must be predictable and consistent or the data could be left in a contaminated, unreliable state. Performing very large deletes can present some challenges and needs to be treated differently in production systems.
Read on for the reasoning behind this, as well as several techniques you can use and how they compare.
Comments closedErik Darling plays Descartes in his spare time:
Let’s talk about two of the most overlooked and undervalued facilities in the SQL language: EXISTS and NOT EXISTS. Perhaps they’d get more traction is they were called THERE and NOT THERE, but but it would be perilously easy to start confusing your WHERE and THERE clause.
Often besmirched by someone who read a blog post about MySQL in 1998 as “subqueries, which are slower than joins”, they’re two of the most useful things you can grasp and use regularly.
This post does a great job of explaining when you’d want to use EXISTS and NOT EXISTS.
Ed Pollack deals with the lack of regular expressions in T-SQL:
This article is focused on how to write, simplify, and get the most out of string data using the least amount of code to do so. It’s a reminder that code complexity is equivalent to technical debt and that in these scenarios, less is always more.
Text-manipulating functions will be introduced and reviewed throughout this article, so feel free to perform searches against it to find what you are looking for.
Read on for mechanisms to split strings into multiple rows, build strings from multiple rows, and search+replace within strings.
Comments closedOne potential issue here is the lack of time to delve really deeply into each programming language at one’s disposal. The thing about SQL Server is that it is a language that performs very differently than, say, C#. Those languages are row based, which means it’s common to iterate through a data set and process each row individually. This works quite well in those languages, but quite terribly in SQL Server. Being a set-based language, SQL Server prefers to deal with the whole data set at once.
Because of these nuances, it’s easy for developers to make mistakes when creating SQL server code. Let’s go through some common errors that we see.
Read on for several common mistakes that we often find in our code.
Comments closedErik Darling digs into a problem. Part 1 sets up the scenario:
Here’s the query plan, which yes, you’re reading correctly, runs for ~23 seconds, fully joining both tables prior to doing the final aggregation.
I’m showing you a little extra here, because there are missing index requests that the optimizer asks for, but we’ll talk about those in tomorrow’s post.
The wait stats for this query, since it’s running in Batch Mode, are predictably HT-related.
Part 2 covers those missing indexes:
I’ve taken a small bit of artistic license with them.
The crappy thing is… They really do not help and in some cases things get substantially worse.
Maybe it’s because it’s early and I’m trying to compile things in my head rather than actually trying it out, but it seems like a combo of CTE + CROSS APPLY or a pair of CROSS APPLY statements could work better (especially with a good index), assuming that join doesn’t need to be in place. Given the query as it is, with two MAX() aggregations and no GROUP BY clause, that could be an avenue for improvement, though one I have not actually tested. Nonetheless, read both of Erik’s posts.
Steve Jones shows ERROR_MESSAGE()’s extended family:
I was working with a customer that was doing some error handling in procs and helped them do some error tracking. As we were working through things, I realized that some of functions working with errors operated differently than I expected.
Read on to learn more about these error functions and the proper way to use them.
Comments closed