Press "Enter" to skip to content

Curated SQL Posts

Understanding Implicit Conversions

Jared Poche takes us through the concept of implicit conversions:

A quick search will tell you that implicit conversions are pretty awful for performance, and in particular drive CPU usage. That’s not news. There is an aspect of this I think a lot of engineers don’t understand; why does it cause performance issues?

An implicit conversion occurs when you try to compare between or assign data across two different data types, without converting one yourself. If you used a CAST or CONVERT, it would be an explicit conversion. When SQL Server does it for you, it’s an implicit conversion, and these can have a real impact on your execution plans. Not all combinations of types can be converted implicitly, for a full list look here.

What’s interesting in this is that there were some cases where Jared expected implicit conversion and the rules indicate that there should be implicit conversion, but the database optimizer saw through his ruse.

Comments closed

Getting Folder Names in Power BI

Reza Rad shows us something new in Power Query:

There are times that you need to get the list of FOLDERS and not just files. Of course folder itself (without considering files in it), doesn’t contain data to be used for a report. However, sometimes, even the folder name might contain some useful information. The Get Data From Folder option in Power BI will just give you a list of files. There is a little trick that can help you to get a list of folders. Let me show you how.

There’s a fair bit to it, so click through and read the whole thing.

Comments closed

Release Flow Branching and Database DevOps

Kendra Little explains why the Azure DevOps Release Flow model can work well for database activity:

But how do you use branches? It’s helpful to pick a strategy. There are many fine Git branching strategies out there, things like GitFlow and GitHub Flow and more — enough that it’s overwhelming to learn about these when you are just starting out.

The strategy that I recommend for folks who are starting out with database DevOps and Git is the Azure DevOps team Release Flow model with dedicated development databases. (Why dedicated development databases? Read more here.)

Read on to learn why.

Comments closed

Reporting Queries and User-Defined Functions

Erik Darling tells a story of two things which do not get along very well:

The OLTP part was working fine, but there was a reporting element that was dog slow, and would cause all sorts of problems on the server.

When we got into things, I noticed something rather funny: All of their reporting queries had very high estimated costs, and all the plans were totally serial.

The problem came down to two functions that were used in the OLTP portion, which were reused in the reporting portion.

Read on to understand why SQL Server 2019 and its function inlining capability would not have helped here.

Comments closed

Refreshing Power BI from Your Outlook Calendar

Chris Webb has a nice use for Power Automate and Outlook:

The ideal way to view when multiple events are scheduled is a calendar and we’ve got great calendar functionality in Outlook. What if you could schedule refresh of your datasets from a calendar in Outlook? It turns out to be easier than you might think! Here’s how.

Read the whole thing, including Chris’s warning not to put it into production. You wouldn’t want the person with all of those calendar entries to leave your company and have things suddenly break, after all.

Comments closed

Streams and Tables in Apache Kafka

Michael Noll has started a four-part series. Part one serves as a primer:

In my daily work as a member of Confluent’s Office of the CTO and as the former product manager for ksqlDB and Kafka Streams, I interact with many users of Apache Kafka—be it developers, operators, or architects. Some have a stream processing or Kafka background, some have their roots in relational databases like Oracle and MySQL, and some have neither. But many of them have the same set of technical questions, such as: What’s the difference between an event stream and a database table? Is a Kafka topic the same as a stream? How can I best leverage all these pieces when I want to put my data in Kafka to use?

By the end of this series, you will have answers to each of these common questions and many more. If you are interested to learn about Kafka, I invite you to join me on this journey through Kafka’s core fundamentals!

Part 2 looks at storage fundamentals:

Part 1 of this series discussed the basic elements of an event streaming platform: events, streams, and tables. We also introduced the stream-table duality and learned why it is a crucial concept for an event streaming platform like Apache Kafka®. Here in part 2, we will take a deep dive into Kafka’s storage fundamentals. Notably, we will explore topics and—in my opinion, the most important concept in Kafka: partitions.

We’ll start with the most basic storage question: how do I store data in Kafka?

I’m looking forward to parts 3 and 4.

Comments closed

Installing .NET Core on a Raspberry Pi 4

Hasan Savran continues a series on Microsoft + Pi:

I have been writing about Azure IOT Hub and Raspberry Pi 4. So far, I bought a Raspberry Pi 4. I registered it as Azure IOT Edge device. Now, I am ready to write some code in Raspberry Pi. In this post, I will show you how install .NET Core 3.1 to Raspberry Pi so we can write some code to generate some data and push this data to Azure IOT Hub.

     First, you need to go to the .NET Core homepage to get the latest version’s url. Following page lists all .NET Core version, 3.1 was the latest when I was writing this blog. Pick the latest one from this list.

Another route might be to install Docker on your Pi.

Comments closed

Why Use Aggregations in Power BI

Kasper de Jonge has five reasons why you should use Power BI aggregations:

A lot has already been written on aggregations and it is one of the most exciting features of Power BI in a while. The guys at SQLBI did a session on it and my colleague Phil Seamark wrote an amazing host of articles with many details and tips and tricks on it. So why another article? Most people think aggregations is only applicable to petabyte scale data sets. That doesn’t have to be the true though, in this blog post I wanted to give my take on it and why it might be applicable to your current, smaller, model.

Read on for those reasons.

Comments closed

Causality Tracking in Extended Events

Grant Fritchey explains what causality tracking is in SQL Server Extended Events:

So, then what happens? It’s simple. A given task, let’s say, for example, an INSERT query, will be given a GUID. Then, all the events associated with that task, from the compile, to the query start, query finish, waits, recompiles, etc., all get associated with that GUID. They also receive a sequence number.

Therefore, through the use of causality tracking, you can see all the behavior associated with a given task and the order in which it occurred. On a test system, with no load and no activity, this isn’t that big a deal. On a real system under load, whether we’re talking, dev, test or production, where you’re going to see tons of simultaneous tasks occurring. Causality tracking enables you to isolate everything and group by task. You can pluck one set of behavior out of the mess.

That’s really useful, though it does add overhead. It also will never be available for Profiler.

Comments closed

Learning About the Power BI Activity Log

David Eldersveld takes us through the new Power BI activity log:

What is this activity data, and how is it valuable?

The audit/activity log data contains details for every interaction that users in your tenant have with the Power BI service (powerbi.com). Activities such as viewing reports, publishing apps, modifying gateway data sources, changing workspace security, and dozens of others have records broken out by user and timestamp.

Using this data, organizations not only know who does what and at what time. You can move beyond a simple audit trail to measure how well Power BI adoption is progressing at your enterprise. In this case, adoption targets for a group’s collective number of touchpoints can be compared to the actuals obtained from the logs—even down to the individual object level. Using the logs in this manner by combining actuals to targets, BlueGranite often finds underutilized reports or other opportunities to improve adoption.

Read on for more info about what it does, how it differs from the Office 365 audit log, and more.

Comments closed