Press "Enter" to skip to content

Curated SQL Posts

The KQL Extend Operator

Published 2022-05-27 by Kevin Feasel

Robert Cain continues a series on learning KQL:

When dealing with data, it’s not at all uncommon to want to create a new column of data by performing a calculation with two other columns. A common example is taking two stored columns, the purchase price of an item, and its shipping cost, then adding them together to get a column which wasn’t stored in your dataset, the total amount of the sale.

The Kusto Query Language lets you accomplish this through the extend operator. This operator allows you to manifest new columns in your output data, based on calculations.

As always, Robert has plenty of examples available to view.

Comments closed

Server Roles in Azure SQL DB and SQL Server 2022

Published 2022-05-27 by Kevin Feasel

Andreas Wolter has some new server roles for us to use:

The new server-roles that can be assigned to server logins to enable customers to assign and delegate job functions for server-wide metadata access and access to certain management commands without requiring Server Admin or AAD Admin privileges. This helps comply with the Principle of Least Privilege and implement role separation (sometimes also interchangeably referred to as Separation of Duties).

There are seven such roles for SQL Server 2022, though we saw three of them already in Azure SQL DB. Andreas takes us through the four new roles in detail.

Comments closed

IF Branching, Local Variables, and Stored Procedures

Published 2022-05-27 by Kevin Feasel

Erik Darling continues a quest. Part 3 involves local variables:

What never seems to get a bad name, despite numerical supremacy in producing terrible results, are local variables.

In this particular scenario, I see developers use them to try to beat “parameter sniffing” to no avail.

A chorus of “it seemed to work at the time”, “I think it made things a little better”, “it worked on my machine”, and all that will ensue.

But we know the truth.

The next part is around stored procedures:

You know and I know and everyone knows that stored procedures are wonderful things that let you tune queries in magickal ways that stupid ORMs and ad hoc queries don’t really allow for.

Sorry about your incessant need to use lesser ways to manifest queries. They just don’t stack up.

But since we’re going to go high brow together, we need to learn how to make sure we don’t do anything to tarnish the image of our beloved stored procedures.

Erik notes that stored procedures are part of the solution but there’s a bit more that we need.

Comments closed

End-to-End Analytics with Power BI Datamarts

Published 2022-05-27 by Kevin Feasel

Katy Young explains the value of Power BI datamarts:

A datamart can be thought of as a subject-specific data warehouse, often containing data related to one domain or line of business within an organization. Because datamarts are often aligned to a particular business use case, analysts don’t need to write complex queries over large volumes of data as they would against a more traditional data warehouse. This reduces time to insight for organizations and promotes self-service analytics by making structured data accessible to exactly the people who need it.

Read on for more information.

Comments closed

Seeding AG Replicas from Snapshots in SQL Server 2022

Published 2022-05-27 by Kevin Feasel

Anthony Nocentino is excited about using storage snapshots in SQL Server 2022:

But what if I told you that you could seed your Availability Group from a storage-based snapshot and that the re-seeding process can be nearly instantaneous?

In addition to saving you time, this process saves your database systems from the CPU, network, and disk consumption that comes with direct seeding and using backups and restores to seed.

This process described in this post is imlemented on Pure Storage’s FlashArray and works cloud scenarios on Pure’s Cloud Block Store.

Click through to see how.

Comments closed

Creating a SQL Server 2022 dacpac

Published 2022-05-27 by Kevin Feasel

Kevin Chant gets an upgrade:

In this post I want to cover how you can create a dacpac for SQL Server 2022 databases using sqlpackage. So that you keep the new SQL Server 2022 compatibility level when you deploy new databases.

Just to clarify, a dacpac file is a special type of file which contains details about SQL Server database objects. Which you can use to deploy database updates to other SQL Server databases.

Read on for initial thoughts, a post-upgrade experience, and more.

Comments closed

Power BI Datamarts

Published 2022-05-26 by Kevin Feasel

Reza Rad has a four-parter on datamarts in Power BI. First up is the simple question:

One of the newest additions to the Power BI components is the Datamart. Power BI Datamart is more than just another feature, it is a major milestone where the development of Power BI solutions will be revolutionized based on that. This is a feature that helps both citizen data analysts and developers. In this article and video, I’ll explain what is a Power BI datamart, how it helps you in your Power BI implementation, and why you should use it?

Next up, how do you create one?

In the previous article, you learned what is a Datamart and the use cases of that in a Power BI implementation. In this article and video, you will have your first experience with Datamarts and learn through an example about what it is and how it works in detail. The interesting thing in all the steps below is that you just need a web browser to build the datamart.

Thirdly, we have datamart components:

What is a Power BI Datamart underneath? Can you connect to the database generated by Power BI Datamart? how the Dataset associated with the Datamart can be used? Is there a linage view? In this article and video, I’ll explain These and you will learn about the components of a Power BI Datamart. If you are new to Power BI Datamarts, this article explains what it is and its use cases and this article gets you through the Datamart editor and your first experience with it.

Reza rounds out this series of videos with how datamarts fit into the broader Power BI ecosystem:

Power BI Datamart is integrating well with other components of the Power BI ecosystem (such as workspaces, sharing, deployment pipelines, endorsements, sensitivity labels, etc). In this article and video, I’ll explain how Power BI works with other features and services in Power BI implementation. If you are new to Power BI Datamarts, this article explains what it is and its use cases and this article gets you through the Datamart editor and your first experience with it. You can also learn about the components of the Power BI Datamart from this article.

Click through for four videos hitting the high points.

Comments closed

Square Area Charts

Published 2022-05-26 by Kevin Feasel

Simon Rowe fills in a grid:

A square area chart—also known as a waffle chart—is a square display.  They often consist of a 10×10 grid containing 100 individual squares. The squares are then coloured based on the data that’s being visualised. The chart gets its food-based name from its resemblance to a tasty waffle. (Sometimes this type of chart is referred to as a “unit chart,” although that term applies more appropriately to a broader category of visualisations, which tend to use discrete icons, rather than squares, to represent the values.)

Click through for several good use cases.

Comments closed

T-SQL Enhancements in SQL Server 2022

Published 2022-05-26 by Kevin Feasel

Aaron Bertrand notes some T-SQL improvements:

A few of the most useful changes I’ve been able to play with in SQL Server 2022 so far:

– GREATEST / LEAST

– STRING_SPLIT

– DATE_BUCKET

– GENERATE_SERIES

In this tip, I’ll explain each one, and show some practical use cases.

Click through for more information. I particularly like GREATEST() and LEAST() but GENERATE_SERIES() could be very useful as well. Some of this stuff was first made available in Azure SQL Edge.

Comments closed