A Fine Slice Of SQL Server
In this video, we will take a look at two KPIs for measuring avidity. We will also show off how to use ranking window functions to order groups of customers.
Click through for the video. There are far too many measures of avidity for me to do a good job explaining them all, and so many of them are closely tied to the specific nature of the business, but hopefully this at least gives you ideas of how the business side may look at user avidity or stickiness.
Comments closedSebastiao Pereira creates a function:
It is very usual to have a set of discrete data points but sometimes it is necessary to estimate values between those points. Is it possible to create a function to do this in SQL Server?
It turns out that the answer is “yes.” Click through to see how.
Comments closedHaripriya Naidu explains SARGability:
Having the right index is helpful, but are you using the predicate (
WHEREclause) correctly to make efficient use of that index?This is where the term SARGable comes into play. SARGable stands for Search ARGumentable. If SQL Server is able to limit the search space while evaluating the predicates and can seek right at the page(s) to get the values, then it is SARGable.
Read on for an explanation of why this is important, as well as several examples of what is SARGable versus what isn’t. The most important thing about SARGability is that you pronounce it like “Sarge” and not “sarg.”
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 closedAnthony Nocentino jump-starts an availability group:
In this post, the fifth in our series, I want to illustrate an example of using the T-SQL Snapshot Backup feature in SQL Server 2022 to seed Availability Groups (AGs) with storage-based snapshots. Efficiently seeding an Availability Group is essential for maintaining high availability and ensuring effective disaster recovery. With the introduction of T-SQL Snapshot Backup in SQL Server 2022, snapshots can now be created at the storage layer. This advancement significantly speeds up the initialization of secondary replicas, particularly in environments that handle large databases.
This post will walk through a PowerShell script that effectively seeds an AG using T-SQL Snapshot Backup, dbatools, and Pure Storage FlashArray.
Click through for the script and the process.
Comments closedBrent Ozar does some sleuthing:
Over the last several years, Microsoft has been putting less and less effort into Cumulative Update documentation. We used to get full-blown knowledge base articles about fixes, but these days, we get a collection of footnotes with deceiving hyperlinks that look like they’re going to lead to more information – but they simply lead back to themselves.
So whenever a new Cumulative Update drops at SQLServerUpdates.com, before I install it, I like to:
- Log my dev server’s list of current sys.all_objects, all_columns, messages, configurations, etc to tables
- Apply the update
- Query the new contents of sys.all_objects, all_columns, etc to see what new ones were added
Click through for a script that does just this. It’s not as good as having detailed patch notes, but it beats having nothing.
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 closedSo I replied: “Challenge Accepted”, well, actually I replied with a gif of Neo saying it, but the effect was the same. So, I decided to just see, what would that look like. Coincidentally I am testing the new template for the Simple Talk site, and a function with 2100 parameters seemed like some code that screams out: “BIG!” Testing is should always be about pressing the limits of your code, so why not.
It turns out that you cannot have 2101 parameters in a single stored procedure definition. But this is definitely an example of Swart’s 10% Rule. It also nets Louis my most coveted category: Wacky Ideas.
1 Comment