A Fine Slice Of SQL Server
Chad Callihan goes index hunting:
SQL Server has the CHARINDEX() and PATINDEX() functions that can both be used to find the position of a value in a string. They each have some differences that make them unique and more fitting for specific situations.
Let’s look at a few examples of how each can be used.
Read on to see how to use CHARINDEX() and PATINDEX() on two statements that are total falsehoods: Kevin Conroy always will be the number 1 and best Batman.
Erik Darling makes a comparison:
Perhaps one of the most exhausting parts of my job is disabusing developers of the notion that common table expressions hold some weight in gold over any other abstraction layer in SQL Server.
Think of it like this:
- Views are like a permanent home
- Common table expressions are like a mobile home
You can put equally terrible queries in either one and expect equally terrible results.
Read the whole thing for additional spicy analogies and similes.
I would say that I certainly do not disdain views, so much as I see them as a yellow flag. It’s really easy to go from helpful views to views nested in views nested in views like malevolent Matryoshka dolls. It’s harder to do that with common table expressions before that reptilian part of your brain kicks in and says that “hey, maybe this isn’t the greatest idea I’ve ever had.” Not that this will stop some people, admittedly…
Leave a CommentErika Balla looks out the window:
Window functions are an advanced feature of SQL that provides powerful tools for detailed data analysis and manipulation without grouping data into single output rows, which is common in aggregate functions. These functions operate on a set of rows and return a value for each row based on the calculation against the set.
In this article, we delve into window functions in SQL Server. You will learn how to apply various window functions, including moving averages, ranking, and cumulative sums, to achieve comprehensive analytics on data sets.
Click through for several examples.
Leave a CommentI play fantasy football. To start each season, there is a draft, where players are randomly assigned draft order. We’ve started some seasons with a “snake” draft, to make the selection of players most fair and balanced. A snake draft works like this (a simple example with four players):
- First pick in the first round picks last in the second round.
- Second pick in the first round picks third in the second round.
- Third pick in the first round picks second in the second round.
- Last pick in the first round picks first in the second round.
- Then, in the third round, it reverts to the original order: The first pick from the first round picks first again (giving them two picks back-to-back).
Read on to see how to implement this in T-SQL. I’m happy to report that I anticipated the answer, though Aaron notes a couple of issues that I didn’t catch in my “compile the code in my head” approach.
Leave a CommentErik Darling has an existential dillema:
Rather selfishly, I do this for my stored procedures, for all the reasons in the first sentence. Especially when debugging stored procedures, you’ll want to know where things potentially went wrong.
In this post, I’m going to walk through a couple different ways that I use to do this. One when you’re storing intermediate results in a temporary object, and one when you’re just using a single query.
Read on for an example of how to do this.
Leave a CommentNikola Ilic reviews a new tool:
In the last 2-3 months, I had an opportunity to try and test the new external tool, called Powerops. This is an external tool developed by Truviz, the company you might already know as the creators of various Power BI “goodies”.
Now, you are probably expecting “another Power BI external tool that solves a specific challenge”…I’ll tell you straight away – you can’t be more wrong! Powerops is a mix of various existing external tools – think of DAX Studio, Tabular Editor, ALM Toolkit, Measure Killer, etc. – but it’s also more than a simple sum of the features of the aforementioned tools.
Read on for the full review. Pricing is free for the first two months, and then either buy a license or you can upload up to two files per month and remain in the free tier.
Leave a CommentKevin Wilkie breaks out the abacus:
Today, I was working with SQL Server to get row counts from several tables so I thought I’d be smart and work with some functions in SQL Server to make it smarter / easier.
Now, if I am truly only getting “straight” row counts from these tables, I would be able to create a query like the below that would provide the answers with no problem:
Read on for the normal approach, as well as a more complicated approach made necessary due to some business logic requirements.
Comments closedAndy Brownsword deals with statistical skew in the data:
Dynamic SQL has many uses and one of these can help us fix Parameter Sniffing issues. Here we’ll look at how it can be used to generate multiple execution plans for the same query.
Parameter sniffing is a common issue. Even for simple queries we can run into suboptimal plans being produced. There are multiple ways we can use Dynamic SQL to solve this challenge. Here we’ll demonstrate one technique: Comment Injection.
My one note about a good post (other than, you should read it) is that parameter sniffing is not itself a bad thing. 95%+ of the time, it’s a great thing. It’s that last 5% or so that give it a bad name.
Comments closedSteve Jones gives it the ol’ college try:
This is a common error handling technique in other languages. C# uses it, as does Java, while Python has TRY EXCEPT. There are other examples, but these are good habits to get into when you don’t know how code will behave or if there is something in your data or environment that could cause an issue.
In SQL, I think many of us get used to writing one statement in a query and forget to do error handling, or transactions. However, this can be a good habit as your code might grow and people might add more statements that should execute.
Read on for a few examples of how to use SQL Server’s TRY-CATCH functionality. It’s not perfect, but as Steve shows, there are definitely good uses for it.
Comments closedI have never once seen anyone use these. The most glaring issue with them is that unlike a lot of other directives in SQL, these ones just don’t do a good job of telling you what they do, and their behavior is sort of weird.
Unlike EXISTS and NOT EXISTS, which state their case very plainly, as do UNION and UNION ALL, figuring these out is not the most straightforward thing. Especially since INTERSECT has operator precedence rules that many other directives do not.
I’ve used EXCEPT to check if two datasets are equivalent for testing purposes: A EXCEPT B should be zero rows, and B EXCEPT A should be zero rows. It has built-in handling of any NULL madness. Set intersections have their uses as well.