A Fine Slice Of SQL Server
Kevin Wilkie tries out a new operator:
In a recent Snowflake release, a slick new operator quietly entered the scene: ->>. This little guy can make certain query workflows both more readable and more efficient—especially when you’re dealing with multi-step commands like
SHOW,LIST, orDESCRIBE.
Click through to see how it works. Seems that this operator has some pretty strict limitations, but for certain use cases, it’s quite nice.
Comments closedEd Pollack digs into some new functionality:
String-searching in SQL Server has always been a mighty hassle. Balancing performance and horribly-complex queries is a compromise that no one enjoys.
Generally speaking, a relational database is not an ideal place to search large amounts of text. Even when leveraging features such as Full-Text Indexing, the ability for an application to leverage speedy text-searching decreases as data becomes larger. If a service optimized for text-search can be used, such as Elasticsearch or Azure AI Search, then it will be far easier to deliver accurate results quickly.
Ed focuses on the mechanisms available rather than performance, and that’s the current sticking point. Whether regular expression queries will get faster in subsequent CTPs or SQL Server 2025 RTM, we’ll see.
Comments closedMarco Russo and Alberto Ferrari compare two keywords:
When you begin modelling in DAX, DISTINCT and VALUES often appear interchangeable: both return the list of unique values for a column in the current filter context. In a clean development model, they behave the same, so it is easy to pick one at random – or worse, swap between them without thinking.
However, they are not identical. The subtle difference is crucial in production models that may one day contain invalid relationships or bad data.
Read on to see how each works and how they differ in practice.
Comments closedIn this video, I show how we can use the APPLY operator to reshape datasets, allowing us to unpivot tables and also calculate the greatest and least values for a row.
If you look closely at the scripts, you’ll see 08 and 10. In the source control repo, I also have a script 09 that covers splitting strings. Using APPLY to split strings has always been a bit of a niche case, but prior to SQL Server 2016’s introduction of STRING_SPLIT() and SQL Server 2022’s improvement of the function, I could make the case that it sometimes made sense to know how to split strings via APPLY. Today, not so much, which is why I tossed that demo from the video.
Hans-Jürgen Schönig combines two features:
Regular expressions and PostgreSQL have been a great team for many many years. The same is true for PostgreSQL arrays, which have been around for a long time as well. However, what people rarely do is combine those two technologies into something more powerful that can be used for various purposes.
Click through for the demonstration.
Comments closedIan Parker shows off some meta-commands:
If you manage PostgreSQL from a terminal you already know
psql, the interactive client that ships with every installation. Most developers use it for the basics—runningSELECTstatements, loading a.sqlfile, maybe poking around with\dtto see which tables exist.Beneath that familiar surface, though,
psqlhides a rich toolbox of meta-commands. These commands, all prefixed with a backslash, live inside the client. They’re not SQL, they’re shortcuts built intopsqlitself, and they can make everyday tasks faster and far less error-prone.
Read on for six of these, including examples like \watch to view something with periodic refresh.
Erik Darling talks about one of my favorite T-SQL features.
As usual, Erik leaves me hanging with respect to the lack of description or snippet I can use as a graf for enticing my wonderful audience to watch his video. Thus, I have to come up with my own. Erik’s video is actually a really good companion piece to my video that also dropped this week, as we both cover the same general concept.
Comments closedLouis Davidson gives it the college try:
When I was perusing my LinkedIn feed the other day, I came across this thread about using SELECT *. In one of the replies, Aaron Cutshall noted that: “Another real performance killer is
SELECT DISTINCTespecially when combined withUNION. I have a whole list of commonly used hidden performance killers!”To which started my brain thinking… What does happen when you use these together? And when you use UNION on a set with non-distinct rows, what happens. So for the next few hours I started writing.
Read on for Louis’s findings.
Comments closedAnthony Nocentino cuts out the middleman:
In this post, I’ll walk you through a T-SQL script that creates application-consistent snapshots on Pure Storage FlashArray, all from within SQL Server, no external tooling. SQL Server 2025 introduces a powerful new feature: the
sp_invoke_external_rest_endpointstored procedure. This enhancement makes calling REST APIs directly from T-SQL easier than ever. Combining this new capability with Pure Storage’s API allows us to orchestrate snapshot operations seamlessly, with no external tools or scripts required.
Click through for the process. I know that sp_invoke_external_rest_endpoint will be controversial for DBAs. That’s why I think it’s good to have examples of how it can be useful before the knee-jerk reaction of “this is automatically bad” takes over.
In this video, I show how we can use the APPLY operator to operate on ad hoc functions. That leads to a powerful use case: pre-aggregating data.
Every once in a while, this tip will save a considerable amount of CPU time and database effort.
Comments closed