A Fine Slice Of SQL Server
Josephine Bush busts out a linter:
I thought I didn’t care about linting, and lately, I haven’t written a lot of SQL, but for the SQL I do write, I have SQLFluff to help me format it. A friend of mine is big into SQLFluff and finally talked me into installing and using it. For more information about SQLFluff itself, visit here.
Josephine shows off some of the configuration for PostgreSQL’s psql as well as SQL Server’s T-SQL.
Comments closedTobias McNulty now has two problems:
regexp_matches()andregexp_match()are two similar string functions that support regular expression matching directly in the PostgreSQL database.regexp_matches()was added in PostgreSQL 8.3, andregexp_match()was added in PostgreSQL 10 (keep reading to see how ChatGPT struggled to answer this question).
Read on for that as well as plenty more information on how the two work, and even a bonus snippet on another regular expression function.
Comments closedIt’s not unreasonable to expect these two statements to produce the same execution plan on most RDBMS, given that they’re logically equivalent. Since we’ve grown used to reading things from left to right and top to bottom, I don’t think
RIGHT JOINwill become more popular any time soon.There is, however, one place in the SQL language where
RIGHT JOINis surprisingly ubiquitous!
Read on to see how the MERGE operator includes as part of its operations the equivalent of a RIGHT OUTER JOIN. MERGE in SQL Server had justifiably built a bad reputation in its early years, but it’s surprisingly okay nowadays, with most of the bugs being fixed over time.
Comments closedSteve Jones performs a pair of tests:
I had someone reach out about generate_series() recently, saying they hadn’t realized this was a new feature in SQL Server 2022. They were wondering if it was better than using a tally table.
I didn’t want to do an exhaustive test, but I thought I’d take a minute and try a couple simple things just to see.
Steve used the CTE-based tally table builder, building based on cross joining spt_values. This is one of the classic approaches. The performance differences aren’t enough on their own to justify large-scale changes if you’re using a classical tally table, though it is good to see that GENERATE_SERIES() does perform well. And if you’re not familiar with the power of a tally table, here is one great explanation of the concept.
Leo Hsu and Regina Obe look at updates to the random() function in PostgreSQL:
Have you ever wanted to get a random integer between 1 and 10 and been a little annoyed the slightly cryptic code you had to write in PostgreSQL? PostgreSQL 17 random functions make that simpler. Sometimes it’s the small changes that bring the most joy.
Click through to see what it took to get a random integer or floating point number prior to PostgreSQL 17 and how it’s a fair bit simpler today.
Comments closedVlad Drumea changes some settings:
You might have noticed when scripting out stored procedures in SSMS that it prepends two SET options before the CREATE PROCEDURE statement.
These are:
- ANSI_NULLS
- QUOTED_IDENTIFIER
Read on to learn more about how these behave and why it’s important to set them in a specific way.
Comments closedMonica Rathbun truncates some data:
SQL Server performance issues often stem from easy fix bottle necks that can be fixed with the right tuning strategies. This short blog will focus on the DELETE statement.
Click through for the full story. One minor correction that I’d offer is that the TRUNCATE TABLE command is logged, which is why you can roll it back in a transaction. The logging process is much less intensive than with DELETE because of the deferred drop logic that Paul talks about in the link.
That aside, Monica’s key point is absolutely correct: DELETE operations tend to be quite slow, especially as the number of records you need to delete increases. This is where techniques like batching delete operations can help reduce the pain level, but if you’re deleting every (or almost every) row from a table, there’s probably a better method. Unless replication is involved in the mix—in that case, there are no good methods for anything because replication hates us.
In this video, we will take a look at several useful KPIs for measuring sales. We will also demonstrate the utility of the APPLY operator, the WINDOW clause, and the APPROX_PERCENTILE_CONT() function.
I enjoyed putting this one together, because I enjoy it any time I have a chance to talk about the APPLY operator.
Comments closedSebastiao Pereira offers a fourth-best solution:
Regular expressions (REGEX) let you adaptively investigate, employ, and authenticate text data. This makes it easy to search for simple or complex string patters. There is no direct way to do this in SQL Server, but in this article we look at some SQL functions you can create to emulate regex like functionality.
Regular expressions are coming to SQL Server 2025 and are in Azure SQL Database, so that’s the best option when it becomes available. The second-best option is to use CLR and offload your regular expressions work to .NET, especially if you’re using a library like SQLSharp to do so. The third-best option would be to do this in Python or R with ML Services, though that’s going to be a bit of setup effort and will probably be somewhat limiting. And if all else fails, this is an admirable fallback.
Comments closedKoen Verbeek tries out a couple of fairly new functions:
I need to construct JSON from data in our database, but I find the existing FOR JSON PATH limited when the data is not located in one single row but rather scattered over multiple rows. Is there another method on how to handle JSON data in SQL Server? Learn how to use the new SQL Server JSON Functions JSON_OBJECTAGG and JSON_ARRAYAGG in this article.
These aren’t available on-premises yet, though given that there’s a new version of SQL Server coming out in 2025, there’s a good chance we’ll have it by then.
Comments closed