A Fine Slice Of SQL Server
Sebastiao Pereira implements a formula:
Runge-Kutta method it is an effective numerical interactive technique used to solve ordinary differential equations, used in physics, engineering, aerospace, ballistics, circuit simulation, epidemiology, chemical kinetics, games, and others. Is it possible to create in SQL Server without the use of external tools?
Click through for the solution.
Leave a CommentReitse Eskens troubleshoots an error:
I’ve declared a variable and assigned it a value. Now, I want to check if my variable matches the regular pattern of Dutch postal codes. These codes are four numbers followed by two letters. Using a regular expression helps check validity.
My expectation was that this query would return either 1 or TRUE. In any case, a result telling me that the postal code is valid. Instead, it throws an ‘Incorrect syntax error near the keyword ‘REGEXP_LIKE’.
Click through for the screenshot and the explanation.
Leave a CommentBrent Ozar has a new animation:
If we modify our query a little by selecting all of the columns instead of just Id and Location, then we have to do a Key Lookup, like we talked about in the How to Think Like the Engine class. For each person who lives in Helsinki, we have to look up their row in the clustered index in order to fetch all the columns we need. That’s not really a big deal, though, as long as a relatively limited number of people live in Helsinki. Like I wrote in that post, the index seek + key lookup is essentially two index seeks: one into Helsinki, and then one seek (for each Helsinki resident) on the clustered index, by their Id.
However, let’s add a little more complexity to the query:
Click through for a scenario in which a more selective query can result in worse performance than a less-selective variant.
Leave a CommentSteve Jones tries out a function:
I was trolling the docs and noticed the SIGN() function. I have never written this in production code, but it is an interesting function. This post looks at where I might use this and when the need arises.
I knew of the SIGN() function but have never used it before I think a simple CASE expression with an inequality predicate is more my speed.
Louis Davidson bangs his head against a wall:
When we decided to use T-SQL and a Fabric Data Warehouse for our ETL, I started thinking about generating the code with the metadata in the system catalog views or the
INFORMATION_SCHEMA. Having done this sort of thing before in SQL Server over the years, it seemed really straightforward. And it kind of is, until it isn’t.In this blog I want to show you a few ways you need to understand how working with metadata and temp tables varies (sometimes wildly) from the comfortable SQL Server environment and language you know very well, and give tips on how to get around these differences.
Click through for some of the fun you can have with a distributed SQL Server-like product.
Leave a CommentJohn Deardurff has some advice:
I’ve been reviewing Azure SQL Database performance guidance recently and came across Microsoft’s documentation on How to Use Batching to Improve Application Performance. While the article focuses on Azure SQL Database, the same principles of batching transactions for better performance apply equally well to SQL Server and Azure SQL Managed Instance.
The reason this topic caught my attention is that batching doesn’t just improve performance. It can reduce blocking, minimize rollback pain, improve transaction log efficiency, and potentially lower costs in cloud environments. That’s a pretty good return on investment for a relatively simple coding change. Here is the SQL Script that I use for this demonstration. Feel free to test for yourself. (It is a text file, so you will have to save it as a .sql file.)
Batching is especially important on delete operations against larger tables, where you don’t remove enough data to make TRUNCATE TABLE a viable alternative (or where you don’t have permissions to truncate). But one thing to keep in mind is that index design matters for batch operations. If you don’t have a good index, your first batches will be fast but they will gradually slow down as SQL Server needs to scan an increasingly large range to find the next set of rows to update. I wrote about this quite a while ago when putting together a talk on near-zero downtime T-SQL operations.
Brent Ozar is a madman and I love it:
Wanna play some Texas Hold ‘Em style poker and make a pile of Query Bucks? Wanna watch other database people playing?
Click through to learn more about the game itself, but also the strategic choices Brent made and a bit of an after-action report on what it took to generate this code.
Also, shout out to Brad Shultz, whose blog I miss. It was his T-SQL Tuesday on the APPLY operator that really opened my eyes to how good that operator is.
Finally, I’m giving this post the most coveted tag in Curated SQL: Wacky Ideas. It’s rare that I get to use this one.
Leave a CommentLouis Davidson does some more digging:
Creating the simplest of tables, the first thing I start thinking about is making sure that the data is going to be protected from the user. Users (including myself on my own projects when I have my user hat on) don’t notice when they start inserting poor quality data sometimes. Oops, I hit
F5twice, I wonder if that will affect my data? Without proper constraints, it probably will.In this second entry, I want to cover a few things about handling surrogate values that will help you avoid some of the (in retrospect) kind of dumb expectations that I had. Fabric Data Warehouse T-SQL feels so much like SQL Server Relational T-SQL that some stuff like choosing a surrogate key makes me think I am missing something.
One of the things Louis mentions is how the values get inserted and how it looks like they’re in different ranges. This makes sense, as each distributed node likely has its own range of identity values, similar to the way merge replication would work with identity keys to prevent overlap.
Leave a CommentAaron Bertrand sees that it’s all funhouse mirrors:
On this site, we usually talk about SQL Server performance problems that involve execution plans, locking, indexes, and waits. But SQL Server Agent can determine when backups, maintenance, ETL, monitoring, and reporting all arrive at the instance. In a way, a job deployment script is part of your performance configuration: you can materially impact the workload if you accidentally share a schedule across too many jobs.
I often create jobs in test environments that will later get distributed to many servers, often in isolated environments that can’t see each other. Creating an idempotent script to create a job, but only if it doesn’t already exist, can be cumbersome to get right. Many will prefer to use DbaTools Copy-DbaAgentJob, and that can be a great option if your deployment mechanism is PowerShell, DbaTools is already entrenched in your environment, and you can run the script from a place that can see the other server. In our case, T-SQL scripts are often passed on to colleagues or automation to be run independently in a different environment. Apologies to friends of SSMS, but the default right-click > Script Job output leaves a lot to be desired:
Click through for a variety of problems that the scripting logic has, as well as the solution Aaron came up with.
Leave a CommentGreg Low makes use of an uncommon function:
If your T-SQL is buried under layers of nested
REPLACE()calls just to swap out a few characters, there’s a simpler way. SQL Server’sTRANSLATE()function — introduced in 2017 but still rarely used — lets you replace multiple characters in a single string with one function call instead of stacking severalREPLACE()functions inside each other.This guide covers how
TRANSLATE()works, how it compares toREPLACE(), and where it falls short compared to other SQL dialects like PostgreSQL and Oracle.
One important thing to remember with TRANSLATE() is that it’s a character-for-character replacement. In other words, if you TRANSLATE('ABCDEFG', 'x'), it will replace every instance of each of those characters with the letter “x.” It doesn’t replace a substring.