Press "Enter" to skip to content

Day: May 27, 2022

The KQL Extend Operator

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

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

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

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

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

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