Press "Enter" to skip to content

Curated SQL Posts

Using the subset() Function in R

Published 2024-11-15 by Kevin Feasel

Steven Sanderson plays duck-duck-goose with the data:

Data manipulation is a cornerstone of R programming, and selecting specific columns from data frames is one of the most common tasks analysts face. While modern tidyverse packages offer elegant solutions, Base R’s subset() function remains a powerful and efficient tool that every R programmer should master.

This comprehensive guide will walk you through everything you need to know about using subset() to manage columns in your data frames, from basic operations to advanced techniques.

Click through for a description of the function and examples of it in action.

Leave a Comment

Tenant Switching in Microsoft Fabric

Published 2024-11-15 by Kevin Feasel

Koen Verbeeck has good news:

Praise whatever deity you believe in, because it’s finally here, a tenant switcher for Microsoft Fabric (which includes Power BI). A what? Let me explain. When you have a organization with multiple tenants in Azure (also called directories in some products like Azure Devops), or you’re a consultant like me who works with multiple clients (with each their own tenants), it’s possible that you can log into multiple tenants using the same email address. This can happen if your user account was added as an external user to another tenant.

This has been a real pain, and unfortunately, that pain still exists for Power BI Desktop.

Leave a Comment

Hyperthreading and Warehouses

Published 2024-11-15 by Kevin Feasel

Joey D’Antoni covers the impact of hyperthreading on warehouse performance:

Database performance, especially in the cloud, is always a big concern. Traditionally, cloud storage has not performed as well as high-end on-premises storage. The introduction of faster cloud storage (in Azure Premium V2 and Ultra Disk, in AWS IO1-3) and VMs with more available storage bandwidth has improved overall storage performance. However, storage is only sometimes the complete picture.

Read on for Joey’s tests and findings.

Leave a Comment

The IS Operator in T-SQL

Published 2024-11-15 by Kevin Feasel

Eric Blinn wants to know of those NULLs:

Many data professionals search for help writing T-SQL queries containing columns with NULL values in a Microsoft SQL Server table. Some of the most common NULL functions include IS NULL, IS NOT NULL, NOT NULL, NULL, what is NULL, NULL vs NOT NULL, etc. In this SQL tutorial, the goal is to help you better understand how to work with NULL values in a SQL database.

Even the best of us have accidentally used = NULL in a script when we meant IS NULL. Which is totally different from ISNULL(), of course. And NULLIF(), naturally.

Leave a Comment

Learn from Developer Edition rather than Express Edition

Published 2024-11-15 by Kevin Feasel

Vlad Drumea learns the right way:

It’s free for non-production use

Just like SQL Server Express Edition, Developer Edition is free, but, unlike Express, it’s only free for non-production purposes (developing, testing, demoing, learning).

If you want to learn more about this, Bob Ward has a blog post that covers this in-depth and answers some questions that people tend to have about Developer Edition.

Back in the day, Developer Edition used to cost money—something like $50, so not much, but just enough to make people prefer Express Edition. Those days are long gone, however.

Leave a Comment

Cross-Workspace KQL Queries in Microsoft Fabric

Published 2024-11-14 by Kevin Feasel

Sandeep Pawar drinks your milkshake:

In Fabric, if you want to query a delta table from a lakehouse in another workspace, you create a shortcut to that table. Similarly, in Eventhouse, you can also create shortcuts to Eventhouses in other workspaces, but the option might not be immediately obvious in the GUI. If you click on New > OneLake shortcut, it creates a shortcut to a delta table, not an Eventhouse.

Click through to see how to do this via UI and programmatically.

Leave a Comment

SCD Types in Microsoft Fabric

Published 2024-11-14 by Kevin Feasel

Kenneth Omorodion reminds us that the Kimball model is still quite valuable:

In modern data warehousing, how we handle updates to dimension tables is crucial. There are several approaches; but the decision often comes down to two primary strategies: Slowly Changing Dimensions (SCD) Type 2 and overwriting tables. Each has its own benefits, use cases, and trade-offs. This tip will explore the two methods and why SCD Type 2 is often a better option in many data warehouse scenarios.

Read on for this overview of the benefits of type-2 slowly changing dimensions, as well as a little bit of coverage of several other types of slowly changing dimensions.

Leave a Comment

Finding the Top Element in a Group via KQL

Published 2024-11-14 by Kevin Feasel

Dennes Torres is looking for the top dog:

When learning something new, we always compare it with what we know. In this case, we end up comparing KQL with SQL.

In SQL, when we need to get the top rows based on a grouping, the process is not easy. For example, let’s say that in a table containing taxi rides, we want to retrieve the record of the ride with the highest fare on each day.

There are multiple ways to do this, none is too easy. One of the methods is to create a row_number based on the day. This can be achieved using what’s called in SQL as window function. In this case row_number with a stablished window in the result based on the date.

Click through to see how you can use arg_max() in KQL to do this.

Leave a Comment

Renaming a Database in SQL Server

Published 2024-11-14 by Kevin Feasel

Steve Jones asks, what’s in a name?:

I had someone ask me how to rename a SQL Server database recently. They were doing some development work and wanted to rename databases to test an application. I thought I remembered, but in this post, I show I learned something.

Read on for the answer, as well as some notes about it. One additional thing I’d point out is that renaming the database doesn’t rename the underlying files.

Leave a Comment

New Permissions and Database Roles in SQL Server 2022

Published 2024-11-14 by Kevin Feasel

Lori Brown builds a list:

Well…..I just learned about these and thought that it would be good to understand them a little more and have some links to read more about them.  I honestly don’t have a lot of SQL 2022 servers in our customers SQL estate, so this has flown under the radar for me.  This will be an attempt to put some spread-out information in a one-stop shop.

Click through for a table with information on roles, as well as lists for permissions.

Leave a Comment