A Fine Slice Of SQL Server
Daniel 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 closedBrendan Tierney wants rowcounts:
A little warning before using these queries. They may or may not give the true accurate number of records in the tables. These examples illustrate extracting the number of records from the data dictionaries of the databases. This is dependent on background processes being run to gather this information. These background processes run from time to time, anything from a few minutes to many tens of minutes. So, these results are good indication of the number of records in each table.
Click through for examples in Oracle, MySQL, Postgres, SQL Server, and Snowflake. Though the SQL Server one does need a GROUP BY clause because it’s a sum of the partitions’ rows.
Comments closedFar and away, some of the trickiest situations I run into when helping clients is rewriting scalar functions that have WHILE loops in them.
This sort of procedural code is often difficult, but not impossible, to replace with set-based logic.
Erik improves a function in this post, though often, the best way to improve a function is not to play the game at all.
Comments closedChad Callihan neeeds to make an update:
Have you ever ran into a situation where you needed to replace both ends of a string? Maybe it was tags in a set of XML values or phrases at the beginning and end. I came across that situation recently and was able to show if two statements were needed or if REPLACE could be used against the same column in the same statement. Let’s look at a quick example and see what’s possible.
Chad’s solution is one I use fairly frequently. I agree that it’s not pretty, though one thing I like to do is tokenize code, so I might have something that looks like:
-- Procedure input parameters here
@FirstName NVARCHAR(150),
@FavoriteColor NVARCHAR(30)
-- Guts of procedure here
DECLARE @msg NVARCHAR(4000) = N'Hello, {FIRST_NAME}! Your favorite color is {FAVORITE_COLOR}!';
SELECT
REPLACE(REPLACE(@msg,
'{FIRST_NAME}', @FirstName),
'{FAVORITE_COLOR}, @FavoriteColor);
It’s definitely not the prettiest but when you have several tokens to replace, it’s a lot easier to read than building the string all at once.
Comments closed