Press "Enter" to skip to content

Curated SQL Posts

Branch Cleanup with git prune

Published 2024-04-19 by Kevin Feasel

Steve Jones breaks out the branch cutters:

As I’ve been working with SQL Saturday and managing changes to events, I’ve accumulated a lot of branches. Even though I’m a solo developer, I decided to use branches, as I expect others to share this load in the future. This post looks at how to start cleaning those up.

As a team gets larger, the necessity of regular branch maintenance increases, but so does the complexity of it: it’s really easy to have one person not pay attention to the e-mails and get burned when old branch deletion en masse does happen.

Leave a Comment

Database Subetting and Data Generation

Published 2024-04-19 by Kevin Feasel

Phil Factor tells us about two possibilities for loading a lower environment:

When dealing with the development, testing and releasing of new versions of an existing production database, developers like to use their existing production data. In doing so, the development team will be hit with the difficulties of managing and accommodating the large amount of storage used by a typical production database. It’s not a new problem because the practical storage capacity has grown over the years in line with our ingenuity in finding ways of using it.

To deal with using production data for testing, we generally want to reduce its size by extracting a subset of the entities from a ‘production’ database, anonymized and with referential integrity intact. We then deliver this subset to the various development environments.

Phil gets into some detail on the process behind subsetting and then covers data generation as an alternative.

Leave a Comment

Using Powershell to Set the SQL Server Port Static

Published 2024-04-19 by Kevin Feasel

Vlad Drumea doesn’t want a dynamic port number:

This post demos a script that I’ve put together to automate the configuration of the static TCP port for a SQL Server instance using PowerShell.

The script is derived from another PowerShell script that I’ve written to help spin up SQL Server test instances in my home lab.

This should be helpful you’re working in a restrictive environment where you can’t install additional PowerShell modules, and you couldn’t take advantage of dbatools’ Set-DbaTcpPort.

Click through for the script, but also use dbatools whenever you can because it’s a good product and I haven’t done any unpaid shilling for them in far too long.

Leave a Comment

The Equivalency of Views and Common Table Expressions

Published 2024-04-19 by Kevin Feasel

Erik Darling makes a comparison:

Perhaps one of the most exhausting parts of my job is disabusing developers of the notion that common table expressions hold some weight in gold over any other abstraction layer in SQL Server.

Think of it like this:

  • Views are like a permanent home
  • Common table expressions are like a mobile home

You can put equally terrible queries in either one and expect equally terrible results.

Read the whole thing for additional spicy analogies and similes.

I would say that I certainly do not disdain views, so much as I see them as a yellow flag. It’s really easy to go from helpful views to views nested in views nested in views like malevolent Matryoshka dolls. It’s harder to do that with common table expressions before that reptilian part of your brain kicks in and says that “hey, maybe this isn’t the greatest idea I’ve ever had.” Not that this will stop some people, admittedly…

Leave a Comment

Processes in PostgreSQL

Published 2024-04-19 by Kevin Feasel

Semab Tariq continues a series on internals in PostgreSQL:

PostgreSQL is a client/server type relational database management system. It has a multi-process architecture that runs on a single host. A collection of multiple processes that manage a database cluster is usually referred to as a PostgreSQL server. In PostgreSQL, every operation is treated as a process, and each action we undertake within PostgreSQL follows an append-only approach. This means that every time we execute an action such as an insert, update, or delete, a new tuple is created rather than modifying the existing data directly.

Consequently, PostgreSQL does not execute updates or deletes in place. Instead, it appends new data or marks existing data as obsolete. This append-only methodology ensures data integrity and allows for efficient management of database changes over time.

Read on to learn more about how these processes work.

Leave a Comment

Azure Regions and Pricing

Published 2024-04-18 by Kevin Feasel

Koen Verbeeck has a public service announcement:

Today I was having a nice discussion with some colleagues about Fabric and pricing/licensing came up. I mentioned an F2 is only around €250 a month, but a colleague said “no no, it’s over €300”.

There can be significant differences in prices for services based on region, not just for Microsoft Fabric, but also for a variety of services. This will depend on how new the hardware is, how much demand there is in the region, and a few other factors. Cloud Price does a good job of keeping track of VM pricing by region, and even tells you the cheapest region for each class of VM. For other services, you may have to trawl through Azure APIs and pricing pages to get the best deal.

Leave a Comment

Data Analysis with Window Functions

Published 2024-04-18 by Kevin Feasel

Erika Balla looks out the window:

Window functions are an advanced feature of SQL that provides powerful tools for detailed data analysis and manipulation without grouping data into single output rows, which is common in aggregate functions. These functions operate on a set of rows and return a value for each row based on the calculation against the set.

In this article, we delve into window functions in SQL Server. You will learn how to apply various window functions, including moving averages, ranking, and cumulative sums, to achieve comprehensive analytics on data sets. 

Click through for several examples.

Leave a Comment

pl/dotnet Version 0.99

Published 2024-04-18 by Kevin Feasel

Brick Abode announces F# and C# support within Postgres:

pl/dotnet adds full support for C# and F# to PostgreSQL. 0.99 is our public beta release; we wish to share its amazingness with the world.

  • We support all PL operations: functions, procedures, DO, SPI, triggers, records, SRF, OUT/INOUT, table functions, etc
  • We natively support 40 out of 46 standard user types, the most of any external PL
  • Fully NPGSQL-compatible, and SPI is exposed through the NPGSQL API for maximum compatibility
  • In our benchmarks, C# and F# are the fastest Procedural Languages in PostrgreSQL
  • All features are fully tested for both C# and F#, with 1013 unit tests
  • 100% free software under the PostgreSQL license

This is a beta release; we invite usage and welcome feedback.

Look at me, side-eyeing SQL Server and how SQLCLR still doesn’t have F# support. I still maintain that the single biggest mistake Microsoft made around SQLCLR was adopting the “safe” and “unsafe” mode language. C# developers understood that “unsafe” meant you could get access to pointers and other internals that .NET languages typically hide from us. But try explaining that to a DBA, who doesn’t understand the language or the concepts.

On the bright side, .NET languages are the fastest procedural languages for Postgres, so that’s pretty neat. H/T Sergey Tihon.

Leave a Comment

Waiting for a Job to Complete in Powershell

Published 2024-04-18 by Kevin Feasel

Patrick Grueanuer waits for a job:

The cmdlet Wait-Job waits until one or all of the PowerShell jobs running in the session are in a terminating state. In this blog post I will show you an example you can build on. Let’s get started.

Start-Job creates one or more PowerShell background jobs. These jobs are running hidden in the background and enable you to continue your work in PowerShell. This example starts a port scan background job.

Click through to see how Start-Job works and what you can do with other job-related cmdlets.

Leave a Comment

Refreshing a Power BI Semantic Model via Fabric Pipelines

Published 2024-04-18 by Kevin Feasel

Marc Lelijveld builds a pipeline:

Recently, Microsoft released a new activity type to trigger Power BI Semantic Model refreshes. A great step forward to have a native pipeline activity and no longer need to setup complex steps with APIs and authentication manually. Or is there still a case?

In this blog I will elaborate on what this new Pipeline activity exactly is, various scenarios in which it can be applied and finally some edge cases and shortcomings.

Click through to see how it works.

Leave a Comment