A Fine Slice Of SQL Server
Joe Obbish looks at time zones:
Queries that use the AT TIME ZONE operator may perform worse than desired. For example, Jonathan Kehayias blogged about performance struggles with AT TIME ZONE at sqlskills.com. One key difference between Jonathan’s opinion and my own is that I enjoy writing these kinds of posts.
Read on for the details and check out some work Joe has done around time zone friendly functions as an alternative to AT TIME ZONE.
Aaron Bertrand pulls out the list:
In a lot of programming languages, efficiency is almost always the guidepost. Sometimes, minimizing character count or line count is a “fool’s gold” measure of the quality of the code itself. Other times, unfortunately, engineers are judged not by quality at all, but rather the sheer number of lines of code produced – where more is, somehow, obviously better. Over my career, “how much code there is” has never been a very meaningful measure in any language.
But I’m here to talk about T-SQL, where certainly efficiency is a good thing to measure – though there are some caveats to that:
Read on for those caveats and what Aaron considers to be the hallmarks of high-quality code.
Comments closedGreg Larsen fills us in on an important command:
Over time data in SQL Server tables needs to be modified. There are two major different aspects of modifying data: updating and deleting. In my last article “Updating SQL Server Data” I discussed using the
UPDATEstatement to change data in existing rows of a SQL Server table. In this article I will be demonstrating how to use theDELETEstatement to remove rows from a SQL Server Table.
This stays pretty simple but provides an effective overview of how to keep those tables tidy.
Comments closedDaniel Hutmacher answers a question:
Can SQL Server piece together two different indexes in a single-table query, rather than just giving up and scanning a suboptimal clustered index? The short answer is: yes, in a fairly narrow band of conditions.
The actual answer is a lot more restrictive than you might initially think.
Comments closedI must say that per principle I’m not a big fan of neither computed columns nor scalar UDFs. I mean, I find them attractive in the way they (appear to) make “things simpler” also allowing code reuse, improving queries readability, etc. Yes but they also hide or mask the complexity behind their use, which can often be quite deceiving, making it much harder to troubleshoot and solve performance problems. Furthermore they have several limitations by design that can hurt performance and all this combined, can sometimes make a “simple” query take many minutes or hours to run, instead of just a few seconds! When you see this situation happen again and again while fine tuning databases, their use becomes much less appealing.
Having this said, sometimes they can be useful of course but it’s very important to choose carefully where, how and when to use computed columns and scalar UDFs, so that performance won’t get hurt and its benefits outweigh the drawbacks.
Click through for an example of where the combo really falls short. I do like computed columns, though never with user-defined functions.
Comments closedLukas Eder shows off one of my favorite operators:
The SQL:1999 standard specifies the
<lateral derived table>, which is SQL’s way of allowing for a derived table (a subquery in theFROMclause) to access all the lexically preceding objects in theFROMclause. It’s a bit weird in terms of syntax, I personally think that Microsoft SQL Server has a much nicer solution for this concept viaAPPLY. Oracle supports both syntaxes (standard and T-SQL’s). Db2, Firebird, MySQL, PostgreSQL only haveLATERAL.
Click through to see how the operator works.
Comments closedChad Callihan doesn’t make two calls:
Are you familiar with the OUTPUT clause in SQL Server? It can be used with commands like INSERT, UPDATE, DELETE, and MERGE. It can also be used with setting variables in stored procedures. Using the tried and true StackOverflow2013, we’ll narrow it down today to focus on how INSERT/DELETE are typically used for logging table changes as well as an example of how to use OUTPUT with stored procedures.
For really busy transactional systems, this provides a nice boost over making an update and then selecting the new values.
Comments closedBalmukund Lakhani has an announcement:
Approximate query processing was introduced to enable operations across large data sets where responsiveness is more critical than absolute precision. Approximate operations can be used effectively for scenarios such as KPI and telemetry dashboards, data science exploration, anomaly detection, and big data analysis and visualization. Approximate query processing family has enabled a new market of big data HTAP customer scenarios, including fast-performing dashboard and data science exploration requirements.
Today, we are announcing preview of native implementation of APPROX_PERCENTILE in Azure SQL Database and Azure SQL Managed Instance. This function will calculate the approximated value at a provided percentile from a distribution of numeric values.
This is way faster than using the PERCENTILE_CONT() or PERCENTILE_DISC() window functions. For a decent-sized query, I was getting anywhere from 5-20x performance improvements, and the larger the dataset, the bigger the gains. It is important to note that the approximate percentiles are not window functions, so you don’t get one row back per row of input.
Hasan Savran won’t settle for just one date:
GENERATE_SERIES function is introduced in SQL Server 2022 version. It is a simple function that generates a series of numbers within a given interval. It requires compatibility level 160.
The natural use for it is to generate a set of numbers, but as Hasan shows, you can easily turn that into a set of dates or dates and times.
Comments closedChad Callihan embraces the power of AND:
Data can be inserted into one temp table from another a couple of ways. There is the INSERT INTO option and the SELECT INTO option.
Are you devoted to one option over the other? Maybe you’re used to one and never experimented with the other. Let’s test each and compare performance to find out which is more efficient.
Both of these are useful, though Chad does mention a performance improvement with SELECT INTO. I tend to prefer INSERT INTO for “structured” scenarios because it lets me define the shape of the output table. When I don’t care what the shape is—for example, when I just need some data one time to perform an analysis—then I prefer SELECT INTO for its simplicity.
Comments closed