Press "Enter" to skip to content

Curated SQL Posts

PyODBC vs C# ODBC Performance Differences

Published 2022-04-25 by Kevin Feasel

Jose Manuel Jurado Diaz explains a performance difference:

A customer asked today, why using ODBC Driver 17 for SQL Server in Python with PYODBC we have a slightly difference in terms of time taken if we compare with C# System.Data.Odbc. Following, I would like to share my lesson learned about it.

Read on for Jose’s explanation. My short version is, it seems particularly important when using the Python ODBC driver to write the exact query you want rather than a SELECT * or query which returns rows/columns you don’t need.

Comments closed

Microsoft.Build.Sql for Database Projects

Published 2022-04-25 by Kevin Feasel

Drew Skwiers-Koballa announces a new way of handling database projects:

Declarative development creates an environment where developers can focus on creating database objects while relying on the support of tooling locally and and in deployment pipelines to manage applying the differential changes calculated on the current state of the target database. Developers create objects such as tables or stored procedures by writing their definition with CREATE statements in scripts that live in source control just as if it is source code for any component of an application. Existing functionality for SQL projects in Visual Studio, Azure Data Studio, and VS Code provides developers with declarative development capabilities, however the existing SQL project file format has a few limitations.  With Microsoft.Build.Sql and SDK-style SQL projects, we look forward to unlocking new scenarios for your development practices.

It does sound interesting.

Comments closed

Rounding Differences in Power BI

Published 2022-04-25 by Kevin Feasel

Marco Russo explains the importance of data types for rounding in Power BI:

In one of the last classrooms I delivered, students were wondering why the results of their formulas were close but not identical to the proposed solution. We quickly identified the problem being an issue of data type conversion already covered in Understanding numeric data type conversions in DAX. However, the issue is interesting as a simpler example to show that different DAX calculations can produce different results because of a different way of rounding numbers!

Read on for Marco’s example.

Comments closed

Keeping Secrets in Azure DevOps

Published 2022-04-25 by Kevin Feasel

Kevin Chant has a secret:

In this post I want to cover how you can keep your Azure Synapse secrets secret in Azure DevOps. Because you need to do this if you are working with production deployments.

With this in mind, I want to raise more awareness about it and make sure others avoid putting secrets directly in their pipelines like in the below example.

Read on to understand what options are available to you. My preference involves Key Vault references but there are alternatives available.

Comments closed

Custom Model Evaluation Metrics with MLflow

Published 2022-04-22 by Kevin Feasel

Mark Zhang shows off a new bit of functionality in MLflow:

According to an internal customer survey, 75% of respondents say they frequently or always use specialized, business-focused metrics in addition to basic ones like accuracy and loss. Data scientists often utilize these custom metrics as they are more descriptive of business objectives (e.g. conversion rate), and contain additional heuristics not captured by the model prediction itself.

In this blog, we introduce an easy and convenient way of evaluating MLflow models on user-defined custom metrics. With this functionality, a data scientist can easily incorporate this logic at the model evaluation stage and quickly determine the best-performing model without further downstream analysis

Click through to see how to use built-in metrics but also how to create your own.

Comments closed

String Concatenation in R

Published 2022-04-22 by Kevin Feasel

Benjamin Smith creates a function:

While it is possible to use the paste() or paste0() for string concatenation. I do understand how it can be messy to deal with, especially when working with loops and/or nested functions. In this short blog I share a remedy for this by writing a special function which can lend for cleaner code as opposed to using paste() or paste0().

It’s not quite as nice as a here string (e.g., @"{FirstName} just referenced the name here string at {UserTime}" user.FirstName DateTime.UtcNow) but this is a good reminder that operator creation in R is pretty easy. H/T R-Bloggers.

Comments closed

Azure Data Studio April 2022 Updates

Published 2022-04-22 by Kevin Feasel

Timi Oshin has some release notes for us:

We are excited to announce the general availability of the Azure SQL Migration extension for Azure Data Studio. Among many other capabilities, this extension can be used for migrating SQL Server databases to Azure for an enhanced user experience. With this extension, users can get right-sized Azure recommendations based on performance data collected from your source SQL Server databases to optimize for cost and scale. The migration experience is powered by the Azure Database Migration Service which provides a scalable, resilient, and secure way to meet the needs of your organization. See below for a snapshot UI of this extension.

Click through for more notes on Azure SQL migration, the table designer, and more.

Comments closed

Subscribing to Power BI Reports

Published 2022-04-22 by Kevin Feasel

Reza Rad looks at e-mail subscriptions of Power BI reports:

Have you ever wondered is it possible to have updates of the Power BI report to be emailed to you (or some other colleagues) on a daily basis? Power BI, fortunately, has this feature, it is called Subscription. Subscriptions are helpful ways to send an up-to-date version of the report and dashboard to the users’ email addresses on a scheduled basis. In this article and video, I’ll explain what a subscription is and how it works in Power BI.

Click through for the video and complete blog post.

Comments closed

Splitting Strings with Quoted Names

Published 2022-04-22 by Kevin Feasel

Daniel Hutmacher mixes separators with regular characters:

Suppose you have a delimited string input that you want to split into its parts. That’s what STRING_SPLIT() does:

DECLARE @source nvarchar(max)='Canada, Cape Verde, '+    'Central African Republic, Chad, Chile, China, Colombia, Comoros';

SELECT TRIM([value]) AS[Country]
FROM STRING_SPLIT(@source, ',');

Simple enough. But delimited lists are tricky, because the delimiter could exist in the name itself. Look for yourself what happens when we add the two Congos to the list:

Daniel has a clever solution to the problem.

Comments closed