Press "Enter" to skip to content

Author: Kevin Feasel

A Primer on Database Constraints in MySQL

Published 2023-09-08 by Kevin Feasel

Robert Sheldon creates some keys:

MySQL supports six basic types of constraints for ensuring data integrity: PRIMARY KEY, NOT NULL, DEFAULT, CHECK, UNIQUE, and FOREIGN KEY. In this article, I introduce you to each constraint type and provide examples for how they work. The examples include a series of CREATE TABLE statements that demonstrate different ways to incorporate constraints into your table definitions. If you’re not familiar with the CREATE TABLE statement or how to create tables in a MySQL database, refer to an earlier article in this series that introduces you to the statement.

In short, they support the same set that SQL Server users are used to. But do read on to see the nuances behind each of these.

Comments closed

Don’t Try These with SQL MI and Private Endpoints

Published 2023-09-08 by Kevin Feasel

Zoran Rilak wraps up a series on Azure SQL Managed Instance and its support for private endpoints:

The first two installments of this mini-series discussed a couple of basic and advanced scenarios involving private endpoints. Today we’ll look at some ways private endpoints cannot be used to implement scenarios where one might expect otherwise.

Read on for four of these in total, laying out things you cannot do via private endpoint to a SQL Managed Instance. In fairness, Zoran also provides what I would consider reasonable work-arounds for each of those: have a VM jumpbox in the same virtual network for DAC connections, peer your virtual networks for replication, and so on.

Comments closed

Building a Weierstrass Function in R

Published 2023-09-07 by Kevin Feasel

Tomaz Kastrun won’t let you take a derivative:

Coming from the simple sine function (remember of Fourier series), German mathematician Karl Weierstrass became the first to publish an example of a continuous, nowhere
differentiable function. Weierstrass function (originally defined as a Fourier series) was the first instance in which the idea that a continuous function must be differentiable was introduced. This is an example of a fractal in a function (known as a fractal function) and also another of pathological functions (runs counter to some intuition).

Click through for an example of this in R.

Comments closed

Appropriate Uses of Jitter in Graphs

Published 2023-09-07 by Kevin Feasel

Steven Sanderson shakes things up:

As an R programmer, one of the most useful functions to know is the jitter function. The jitter function is used to add random noise to a numeric vector, which can be helpful when visualizing data in a scatterplot. By using the jitter function, we can get a better picture of the true underlying relationship between two variables in a dataset.

Read on to get an idea of how to use jitter, though I recommend making it very clear to chart viewers that you are, in fact, using jitter, as it can be easy to misinterpret the jitter as actual value locations.

Comments closed

Creating a Power BI Dataset and Report via DirectLake

Published 2023-09-07 by Kevin Feasel

Gilbert Quevauvilliers finishes up a series:

In the final part of my series, I am going to be creating the Power BI DirectLake dataset and report from my tables that I had previously loaded into the lakehouse.

In this series I am going to show you all the steps I did to have the successful outcome I had with my client where I created the dataset (measures and fields) and the Power BI report.

Click through for links to the prior posts, as well as a walkthrough on creating a DirectLake asset in Power BI.

Comments closed

Troubleshooting a Downed SQL Server

Published 2023-09-07 by Kevin Feasel

Kevin Hill takes us through an issue:

This is a stand-alone bare metal server located at a hosting facility with some local storage as well as NAS storage.  SQL Server 2016 Standard Edition, 8 cores, 128GB RAM.  The primary database is nearing 1TB and is has multiple filegroups (Active, Archive, Indexes, etc.).   This database and apps that touch it ARE the company.

Read on for Kevin’s process, which was a solid bit of troubleshooting.

Comments closed

Value and Hash Encoding in VertiPaq

Published 2023-09-07 by Kevin Feasel

Kristyna Hughes looks at column encodings:

Power BI encoding is a powerful optimizing option that is often overlooked because it’s not visible in neither the Power BI Desktop tool nor in Power BI Service. Natively, the VertiPaq engine in Power BI investigates all columns in the data model and determines how it can store that data most efficiently. To achieve maximum compression, the VertiPaq engine starts by encoding each column which determines the method of compression applied to that column. There are a couple types of encoding – value and hash.

Read on to learn the difference, as well as how to push your columns to use a specific type of encoding.

Comments closed

Transaction Log Files and Instant File Initialization

Published 2023-09-07 by Kevin Feasel

Erik Darling preps us for SQL Server 2022:

Look, I don’t blame you if you haven’t dug deep into what SQL Server 2022 has to offer just yet. It’s hard enough to keep up with all the problems fixed and caused by cumulative updates.

One thing you may want to pay attention to is how transaction log files are grown and VLFs are created, especially for new databases.

Read on to see what has changed there.

Comments closed

An Overview of SQL Server Indexes

Published 2023-09-07 by Kevin Feasel

Adron Hall gives us a tour of indexes in several relational database management systems:

1. Structure of the Craft: Dive deep, and you’d find tree-like structures, be it the B-tree or its illustrious cousins – the B+ tree and B* tree. These aren’t ordinary trees; they’re a labyrinth that efficiently guides the system to the row it seeks in a jiffy.
2. Guiding Stars – Pointers: Each entry in this labyrinth isn’t just a dead end. It carries a key value and – wait for it – a pointer. Think of it as a magical compass pointing directly to the treasure, or in this case, the row in the table.
3. Supercharged Searches: Now, imagine sifting through a library without a catalog – agonizingly slow, right? That’s how a database without an index feels. But bring in an index, and suddenly even the vastest of tables become a playground of swift searches.

Read on for an overview of what purposes indexes fulfill in these data platforms. I use “purposes they fulfill” rather than “types of indexes” because there are a couple entries on the list which are not, strictly speaking, actual types of indexes.

Comments closed

First Impressions of DAX Optimizer

Published 2023-09-07 by Kevin Feasel

Nikola Ilic takes a look:

A few months ago, while scrolling through posts on social media, one of them immediately grabbed my attention! It was about a new tool, called DAX Optimizer, which promised to identify and remove performance bottlenecks in your DAX formulas. For all of us dealing with optimizing Power BI reports on a day-to-day basis, that was a huge promise (and one I was impatiently waiting to see in action).

One important note is that this is not a free tool, as Nikola mentions. Read on for more thoughts about how it works, what it picks up, and whether it’s a good fit for your environment given the price.

Comments closed