Press "Enter" to skip to content

Curated SQL Posts

Thinking about Execution Plan Icons

Published 2023-04-19 by Kevin Feasel

Hugo Kornelis polls the community:

Fast forward to today. More than five years have passed since I published the first pages of the SQL Server Execution Plan Reference. The world has changed, in many ways. But the icons on the reference pages have not. One might wonder whether the choices I made over five years ago are still relevant today. Or rather, I know that they are not all relevant anymore, I know that there is very good reason to rethink those choices. But this time, I prefer not to do this on my own. This time I am asking your feedback.

Click through for the entirety of Hugo’s request. My recommendation would be to keep the SSMS/ADS icon set but not old SSMS or SQL Sentry Plan Explorer. I like and still use Plan Explorer (and it’s the only third-party SQL Server execution plan reader I regularly use) but don’t need to see that icon set in Hugo’s documentation to understand what he’s covering.

Comments closed

DAX Updates: RANK and ROWNUMBER

Published 2023-04-18 by Kevin Feasel

Jeffrey Wang takes us through some DAX language updates:

In the April 2023 release of Power BI Desktop, two new functions, RANK and ROWNUMBER, have been added to the DAX window functions family, along with significant enhancements to the ORDERBY sub-function. These improvements allow ORDERBY to support sorting by arbitrary DAX scalar expressions, rather than being limited to column names. This not only benefits the new functions, but also existing window functions that we have previously discussed here and here. You might be wondering, since we have had RANKX and RANK.EQ since the inception of DAX, why do we need another rank function? In today’s blog, we will address this question and explore other considerations related to using these new functionalities.

Click through for examples and caveats.

Comments closed

Building an Azure DevOps YAML Pipeline

Published 2023-04-18 by Kevin Feasel

Olivier Van Steenlandt busts out the YAML:

In previous blog posts, I explained how to automate the Database Project Build & Deployment process using Azure DevOps (Release) Pipelines. These blog posts focused on setting up as easily as possible using the Classic Editor.

In this blog post, I’m going through the steps of setting up a build pipeline using YAML.

Read on to learn why the YAML-based approach is the best option for ADO and how to build a pipeline.

Comments closed

Building a Report Development Workflow for Power BI

Published 2023-04-18 by Kevin Feasel

Richard Swinbank thinks about process:

In this series I’ll be talking a lot about workflow. Loosely speaking, report development workflow is the set of steps you have to take to create or update a Power BI report. This typically starts in Power BI desktop, and doesn’t end until users are able to get business value out of a report in the Power BI service.

A good workflow frees a report developer to do the things they like and are good at – understanding business problems, and crafting beautiful reports to help solve them. Anything that that doesn’t contribute to that just gets in the way – a bad workflow clutters up the development experience with peripheral tasks like version control, managing environments and performing deployments.

Richard shares a good number of thoughts on the matter, so check them out.

Comments closed

Purging WSUS Synchronization Events

Published 2023-04-17 by Kevin Feasel

Hannah Vernon has a script:

Since Windows Server Update Services synchronizes the list of Windows Updates from the source Microsoft Servers on a regular basis, the history of sync events can become quite tedious to load in the User Interface. Frustratingly, there is no way through the user interface to remove old history for synchronization events. The SQL Server T-SQL code below creates a stored procedure that can be used to cleanup old events prior to a particular cut-off date. I run the code via a SQL Server Agent job daily, with a cut-off date of 30 days ago.

Click through for the stored procedure.

Comments closed

Learning Important Postgres Settings for SQL Server DBAs

Published 2023-04-17 by Kevin Feasel

Ryan Booz helps a SQL Server DBA out:

Five years ago, I began my transition back to using PostgreSQL full-time, and it wasn’t the smoothest journey. One of the biggest reasons I struggled to make progress learning PostgreSQL early on was simply not knowing what I should be looking for. In fact, I often have conversations multiple times a month about the differences between SQL Server and PostgreSQL with folks setting out on a similar journey.

My guess is that you’re trying to figure out the same things, which is how you ended up on this series of posts.

Read on to take advantage of Ryan’s pain and suffering.

Comments closed

Postgres GIS Calculations by SRID

Published 2023-04-17 by Kevin Feasel

Ryan Lambert talks accuracy:

A common use case with PostGIS data is to calculate things, such as distances between points, lengths of lines, and the area of polygons. The topic of accuracy, or inaccuracy, with GEOMETRY data comes up often. The most frequent offenders are generic SRIDs such as 3857 and 4326. In some projects accuracy is paramount. Non-negotiable. On the other hand, plenty of projects do not need accurate calculations. Those projects often rely on relationships between calculations, not the actual values of the calculations themselves. If Coffee shop Y is 4 times further away than Coffee shop Z. I’ll often go to Coffee shop Z just based on that.

In most cases, users should still understand how significant the errors are. This post explores one approach to determine the how accurate (or not!) the calculations of a given SRID are in a particular region, based on latitude (North/South). The queries used in this post can be adjusted for your specific area.

Click through to see how much the choice of SRID can impact your results.

Comments closed

Performing a Pareto Calculation in DAX

Published 2023-04-17 by Kevin Feasel

Phil Seamark does some manufacturing analysis:

I always enjoy it when we get new DAX functions, especially so for the new set of WINDOW Functions recently added. As part of the April 2023 release of Power BI Desktop, we now have a RANK function and the ability to use a measure to control the order within the existing WINDOW function.

The first thing that sprung to my mind was to see how a Pareto calculation might leverage the new capability.

The basic idea of a Pareto calculation is to create a curve like representation of data ordered from largest to smallest.

Read on to see how.

2 Comments