Press "Enter" to skip to content

Category: T-SQL

FizzBuzz in SQL

Published 2022-09-08 by Kevin Feasel

Cathrine Wilhelmsen does some modulo division:

This week, my coworkers and I were given a fun challenge. Using any tool or language, solve FizzBuzz! Then present and explain the solution to the rest of the team. This was a fun challenge because our team is a mix of junior-to-senior developers and data professionals, working with everything from SQL to Python to C# to DAX to PowerShell. Those who had never solved FizzBuzz before got the chance to do so, while those who had already solved it got the chance to try again using a different tool or language.

Read on for Cathrine’s solution using a tally table. This classic variant of FizzBuzz is pretty easy to do in T-SQL; building the numbers table is the trickiest part.

Comments closed

Improvements to Parameter Sensitive Plan Optimization

Published 2022-08-16 by Kevin Feasel

Erik Darling is not good at being on vacation:

Several weeks back, I blogged about a missed opportunity with the new parameter sensitive plan feature. At the time, I thought that there was indeed sufficient skewness available to trigger the additional plan variants, and apparently some nice folks at Microsoft agreed.

If we step back through the old demo, we’ll get different results.

Click through for those results.

Comments closed

Attaching All SQL Server Data Files in a Single Directory

Published 2022-08-16 by Kevin Feasel

David Fowler migrated a bunch of databases:

Have you ever had the need to attach a large number of database in one go? There’s no way to attach multiple databases in SSMS or via script, so you’re probably going to be left with the slow, arduous task of doing them one by one.

I recently had to deal with a DR situation (I won’t go into details of what happened just yet as things are still quite sensitive, but I might look at it at some point in the future) where I faced exactly that issue. For one reason or another I needed to attach several hundred databases quickly. I didn’t fancy doing that via SSMS or script each one individually so I knocked together this script to do the job for me.

Click through for that script and instructions. Alternatively, a bit of Powershell and the right dbatools command could get you to the same result but this is good in the event that you can’t leave SSMS.

Comments closed

Improvements to GENERATE_SERIES

Published 2022-08-08 by Kevin Feasel

Erik Darling notes some improvements:

With the release of CTP 2.1, the problems that we saw the first time around are all gone. But there are still a couple small caveats that you should be aware of.

There’s also been a change in the way you call the function, too. You not longer need the START and STOP identifiers in the function.

There are still some limitations but it does look like the function is considerably better in CTP 2.1.

Comments closed

T-SQL Improvements in CTP 2.1

Published 2022-08-03 by Kevin Feasel

Itzik Ben-Gan looks at ways recent T-SQL improvements:

One of the complexities that is of a specific importance to this section is comparisons that potentially involve NULL comparands, such as ones that you use in filter and join predicates. Most operators that you use in such comparisons, including the equals (=) and different than (<>) operators, use three-valued logic. This means that there are three possible truth values as a result of a predicate that uses such operators: true, false and unknown. When both comparands are non-NULL, such operators return true or false as you would intuitively expect. When any of the comparands is NULL, including when both are NULL, such comparisons return the unknown truth value. Both filter and join predicates consider unknown as a non-match, and sometimes that’s not the behavior that you’re after.

The distinct predicate (IS [NOT] DISTINCT FROM) should simplify a lot of code in the wild.

Comments closed

Data Quality Checks in Power BI

Published 2022-07-28 by Kevin Feasel

Kristyna Hughes wants to match up data:

Picture this, you have a report in Power BI that someone passes off to you for data quality checks. There are a few ways to make sure your measures match what is in the source data system, but for this demo we are going to use python and excel to perform our data quality checks in one batch. In order to do that, we are going to build a python script that can run Power BI REST APIs, connect to a SQL Server, and connect to Excel to grab the formulas and to push back the quality check into Excel for final review. To find a sample Excel and the final python script, please refer to my GitHub.

Check out the GitHub repo as well as Kristyna’s very detailed walkthrough.

Comments closed

The Cure to Scalar Functions

Published 2022-07-25 by Kevin Feasel

Tom Zika has a cure:

In the first two parts, we have seen why the Scalar functions (UDFs) are a problem for the performance. So how do we deal with it now that we know it’s a problem?

There is only one solution:

I say we take off and nuke the entire site from orbit. It’s the only way to be sure.
— Ellen Ripley

Tom’s ideas are intriguing to me and I wish to subscribe to his newsletter.

Comments closed

CETAS and the Serverless SQL Pool

Published 2022-07-22 by Kevin Feasel

Liliam Leme takes us through the Create External Table as Select operation in the Azure Synapse Analytics serverless SQL pool:

Serverless SQL pool  has a very cool way to handle CREATE EXTERNAL TABLE AS SELECT (CETAS).  I mean, you can use a serverless SQL pool to create a CETAS which will materialize the query results. It means a heavy query in which, for example, the results would be part of future large join with other queries or aggregations that can be consolidated for reporting purposes. Those scenarios are examples that could be used as a CETAS. CETAS will be stored as a parquet file accessed by an external table in your storage and the performance is awesome. Later I am suggesting how to recreate the CETAS using pipeline against serverless SQL Pool.

Click through to see it in action.

Comments closed