Press "Enter" to skip to content

Month: July 2021

String Splitting with Line Numbers using CTEs

Published 2021-07-13 by Kevin Feasel

Steve Stedman has an interesting use for common table expressions:

Years ago while working on my CTE presentation for a SQL Saturday I added a blog post called “Using a CTE to Split a String Into Rows“, and since that posting I have used it many times.   But as things go in development, eventually there is a need to do something more.

Doing some complex string building to create files lately I cam across the need to use a CTE to split strings into rows but to also include line numbers.  To accomplish that I first started with the same function that I used last year, but renamed it, and added a second output column called LineNumber, here is what it looked like:

Click through for the script, as well as some additional notes.

Comments closed

Uses for SQL Server Containers

Published 2021-07-13 by Kevin Feasel

Aaron Bertrand explains two good uses for using SQL Server containers:

I used this technique of “stop, drop, repeat” just the other day while testing some behavior around case-sensitive and binary collations at the instance level. In the old days, I would have had to install a second full-on instance of SQL Server in order to test a specific instance-level collation, then repeat for every collation in my set of tests. Yikes! With containers, this is much easier; I just have to add one additional argument to the docker run command:

Check it out for a quick walkthrough of how to spin up a container and some good uses for it.

Comments closed

Filtering Power BI Slicers Based on Other Slicers

Published 2021-07-13 by Kevin Feasel

Matt Allington answers a question:

This is a follow up article from my last blog post where I demonstrated how easy it is to use two slicers to compare any two items. In the demo in my article, I specifically showed how you could compare any 2 years of choice, but the principle applies to any two products, any two customers, or any two of anything. At the bottom of that article, there was a comment from Artur asking if there was a way to limit the choices in the comparison slicer to exclude the item selected in the first slicer. E.g.

If I select Year = 2016 in slicer 1 below, then I don’t want to see Year = 2016 in the comparison slicer 2 below.  I thought that was a great question/suggestion and hence that is the topic for today.

Click through for the video in which Matt answers the question.

Comments closed

Time Series Estimation with Facebook’s Prophet

Published 2021-07-12 by Kevin Feasel

Dan Lantos looks at the Prophet library:

This article (part of a short series) aims to introduce the Prophet library, discuss it at a high level and run through a basic example of forecasting the FTSE 100 index. Future articles will discuss exactly how Prophet achieves its results, how to interpret the output and how to improve the model.
Please see this article (by my talented colleague Gavita) for an introduction to time-series forecasting algorithms.

Click through for part one in an ongoing series.

Comments closed

Age Calculations in Power BI

Published 2021-07-12 by Kevin Feasel

Meagan Longoria wants to calculate age:

In week 26 of Workout Wednesday for Power BI, I asked people to calculate the age of Nobel laureates at the time they received the award. I provided some logic, but I didn’t prescribe how to create the age calculation. This inspired a couple of questions and a round of data validation as calculating age may be trickier than you think. In this post, I’ll explore some of the ways people have calculated age in Power BI and the edge cases where those calculations may not work.

In my solution video for Workout Wednesday, I used Power Query to calculate age. This was inspired by several blog posts and videos I had seen previously.

This turns out to be a much trickier problem than it first appears.

Comments closed

The First Partition in a Power BI Dataset Refresh

Published 2021-07-12 by Kevin Feasel

Chris Webb wants to figure out why the first partition is so special:

It’s a visualisation from a report created by my colleague Phil Seamark (as detailed in this blog post) showing how long all the partitions in a dataset take to refresh. If you look at these visualisations you’ll probably ask the same question I did: why does the first partition always start before the others?

Click through for the answer.

Comments closed

Attaching a Database sans Transaction Log File

Published 2021-07-12 by Kevin Feasel

Chad Callihan lost a transaction log file in a boating accident:

What if you’re moving a database to a new server by detaching and re-attaching database files and someone (not you of course) loses the log file? What if an old database needs to be brought online but the person coming to you only has an mdf file? Can you still attach the database in these scenarios? Let’s find out.

Read on for the answers.

Comments closed

A Measure of Utilization by SQL Server Version

Published 2021-07-12 by Kevin Feasel

Steve Stedman some numbers:

Here listed is the current percentages of SQL server versions running our Daily Check-up with Database Health Monitor.

Do note that the population for this is “People who use Steve Steadman’s SQL Daily Checkup product” and not organizations which use SQL Server as a whole, so it’s not wise to apply findings from the first directly onto the second. That said, it doesn’t surprise me that 2016 is the most in-use version of SQL Server in this sample. It’s a little surprising how many 2014 instances there are, but that might be related to cardinality estimator changes.

1 Comment