Press "Enter" to skip to content

Curated SQL Posts

Using Database Projects for Declarative Database Development

Haroon Ashraf explains the principles behind database projects and demonstrates their use:

This article is all about declarative database development using Azure Data Studio for both beginners and professionals who are new to it.

Additionally, some professional life tips in the context of the topic are also shared. The importance of declarative database development over its counterparts can also be fairly understood by going through this article.

Conceptually, I love it. Focusing on the end state is easier to understand. The problem I run into is that the tooling for generating change scripts is not great. It works for trivial database sizes, but as soon as you start talking dozens or hundreds of gigabytes of data, database projects have a tendency to do rather drastic changes which require rebuilding the table, when they could (with a bit of human smarts) perform an action which is much less disruptive. So in the end, you still end up needing to create change scripts.

Comments closed

From Excel to SQL Server via Powershell

Kevin Wilkie combines Excel and dbatools like a mad scientist:

Those ways work great if you’re inserting one tab of an Excel spreadsheet. I don’t know about y’all, but I don’t want to have to do all of this work for each tab – especially if I have 30+ tabs to import into SQL Server (and yes, I’ve been asked to do that quite a few times.)

One of the easiest ways I’ve seen to insert a lot of data (once you’ve made sure that the data is how you want it) is to use the power of PowerShell.

Read on to see how.

Comments closed

Preventing Calendar Overrun in Power BI

Matt Allington updates an older article:

Consider the example below where the CalendarYear is filtered for 2019 and the values of the measures Total Sales and Total Sales YTD are displayed by month. As you can see, the total sales are shown up to July 2019. This is because with the sample data, the last sales date is somewhere in July 2019. However, the values of Total Sales YTD are repeated all the way until the end of year (July 2019 to December 2019). This is what I call Calendar Over Run. It is common to want to prevent this overrun.

Read on for two separate methods of preventing this visual issue.

Comments closed

The Case Against Consistency in Code

Mark Seemann takes a contrarian approach:

In this essay, I use the term coding style to indicate a set of rules that governs how code should be formatted. This may include rules about where you put brackets, whether to use tabs or spaces, which naming conventions to use, maximum line width, in C# whether you should use the var keyword or explicit variable declaration, and so on.

As already stated, I can appreciate consistency in code as much as the next programmer. I’ve seen more than one code base, however, where a formal coding style contributed to ossification.

Read on for the argument. In this case, the language is C# but the concept applies across the board.

Comments closed

Column-Level Encryption in SQL Server

David Fowler takes us through a venerable (here by which I mostly just mean “old”) technique for encryption in SQL Server:

SQL Server gives us a few different options when it comes to encryption and I’m going to take a look at each of them at some point in this series but in this first post in the series I want to look at column level encryption.

Before we can even start thinking about encrypting our data, there are a few things that we’re going to need to set up first.

Although I joke about column-level encryption, David shows us just how easy it is to implement. It’s quite useful if you have just one or two columns in the database which need to be encrypted at rest and you don’t want to (or can’t) have the application handle it directly.

Comments closed

Grouping Outputs of Pester Tests

Shane O’Neill has fun with Pester:

I’ve been working with Pester v5 lately.

Pester v5 with PowerShell v5 at work & Pester v5 with PowerShell Core outside of work.

There are quite a few changes from Pester version 3, so it’s almost like learning a new language… except it’s based on slang. I think that I’m speaking eloquently, and then I’ve suddenly insulted someone and Pester no longer wants to play nice with me.

Read on to see how to make those Pester outputs look a lot nicer.

Comments closed

Running SQL Server Containers from Scratch

Andrew Pruski tells us there is no spoon:

I’ve been interested (obsessed?) with running SQL Server in containers for a while now, ever since I saw how quick and easy it was to spin one up. That interest has led me down some rabbit holes for the last few years as I’ve been digging into exactly how containers work.

The weirdest concept I had to get my head around was that containers aren’t actually a thing.

Containers are just processes running on a host that implement a set of Linux constructs in order to achieve isolation.

So if we know what constructs are used…shouldn’t we be able to build our own container from scratch?

Read on as Andrew breaks out the three necessary constructs and dives into it.

Comments closed

Plotting Correlation Analyses in R

Finnstats shows a few techniques for plotting correlation in R:

Correlation analysis, correlation is a term that is a measure of the strength of a relationship between two variables.

Pearson’s Product-Moment Correlation

One of the most common measures of correlation is Pearson’s product-moment correlation, which is commonly referred to simply as the correlation, or just the letter r.

Correlation shows the strength of a relationship between two variables and is expressed numerically by the correlation coefficient.

Click through for examples from several packages. H/T R-Bloggers.

Comments closed