A Fine Slice Of SQL Server
Erik Darling theorizes about strings:
If you write the good kind of dynamic SQL, that is:
1. Parameterized
2. Executed with sp_executesql
You’ll probably have run into some silly-ish errors in the practice. Namely, that sp_executesql expects your SQL string and your Parameter string to be NVARCHAR(…).
Read on for some thoughts on data types, max-ness, and the like.
Comments closedRichard Swinbank shows off one of the more common uses of dynamic SQL:
Now you come to ingest your second table – and you have to do this all again!
It’s a lot of code, and worse, it’s all basically the same – only the names and types are any different. You can easily make a mistake, and the boredom of repetition only makes that more likely. Later on, when you decide you want to add something to the process – a logging statement at the end of the stored proc, for example – you’ll have to add it separately to every stored procedure you’ve created.
The solution? Generate your code automatically, from metadata, using dynamic SQL!
Read on for what you’d need to pull this off.
Comments closedMurder Forrest McDaniel Wrote:
“A duplicate despite a DISTINCT bodyguard…” one mutters. “This isn’t the normal level of foul play.”
Mods arrive to cordon off the scene. Twelve different butlers have already been accused, but each has a solid alibi, even MAXDOP.
Generic Protagonist paces back and forth. They’re waiting for the forensics report. Finally it arrives.
Of course, those forensics people don’t know what they’re doing, so you’d better click through and find the real killer.
Comments closedPatrick 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 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 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 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.