A Fine Slice Of SQL Server
Haripriya Naidu runs an experiment:
Instead of looking at which option is faster, you may want to look at which option is better suited for a given context. Let’s take a look at 2 common options to insert data and analyze them.
INSERT INTO TARGETTABLE SELECT * FROM SOURCETABLE OR SELECT * INTO TARGETTABLE FROM SOURCETABLE
Click through for a comparison of the two, not just for which is faster but also the pros and cons of each approach.
Comments closedMehdi Ghapanvari explains why TABLOCK can be useful for bulk inserts:
Does bulk insert performance improve when you use the TABLOCK hint? In some cases, YES! Let’s take a look at this in action to see how this hint could improve insert performance when using SQL bulk insert with TABLOCK.
Of course, standard TABLOCK concurrency rules (specifically, the lack of concurrency) apply.
Comments closedGrant Fritchey looks for an answer:
Let’s face it, most of the time, you probably don’t want your SQL Server execution plans to go parallel. After all, that’s why, instead of adjusting the Cost Threshold for Parallelism (as you should have), you’ve set the Max Degree of Parallelism to 1 (I see you out there). However, some of you recognize that, in fact, some queries need to run in Parallel.
Yet, sometimes, a query you think should run in parallel doesn’t. Can you tell why a plan didn’t go parallel?
Read on to learn how.
Comments closedBrent Ozar breaks the bad news:
Regular expressions are a way of doing complex string searches. They can be really useful, but they have a reputation: they’re hard to write, hard to read, and they’re even harder to troubleshoot. Once you master ’em, though, they come in handy for very specific situations.
This post isn’t about their complexity, though. This post is about Azure SQL DB & SQL Server 2025’s regex performance.
Brent’s testing hurts, because I want to use regular expressions, and based on what he’s seen so far, we’re probably still better off using CLR-based regex in SQLSharp.
Comments closedIn this video, I demonstrate how order of operations matters when it comes to casting or converting a data type and performing an aggregation on that result. I’ll use the specific example of converting binary data to a number and show where the fast version might lead you astray.
This is something pretty easy to miss, especially when the code returns fast enough. But over a large enough number of calls, these sorts of things add up, as I note in the video.
Comments closedSteve Jones performs a pair of tests:
I had someone reach out about generate_series() recently, saying they hadn’t realized this was a new feature in SQL Server 2022. They were wondering if it was better than using a tally table.
I didn’t want to do an exhaustive test, but I thought I’d take a minute and try a couple simple things just to see.
Steve used the CTE-based tally table builder, building based on cross joining spt_values. This is one of the classic approaches. The performance differences aren’t enough on their own to justify large-scale changes if you’re using a classical tally table, though it is good to see that GENERATE_SERIES() does perform well. And if you’re not familiar with the power of a tally table, here is one great explanation of the concept.
MIchael Christofides views a plan:
When you’ve got a slow Postgres query, EXPLAIN and its parameters are incredibly useful for working out why.
However, the information returned can be difficult and time-consuming to interpret, especially for more complex queries. Over the years, people have built quite a few tools for visualizing Postgres query plans. As one of those people, I’m
a littleincredibly biased, but as a fan of many of the others, I hope to do them justice.
Read on for the options. I think the SQL Server world is a bit spoiled with Solarwinds Plan Explorer (even if the product hasn’t really changed since Solarwinds bought Sentry One), but some of these options look very solid.
Comments closedHaripriya Naidu explains SARGability:
Having the right index is helpful, but are you using the predicate (
WHEREclause) correctly to make efficient use of that index?This is where the term SARGable comes into play. SARGable stands for Search ARGumentable. If SQL Server is able to limit the search space while evaluating the predicates and can seek right at the page(s) to get the values, then it is SARGable.
Read on for an explanation of why this is important, as well as several examples of what is SARGable versus what isn’t. The most important thing about SARGability is that you pronounce it like “Sarge” and not “sarg.”
Comments closedBrent Ozar busts out the calculator:
You’re tuning a Postgres query and you’ve been told to run EXPLAIN with the BUFFERS option, like this:
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)But the output like this is confusing, because the numbers don’t add up vertically or horizontally. It’s just a whole mess of numbers that seem unrelated:
Read on to learn more about which numbers to pay attention to and what they all mean.
Comments closedRadim Marek shows off how the PostgreSQL database engine can inline a view:
Database VIEWs are powerful tools that often don’t get the attention they deserve when building database-driven applications. They make our database work easier in several ways:
- They let us reuse common query patterns instead of writing them over and over
- They give us a place to define business rules once and use them everywhere
- They help us write cleaner, more organized queries
Let’s see how this works with a practical example.
I’m not sure how well PostgreSQL manages nested views, as the biggest problem in SQL Server with inlining views comes when you have overly complicated views made up of views joined to views made up of views joined to–well, you get the idea. That said, Radim does point out a variety of scenarios in which inlining doesn’t happen in PostgreSQL.
Comments closed