Press "Enter" to skip to content

Author: Kevin Feasel

Fun with Implicit Conversion and Table Partitioning

Published 2024-05-30 by Kevin Feasel

Rod Edwards takes us through an issue:

CONVERT_IMPLICIT(nvarchar(200),[xxxxxxxxxxxxxxxxxxx].[Category,0)=[@Category] AND CONVERT_IMPLICIT(nvarchar(200),[xxxxxxxxxxxxxxxxxxx].[Id],0)=[@Id]

Oh dear, what’s all that then, we have a fat Residual Predicate, where SQL is performing an Implicit conversion on our query predicates.

Time to look at our datatypes.

This is one of several reasons why I espouse the philosophy of NVARCHAR Everywhere. You can’t have implicit conversion if you’re always using NVARCHAR over VARCHAR.

Comments closed

Reviewing the Microsoft Fabric Roadmap

Published 2024-05-30 by Kevin Feasel

Paul Turley has a report:

I created this report to summarize the release status for all Fabric features that are documented in the Microsoft Fabric Roadmap. The information on this report is collected and updated frequently from the Fabric Roadmap hosted by Microsoft, and displays status information in a convenient, single-page Power BI report. Each feature area on the report has links to the detail documentation on the official roadmap site. For convenience, I’ve shortened the report path to TinyURL.com/FabricRoadmapReport.

Check out that link to see the report.

Comments closed

Using the CONVERT() Function in T-SQL

Published 2024-05-30 by Kevin Feasel

Joe Gavin shows how to use a function:

A common task while working with data in Microsoft SQL Server is converting from one data type to another. Most often, it’s done to change the way data is presented, but sometimes it is needed to make sure the right data types are being used for comparisons, joins, or sorting.

The SQL CONVERT function, which has long been part of the SQL language, and as the name implies, can be used to convert a value of one data type into a specified data type with optional formatting attributes. CONVERT gives you the ability to format, whereas the ISO Compliant CAST function does not.

My very strong recommendation for 99% or so of the audience: use TRY_CONVERT() instead. TRY_CONVERT() came out in SQL Server 2012 (sorry for the 1% stuck pre-2012) and has the same performance profile as CONVERT(), except that, when conversion fails, TRY_CONVERT() returns NULL rather than throwing an error.

There is also a TRY_CAST() that does exactly what you think it would.

Comments closed

healthR.data Package Updates

Published 2024-05-29 by Kevin Feasel

Steven Sanderson has an update for us:

I’m excited to share the latest updates to the healthyR.data R package! This release brings new functionality and minor improvements, all aimed at making your data management tasks easier and more efficient. Here’s a breakdown of what’s new:

Read on for information on four new functions and a couple of bugfixes.

Comments closed

Diagnosing Slow Commits in Postgres

Published 2024-05-29 by Kevin Feasel

Laurenz Albe offers up some thoughts:

Sometimes one of our customers looks at the most time consuming statements in a database (either with pg_stat_statements or with pgBadger) and finds COMMIT in the high ranks. Normally, COMMIT is a very fast statement in PostgreSQL, so that is worth investigating. In this article, I will explore the possible reasons for a slow COMMIT and discuss what you can do about it.

Read on for those reasons.

Comments closed

Azure SQL Managed Instanced Update Policies

Published 2024-05-29 by Kevin Feasel

Rod Edwards is not amused:

Ah, SQL 2022, the release that finally brought box SQL and Azure managed instance closer together. We have wonderful toys such as Managed Instance Link, allowing us to connect our SQL 2022 on prem instances with Managed Instance Link. It waslike the first real effort to integrate modern Azure offerings with those who also need / prefer an On Prem presence.

Rob Litjens has a follow-up on this:

I prepared some questions:

  1. What polices does Managed Instance have?
  2. Why did Microsoft implement the ‘Always-up-to-date update policy’ Policy?
  3. Why is it named Policy?
  4. Do we need to update our Azure scripts to implement it (immediately)?
  5. Is there impact on offerings like Managed Instance Link

Do read both of these as they combine for a rounded perspective of the issue Rod brought up.

Comments closed

Power BI Studio 2.0 Released

Published 2024-05-29 by Kevin Feasel

Gerhard Brueckl moves fast and doesn’t break things:

Due to the great feedback I have received for the first version of my VSCode extension to mange Power BI objects from within VSCode I decided to continue working on it and am finally happy to share that I am releasing a new version – v2.0!

If you already had the previous version installed in VSCode, you do not have to do anything as it will update automatically. If you are a new user, you can install it from the gallery or search for “Power BI Studio” in the VSCode extensions tab.

Click through for the list of updates, as well as how you can install the Visual Studio Code extension.

Comments closed

Recapping an Orchestration Framework

Published 2024-05-29 by Kevin Feasel

Martin Schoombee wraps up a series:

Frameworks are extremely useful when they are thoughtfully designed and implemented. I have seen both sides of the coin, but what I probably see the most of is a lack of any sort of framework. What I typically see are some naming conventions and coding standards, but many companies miss the opportunity to take it one step further and reduce the inefficiencies of repetitive tasks. There’s a ton of repetition in ETL processes, and in my opinion that gives us a really good opportunity to streamline the way in which we are doing things with a well designed framework.

Read on for Martin’s notes to keep in mind, as well as where to go from here.

Comments closed

Dynamic Historical Partition Refresh in Power BI

Published 2024-05-29 by Kevin Feasel

Marc Lelijveld digs into partition refreshing:

I’ve heard the question pretty often from customers: “You told me to use incremental refresh, but how can I regularly run a full load or refresh onder partitions?” Well, there are perfect ways to do this using Tabular Editor or SQL Server Management Studio. But this often includes manual work to trigger the processing.

Today, this question was asked again to me. I thought, there should be a smarter way to do this. Since I recently explored more in the wonderful world of Fabric Notebooks and Python, decided to dive a bit deeper in this world and see if it is possible to script something like this using Semantic Link. And obviously, the answer is “Yes!”

Read on to learn how to do it with a bit of Python and Microsoft Fabric’s Semantic Link library (sempy).

Comments closed