Press "Enter" to skip to content

Month: September 2022

IS [NOT] DISTINCT FROM

Published 2022-09-14 by Kevin Feasel

Louis Davidson likes a new operator in SQL Server 2022:

The MOST exciting change from a T-SQL standpoint is: IS NOT DISTINCT FROM. This feature solves an age-old issue for T-SQL programmers and is worth its weight in gold. It is basically an equals comparison operator like =, but treats NULL as an individual value. Unlike =, this new operator returns only TRUE or FALSE, but not UNKNOWN. Writing queries that compare to values that can contain NULL is tedious, mostly because of code like the following:

Louis is quite happy here. I like the fact that the syntax is here, though I’d be concerned about performance—the syntax is nicer but you can run into the same performance issues as you’d have with “NULL or match” type queries.

Comments closed

Comparing the Dedicated and Serverless SQL Pools

Published 2022-09-14 by Kevin Feasel

Liliam Leme compares SQL pools in Azure Synapse Analytics:

Two recurring questions I frequently get from customers are: “What is the difference between Synapse dedicated SQL pool (formerly SQL DW) and Serverless SQL pool?” and “Which one should I choose for my Business?”

This post is intended to explain the basic concepts of dedicated SQL pool and Serverless SQL Pool, help you understand how they work, and how to use them based on your business needs.

Click through for the comparison.

Comments closed

Thoughts on Power BI Datamarts

Published 2022-09-14 by Kevin Feasel

Marc Lelijveld dives into the topic of Datamarts in Power BI:

The first post after my vacation. I was in doubt for a while whether I should write this one or not… given the preview state. Since one of the customers I work for asked me for advise today about Power BI Datamarts, I thought lets do it! They convinced me that it would be a valuable blog for many. I hope for you too.

What is this blog about? I will explain my first experiences with Power BI Datamarts which is currently in preview state. I will elaborate on my test cases, the value I see in Datamarts but also some open ends and where you can potentially position it in your Power BI solution architecture.

I think Marc ends up pretty far on the positive side of the opinion curve here relative to many early reviews.

Comments closed

Compacting Window Function Definitions

Published 2022-09-14 by Kevin Feasel

Rob Farley like a syntax change:

This was fine, but it did start to become a little cumbersome.

Enter SQL Server 2022. Not only do we get the ability to ignore nulls now, making it easy to get the last non-null value from a list, but we also get a WINDOW clause – part of the SELECT query itself, dropping in between the HAVING clause and the ORDER BY clause, allowing us to predefine those OVER clause segments.

Read on for the full scope of Rob’s thoughts.

Comments closed

Data Virtualization in Azure SQL Managed Instance

Published 2022-09-14 by Kevin Feasel

Mladen Andzic has an announcement:

We are excited to announce the general availability (GA) of data virtualization capabilities in Azure SQL Managed Instance, with improved query performance and managed identity as a new supported option for authenticating to storage accounts.  

The data virtualization enables you to execute Transact-SQL (T-SQL) queries on files storing data in common data formats in Azure Data Lake Storage Gen2 or Azure Blob Storage and combine it with relational data stored locally in the managed instance using logical joins. This way you can transparently access external data while keeping it in its original format and location. There is no data duplication or need to run and maintain ETL processes, which means that you can extract and deliver insights faster. The supported file formats are Parquet, CSV, and JSON.

This is similar to PolyBase in SQL Server 2019 but is a different underlying technology. In SQL Managed Instance, it looks like we only get API-based data virtualization, not the ODBC-based PolyBase we saw in SQL Server 2019.

Comments closed

Format_DateTime in KQL

Published 2022-09-13 by Kevin Feasel

Robert Cain continues a series on KQL:

Reformatting dates is very common when querying data. You may, for example, wish to come up with a more compact date than the default KQL returns. Perhaps you work for an international company. In some countries you may want the date in month/day/year format, while other countries would prefer day/month/year.

Fortunately Kusto provides the format_datetime function that allows you to do just this. We’ll take a look at it in this article.

Read on for a variety of examples.

Comments closed

Spark Query Optimization in Synapse

Published 2022-09-13 by Kevin Feasel

Daniel Coelho lays out a few optimizations in Azure Synapse Analytics Spark pools:

The Azure Synapse Analytics team has prominent engineers enhancing and contributing back to the Apache Spark project. One of our focus areas is Spark query optimization techniques, where Microsoft has decades of experience and is making significant contributions to the Apache Spark open source engine.

The attachment at the bottom of this blog post will be presented at the 48th International Conference on Very Large Databases (#VLDB2022) and covers the latest developments in query optimization for Apache Spark 3. Those optimizations were developed by Microsoft engineers and are available today in the Azure Synapse runtime for Apache Spark versions 3.1 and 3.2.

Check out the high-level updates as well as a complete technical paper laying out the changes.

Comments closed

Try out Chocolatey

Published 2022-09-13 by Kevin Feasel

Grant Fritchey makes a recommendation:

Chocolatey, as it says on the web site, is a package manager for Windows. But what does that mean? Basically, a package manager is a central place to install, upgrade and remove software. You get a database of the software you have installed, then it can help you to manage your software. Package managers are built into most operating systems. For example, apt or yum on a Linux box. However, not Windows. That’s where Chocolatey comes in.

I do like Chocolatey a lot as well and not just on VMs that I regularly need to rebuild.

Comments closed

Working with CROSS APPLY

Published 2022-09-13 by Kevin Feasel

Paul Randal takes us through one of my favorite operators:

Introduced by Microsoft in SQL Server 2005, SQL CROSS APPLY allows values to be passed from a table or view into a user-defined function or subquery. This tutorial will cover the incredibly useful and flexible APPLY operator, such as how the CROSS APPLY and OUTER APPLY operators work, how they’re like the INNER and LEFT OUTER JOIN, and give you some examples of both. All the examples use the AdventureWorks example database.

Later in the article, I’ll also discuss a highly pervasive SQL Server performance problem—one I still encounter with customers on a weekly basis. This problem is related to using the APPLY operator against a specific type of user-defined function. It’s so problematic and can overwhelm tempdb, bringing your SQL Server instance to a crawl!

If you’re not too familiar with APPLY in its two forms, read the whole thing.

Comments closed