Press "Enter" to skip to content

Month: August 2022

T-SQL Improvements in CTP 2.1

Published 2022-08-03 by Kevin Feasel

Itzik Ben-Gan looks at ways recent T-SQL improvements:

One of the complexities that is of a specific importance to this section is comparisons that potentially involve NULL comparands, such as ones that you use in filter and join predicates. Most operators that you use in such comparisons, including the equals (=) and different than (<>) operators, use three-valued logic. This means that there are three possible truth values as a result of a predicate that uses such operators: true, false and unknown. When both comparands are non-NULL, such operators return true or false as you would intuitively expect. When any of the comparands is NULL, including when both are NULL, such comparisons return the unknown truth value. Both filter and join predicates consider unknown as a non-match, and sometimes that’s not the behavior that you’re after.

The distinct predicate (IS [NOT] DISTINCT FROM) should simplify a lot of code in the wild.

Comments closed

Variables in DAX

Published 2022-08-03 by Kevin Feasel

Marco Russo and Alberto Ferrari show us how to use variables in DAX:

Variables were introduced in DAX in 2015 and so far, they have proven to be the best enhancement of the DAX language ever. When presented with the concept of variables, most newbies focus on performance improvement, thinking that you introduce variables in your code mainly to obtain better performance. Although variables can improve performance, performance is a minor advantage. There are several more important considerations that should encourage any DAX developer to make extensive use of variables. In this article we share a few considerations, along with best practices about variables and DAX.

Read on to see what makes variables so powerful.

Comments closed

Power BI CAT Guidance

Published 2022-08-03 by Kevin Feasel

Matthew Roche advises customers:

I’ve written previously about some of what the Power BI CAT team does, but the Power BI guidance documentation only gets a passing mention… and it’s worth going into more deeply.

A lot of what the Power BI CAT team does involves working with large enterprise customers. These customers are often trying to achieve difficult goals that often involve complex data architectures, and Power BI is often a significant part of their end-to-end information supply chain. We get involved when these enterprise customers need help achieving their strategic goals, and this help often includes helping them effectively use the existing capabilities of Power BI.

Read on to learn more about this guidance documentation.

Comments closed

B-Tree and Hash Indexes in Postgres

Published 2022-08-02 by Kevin Feasel

Paul Randal takes us through indexing in Postgres:

This article explores the PostgreSQL implementation of the B-Tree (the B stands for Balanced) and hash index data structures. As PostgreSQL grows in popularity as an open-source database system for developers and as a target for migrating from Oracle workloads, understanding how PostgreSQL indexes work is extremely important for database developers and administrators. PostgreSQL has several other types of indexes, such as GIN indexes, GiST indexes, and BRIN indexes. I will omit them for this article as they’re somewhat specialty indexes suited for text-based searches, geography, and other complex data types. And while B-Tree index usage makes up roughly 90% of use cases, hash indexes and their concepts are also important to understand.

Understanding and implementing the correct indexes for the workload is the foundation of any well-running relational database system. Adding and adjusting indexes to suit the workload has yielded some of the most significant performance gains over my many years of consulting. However, to add the right indexes, you must first understand them.

Read on to learn more. Even if you live in SQL Server, this is a really good article to read because the types of indexes available can differ radically between platforms and that naturally affects indexing strategy.

Comments closed

When Query Store Plan Forcing Fails

Published 2022-08-02 by Kevin Feasel

Joe Billingham roots out failure:

Query Store is a fantastic feature of both SQL Server and Azure SQL DB. It allows you to monitor how queries execute against the database which is invaluable for troubleshooting performance issues. More than that though, it gives you the option to force an erratic query to use a particular execution plan, this helps avoid queries from running with inefficient plans and provides predictability and stability on the server.

But it does fail for various reasons and Joe has a list of them.

Comments closed

SQL Server and Golang

Published 2022-08-02 by Kevin Feasel

Shane O’Neill goes long:

It’s all very well and good to open up Golang and write a FizzBuzz (note to self: write a FizzBuzz), but I still work with databases.

So before I do anything with Golang, I’d like to know: can it interact with databases. 

Granted, everything can, but how easy is it? And does trying to interact with databases kill any interest I have in the language.

So, let’s give it a go.

Click through to see what Shane came up with.

Comments closed

Azure Synapse Analytics July 2022 Updates

Published 2022-08-02 by Kevin Feasel

Ryan Majidimehr notes that the Azure Synapse Analytics team has been busy:

Azure Synapse Link for SQL is an automated system for replicating data from your transactional databases into a dedicated SQL pool in Azure Synapse Analytics. Starting this month, you can make trade-offs between cost and latency in Synapse Link for SQL by selecting the continuous or batch mode to replicate your data.  

By selecting “continuous mode”, the runtime will be running continuously so that any changes applied to the SQL database or SQL Server will be replicated to Synapse with low latency. Alternatively, when you select “batch mode” with a specified interval, the changes applied to the SQL database or SQL Server will be accumulated and replicated to Synapse in batch mode with the specified interval. This can save cost as you are only charged for the time the runtime is required to replicate data. After each batch of data is replicated, the runtime will be shut down automatically. 

Click through for the complete list.

Comments closed

Bidirectional Transactional Replication and Managed Instances

Published 2022-08-02 by Kevin Feasel

Holger Linke builds a transactional replication topology with a couple of twists:

Bidirectional transactional replication is a specific Transactional Replication topology that allows two SQL Server instances or databases to replicate changes to each other. Each of the two databases publishes data and then subscribes to a publication with the same data from the other database. The “@loopback_detection” feature ensures that changes are only sent to the Subscriber and do not result in the changes being sent back to the Publisher.

The databases that are providing the publication/subscription pairs can be hosted either on the same SQL instance or on two different SQL instances. The SQL instances can either be SQL Server on-premise, SQL Server hosted in a Virtual Machine, SQL Managed Instance on Azure, or a combination of each. You just have to make sure that the instances can connect to each other. If you add a subscription by using the fully-qualified domain name (FQDN), verify that the server name (@@SERVERNAME) of the Subscriber returns the FQDN. If the Subscriber server name does not return the FQDN, changes that originate from that Subscriber may cause primary key violations.

Read on for the scripts.

Comments closed

An Overview of Power BI Datamarts

Published 2022-08-02 by Kevin Feasel

Melissa Coates has a new diagram for us:

An new diagram of Power BI Datamarts is now available. It includes the technical components of a Power BI datamart.

Want to download a copy? Head on over to the Diagrams page where the latest version will always be. You can download a PDF copy of it from the Diagrams page.

This blog post includes a brief summary of each technical component of a Power BI datamart. Datamarts are really new, and they’ll be changing as (1) the functionality matures over time, and (2) based on feedback that customers give. Therefore, I’ve kept the descriptions below brief.

Despite their brevity, the descriptions are worth the read.

Comments closed

A Primer on Contrast and CVD

Published 2022-08-01 by Kevin Feasel

Tim Brock covers color vision deficiency:

In this blog post and a follow up I’m going to describe why and how we used theming to make diffify.com more accessible to users who suffer from some common visual impairments. Here in Part 1 I’ll cover some of the science and the terminology. Part 2 will look at the actual changes we made.

Click through for a brief discussion of contrast sensitivity and a longer one on color vision deficiency, including why color choice matters.

Comments closed