Press "Enter" to skip to content

Curated SQL Posts

Comparing Table Records with T-SQL

Published 2022-12-22 by Kevin Feasel

Chad Callihan compares and contrasts:

We recently looked at looked at comparing schemas using Azure Data Studio. What if we need to compare tables by using a query? For this post we’ll compare using EXCEPT, NOT IN, and NOT EXISTS to find differences between two tables.

Our two tables to compare will be Comic and Comic_Copy. Based on counts, we have 48 more records in Comic than we do in Comic_Copy. Let’s find the differences.

In Chad’s specific query, NOT EXISTS works great. Where I like EXCEPT is when you need to see if any of the non-key columns differ. For example, if you also needed to compare titles for rows with the same ID and ensure those titles matched.

Comments closed

Adding Emoji to Power BI Apps

Published 2022-12-22 by Kevin Feasel

Ed Hansberry ran out of words:

Your report page names, and in turn, the Power BI app can be enhanced with the judicious use of emoji. I was surprised to find out that the characters came through in full color, and that can help your users find the important pages faster. This can be especially useful in a large Power BI app with dozens of reports and potentially hundreds of pages.

Adding emoji is relatively straight-forward in Windows 10 and 11. Below are the steps for Windows 11.

Read on to learn how to do it with Windows 11, followed by the steps for Windows 10.

Comments closed

From SQL Server to Cassandra

Published 2022-12-22 by Kevin Feasel

Lewis DiFelice shares some lessons learned:

The first 6 months were rough. The cluster had been in operation for more than 6 months but was not doing too well. Performance was poor and, worse, it frequently crashed. It was not a fun time. But eventually, the problems got fixed. 

There were several issues (including my inexperience) that caused these problems, but the core one was that the original developer had treated it like another relational database. 

Read on for a few tips to make learning (and managing) Apache Cassandra a little easier.

Comments closed

Keys and Certificates with TDE

Published 2022-12-22 by Kevin Feasel

Matthew McGiffen has a big keychain to store all of those keys:

When you first look at the encryption hierarchy for TDE in SQL Server it can be a bit daunting. There seem to be a lot of objects involved and it might not be clear why each is required. It can be tempting to skip a full understanding of all the objects and just get on with setting things up – which is relatively straightforward.

I’d encourage you not to do that and I’ll explain why. There are a lot of scenarios that might crop up in the lifecycle of a TDE protected database instance. Recovering a protected database from backup. Migrating database from one server to another. Managing high availability. The list goes on.

Remember: the bigger the keychain, the more powerful the man.

Comments closed

Structuring Azure ML Projects and using the Terminal

Published 2022-12-21 by Kevin Feasel

Tomaz Kastrun nears the end of the Azure ML advent. Day 20 covers package requirements and other niceties:

When creating notebooks, it is always a good way to have the dependencies included. Whether it is a particular version of a package, a separate script file or an installation requirement.

Selecting an environment or kernel can be an issue if it is not correctly initiated with the code. And you can also check the kernels with a simple python code:

Day 21 looks at the Azure CLI and running code from within a compute instance terminal:

Using Azure CLI can help you progress faster, make repetitve tasks automated and even use the GIT integration, for faster and better collaboration.

So we have created a YAML file on Day20 and we can use it also with Azure CLI to create an environment.

Comments closed

High-Level Thoughts on Migration

Published 2022-12-21 by Kevin Feasel

Marc Lelijveld thinks about migrations:

Over the last half year, I have been involved in many large migration projects from another BI tool to Power BI. All with a different setup, from Tableau, Microstrategy and from Analysis Services to Power BI. In this blog I will shine a light on the experiences I gained during these migrations and share some do’s and don’ts. In this blog I start with the why you even migrate in the first place, after which we dive into the where to start and of course also how. A blog that is more focused on the process rather than the technical how-to. At the same time, this blog describes the work I did over the past months and therefore is a small recap of the past half year.

Read on to understand your “why,” at least when it comes to migrations.

Comments closed

The Value (and Cost) of DATETRUNC

Published 2022-12-21 by Kevin Feasel

Brent Ozar points out the ups and downs of DATETRUNC():

The first one, passing in a specific start & end date, gets the best plan, runs the most quickly, and does the least logical reads (4,299.) It’s a winner by every possible measure except ease of writing the query. When SQL Server is handed a specific start date, it can seek to that specific part of the index, and read only the rows that matched.

DATETRUNC and YEAR both produce much less efficient plans. They scan the entire index (19,918 pages), reading every single row in the table, and run the function against every row, burning more CPU.

SQL Server’s thought process is, and has always been, “I have no idea what’s the first date that would produce YEAR(2017). There’s just no way I could possibly guess that. I might as well read every date since the dawn of time.”

Read on for the upshot.

Comments closed

Breaking the World with auto_explain

Published 2022-12-21 by Kevin Feasel

Ryan Lambert gets a lot of explanation:

Postgres has a handy module called auto_explain. The auto_explain module lives up to its name: it runs EXPLAIN automatically for you. The intent for this module is to automatically provide information useful for troubleshooting about your slow queries as they happen. This post outlines a pitfall I recently discovered with auto_explain. Luckily for us, it’s an easy thing to avoid.

I discovered this by running CREATE EXTENSION postgis; and watching it run for quite a while before failing with an out of disk space error. That is not my typical experience with a simple CREATE EXTENSION command!

Read on to learn what happened and how you can prevent making a similar mistake.

Comments closed

Capturing Event Hubs Data in Delta Lake Format with Stream Analytics

Published 2022-12-21 by Kevin Feasel

Xu Jiang announces a public preview:

The Stream Analytics no-code editor is a drag and drop design tool that helps customers to develop the Stream Analytics jobs without writing a single line of code. The experience provides a canvas that allows you to connect to input sources to quickly see your streaming data. Then you can transform and preview it before writing to your destination of choice in Azure. To learn more, see No-code stream processing through Azure Stream Analytics | Microsoft Learn.

Read on to see how you can capture and process data into Delta Lake format via their designer.

Comments closed