A Fine Slice Of SQL Server
Patrick LeBlanc learns about string concatenation in Spark SQL:
Using PySpark in Azure Synapse Analytics and found that my regular way of concatenating two strings in T-SQL didn’t work. I figured out what you need to do to make this work.
Click through for a short video.
Comments closedAaron Bertrand provides a warning around dynamic SQL:
For October’s T-SQL Tuesday, Steve Jones asks us to talk about ways we’ve used dynamic SQL to solve problems. Dynamic SQL gets a bad rap simply because, like a lot of tools, it can be abused. It doesn’t help that a lot of code samples out there show that “good enough” doesn’t meet the bar most of us have, especially in terms of security.
In a series I started last year, I talked about ways to do <X> to every <Y> inside a database, focusing on the example of refreshing every view (in a single database or across all databases). I already touched on what I want to dig into today: that it can be dangerous to try to parameterize things that can’t be parameterized in the ways people typically try.
Read the whole thing. I do find it funny how often people aren’t allowed to install well-known, third-party stored procedures (like Aaron’s sp_ineachdb) but it’s perfectly okay to write terrible code which is vulnerable to exploit because it was written in-house and is therefore more trustworthy somehow.
I don’t want to dunk on security teams too much in this regard, as I understand and really do appreciate the principle, though it often has counterintuitive first- and second-order consequences.
Comments closedJared Poche continues a series on join types:
Merge joins traverse both inputs once, advancing a row at a time and comparing the values from each input. Since they are in the same order, this is very efficient. We don’t have to pay the cost to create a hash table, and we don’t have the much larger number of index seeks nested loops would encounter.
Read the whole thing. Remember: merge joins are also the best strategy for when two lanes of the road come together.
Comments closedReitse Eskens shows off a good use case for window functions:
Last week, one of my coworkers had a nice case. From a dataset, he needed the third row based on an indicator and a timestamp. That sounded a bit vague so I started out with creating a table that contained some records with the columns he’d have to filter on.
This turns out to be an ideal use of the ROW_NUMBER() function.
Robert Sheldon takes us through data insertion in MySQL:
In most cases, adding a single row of data to table is a reasonably straightforward process. You define the
INSERTclause andVALUESclause and usually specify the column list in between. The column list should include only those columns for which you provide values. The list can include the primary key column, generated columns, or columns defined with default constraints, but you must be careful how you handle them, as you’ll see later in the article.
This is definitely aimed at people new to MySQL and SQL in general.
Comments closedTom Zika continues the 12-step program for scalar function abusers:
In the previous posts, we have learned why Scalar Functions (UDFs) are bad for parallelism and performance and what the options are for their removal.
The only remaining question is where to start. You’ve probably guessed there isn’t one true way to approach this. So instead, I offer several strategies that you can combine.
Plus Tom has an offer for you at the bottom of the post: conversion from Scalar UDFs to Inline Table-Valued Functions, which tend to perform much better.
Comments closedEmily Tehrani notes an addition to Azure Synapse Analytics:
We are thrilled to announce that the MERGE T-SQL command for Azure Synapse Dedicated SQL pools is now Generally Available! MERGE has been a highly requested addition to the Synapse T-SQL library that encapsulates INSERTs/UPDATEs/DELETEs into a single statement, drastically easing migrations and making table synchronization a breeze.
If you do decide to use this, I’d expect it to have the same bugs which make its use on-premises a mess. As always, MERGE responsibly.
Comments closedDavid Fowler understand the order of things:
This was an interesting question that I was asked yesterday and something that I’d never really thought of before. Can you delete the top x number of rows based on an ORDER BY?
Why would you want to do that? Well let’s just assume that we have a ‘people’ table and some strange bug in the application has cause the need to delete the top 10 oldest females for whatever reason (I know, it’s very contrived example and not the situation that my colleague was facing but it’ll do to illustrate the point).
Click through for one way which doesn’t work and two ways which do.
Comments closedLouis Davidson likes a new operator in SQL Server 2022:
The MOST exciting change from a T-SQL standpoint is:
IS NOT DISTINCT FROM. This feature solves an age-old issue for T-SQL programmers and is worth its weight in gold. It is basically an equals comparison operator like =, but treatsNULLas an individual value. Unlike =, this new operator returns onlyTRUEorFALSE, but notUNKNOWN. Writing queries that compare to values that can containNULLis tedious, mostly because of code like the following:
Louis is quite happy here. I like the fact that the syntax is here, though I’d be concerned about performance—the syntax is nicer but you can run into the same performance issues as you’d have with “NULL or match” type queries.
Comments closedRob Farley like a syntax change:
This was fine, but it did start to become a little cumbersome.
Enter SQL Server 2022. Not only do we get the ability to ignore nulls now, making it easy to get the last non-null value from a list, but we also get a WINDOW clause – part of the SELECT query itself, dropping in between the HAVING clause and the ORDER BY clause, allowing us to predefine those OVER clause segments.
Read on for the full scope of Rob’s thoughts.
Comments closed