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 closedDevang Shah and Surya Teja Josyula do some analysis:
The below screenshot shows the results of a load test conducted on ADX using Grafana k6. This load test included 10 different queries that were concurrently sent to ADX for a duration of 3 mins generating a total request volume of 2144 requests, nearly 12 requests per second. P95 response time from ADX was 2.38 seconds which was well within the desired performance measure of the customer.
Read on to learn more.
Comments closedAs I sit here on October 7, 2022 watching PostgreSQL friends from all around the globe post contributions to the first ever PGSQL Phriday blogging event, I’m honestly pretty shocked… and very (very, very, very) grateful! While I have lots of ideas and love connecting with people to hear their stories, I wasn’t sure what to expect because, let’s face it, there are so many demands for our time and attention.
The fact that many folks have been supportive of this idea and contributed to this first event truly warms my heart. Thank you each for helping to get this ball rolling!
Click through for the all of the responses. And it’s good to see this getting picked up on the Postgres side.
Comments closedMarco Russo and Alberto Ferrari aren’t satisfied with a single date:
The best practice when we have multiple dates is to create a single, shared Date table and to connect it to all the date columns with different relationships. When a table has more than one date column, only one relationship can be active while the other relationships are inactive. In our sample model, the Date table connects both Sales[Order Date] and Sales[Delivery Date] with two relationships: one is active (with Order Date) and one is inactive (with Delivery Date).
Read on for a couple of options and what they mean for your visuals.
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 Cain needs more than two paths for branching logic:
In my previous post Fun With KQL – IIF, we saw how to use the Kusto
iiffunction to check for a condition then perform an action based on the result of a condition.What if you had multiple conditions you need to check? While you could string multiple
iiffunctions together there’s better solution: the KQLcasefunction.
Robert includes several examples, as well as a check of whether KQL does short circuiting or not.
Comments closedRobert 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 closedDaniel Hutmacher noticed a problem:
As I was performance tuning a query, I found that a number of date calculation functions in SQL Server appear to be forcing a conversion of their date parameters to a specific datatype, adding computational work to a query that uses them. In programming terms, it seems that these functions do not have “overloads”, i.e. different code paths depending on the incoming datatype.
So let’s take a closer look at how this manifests itself.
Some of these results are pretty annoying, especially because I like to use DATETIME2(0) for the large majority of development work