Press "Enter" to skip to content

Curated SQL Posts

A Primer on ACID Properties and Transactions

Published 2024-10-30 by Kevin Feasel

Joe Celko covers four useful properties of database transactions:

ACID is a cute acronym for Atomicity, Consistency, Isolation, and Durability. This is a set of properties we want in transactions in a database. The original work was done by the late Jim Gray, but this acronym is due to Andreas Reuter and Theo Härder at IBM. These four properties as defined by Reuter and Härder are as follows:

Click through for a description of each property, as well as a brief overview of isolation levels.

Comments closed

Sending E-Mail from SQL Server and Snowflake

Published 2024-10-30 by Kevin Feasel

Kevin Wilkie says you’ve got mail:

For the many other parameters you can use with sp_sendmail, please refer to the Microsoft Documentation found here.

After running this stored procedure, SQL Server queues the email to be sent. From there, you should check often to see if it was actually sent. (And yes, it can get stuck there forever.)

With, Snowflake, it’s actually fairly close to the same basic principle as SQL Server.

Click through for the syntax for both systems.

Comments closed

KQLMagic in Fabric Runtime 1.3

Published 2024-10-30 by Kevin Feasel

Sandeep Pawar spreads the news:

I wrote a blog last year on the usefulness of KQLMagic command in Fabric notebook and made a suggestion that it should be part of the default runtime. Well, guess what – it’s now in the Fabric Runtime 1.3. No installation necessary and authentication is handled automatically.

Read on to learn more about how you can use KQLMagic in a Microsoft Fabric notebook to read from an Eventhouse.

Comments closed

Solid Practices for Power BI

Published 2024-10-30 by Kevin Feasel

Paul Turley has the beginnings of a new series:

It’s time for a refresher and reboot on this important topic. Much has changed in the Power BI world, the core design principles remain the same, practices and architecture patterns have evolved over the past few years. Power BI has grown up in the enterprise space and Microsoft Fabric now adds new options and capabilities. Back in 2020, I began writing a series of blog posts titled “Doing Power BI the Right Way” and it has become my mission to evolve and maintain a current collection of the most important best practice recommendations. This has been my passion and topic of several conference talks, user group sessions and a book currently in development for O’Reilly that will help you prepare for the PL-300 Power BI Analyst exam and then guide you apply enterprise best practices in your solutions.

I work with hundreds of consulting clients who go through the same cycles, having the same experiences, facing the same challenges, many making the same mistakes, and many learning some of the same lessons. The purpose of this series is to share those lessons with you.

Click through for the overview, as well as an outline of what Paul will include in this series.

Comments closed

Viewing Storage Consumption in Microsoft Fabric

Published 2024-10-30 by Kevin Feasel

Gilbert Quevauvilliers wants to know about storage utilization in Microsoft Fabric:

This blog post will show you how to understand what is consuming your Fabric Storage.

If you want to know how I got this data, please read my previous blog post View all your Storage consumed in Microsoft Fabric – Lakehouse Files, Tables and Warehouses – FourMoo

With this Semantic model below, I could also create alerts to notify based on certain thresholds. For example, if total storage in a single App workspace is more than 100GB send me an alert (This could be done using Power Automate). Or it could be on too many files being stored, or even looking at the Parquet file sizes and if they are too small they would then need to be optimised (for better performance).

Click through for the report.

Comments closed

Security Risk Profile in AI-Generated Code

Published 2024-10-29 by Kevin Feasel

Jerome Robert reviews the papers:

As such, nowadays, almost all developers use some form of AI-generated code — and they absolutely should. AI tools make developers’ lives easier by leveraging the knowledge cultivated by the development community over time and across the globe to overcome obstacles that, while potentially new and challenging to them, have long been addressed. They can reasonably trust that code to perform the function they want to achieve — and can test it to be sure.

But can they trust that code to be secure? Absolutely not. With all that time and work spent committing functional code, just as much, if not more, is spent navigating the security backlog afterward.

Click through for a summary of two recent academic papers, as well as links to the papers themselves.

Comments closed

An Intro to Power Apps

Published 2024-10-29 by Kevin Feasel

Vikash Kumar has a two-parter. First up is an introduction to Power Apps:

Microsoft PowerApps is a tool within the Microsoft Power Platform suite that enables users to create custom apps without extensive coding knowledge. It’s a low-code platform that empowers both technical and non-technical users to build apps quickly. Whether you’re looking to automate business processes or solve specific problems, PowerApps provides the tools you need.

Next is a primer on Canvas Apps in Power Apps:

Canvas Apps are applications where you start with a blank canvas and design the user interface from scratch. You have complete control over the layout, appearance, and functionality of the app by dragging and dropping components onto the canvas. This makes Canvas Apps ideal for creating custom apps that match your specific design and workflow requirements.

Power Apps is something I’ve never really gotten into. I don’t think it’s extremely intuitive, which can be a drawback in a tool whose primary audience is non-developers.

Comments closed

Comparing PL/SQL to PL/pgSQL

Published 2024-10-29 by Kevin Feasel

Umair Shahid switches dialects:

To address these limitations, database systems like Oracle and PostgreSQL offer procedural extensions to SQL. Oracle’s PL/SQL and PostgreSQL’s PL/pgSQL allow developers to implement more advanced logic, including loops, conditionals, error handling, and transaction control—all within the database. These procedural languages enhance SQL’s capabilities, making it possible to write complex routines that can execute closer to the data, thus improving performance and maintainability.

As an Oracle DBA transitioning to PostgreSQL, understanding the differences between PL/SQL and PL/pgSQL is critical. This article explores the nuances between the two languages, covering syntax, features, and practical migration tips, ensuring you can leverage PL/pgSQL effectively in your PostgreSQL environment.

Read on for several differences between the two languages.

Comments closed

Power BI Automatic Aggregations and Databricks

Published 2024-10-29 by Kevin Feasel

Katie Cummiskey, et al, do a bit of caching:

Automatic aggregations streamline the process of improving BI query performance by maintaining an in-memory cache of aggregated data. This means that a substantial portion of report queries can be served directly from this in-memory cache instead of relying on the backend data sources. Power BI automatically builds these aggregations using AI based on your query patterns and then intelligently decides which queries can be served from the in-memory cache and which are routed to the data source through DirectQuery, resulting in faster visualizations and reduced load on the backend systems.

Click through to learn more about automatic aggregations, which SKUs of Power BI / Fabric are eligible, and how you can enable it for data coming from Databricks.

Comments closed

Incremental Integrity Checks for SQL Server Databases

Published 2024-10-29 by Kevin Feasel

Eitan Blumin eats the elephant:

Traditional database integrity checks in SQL Server can be time-consuming and resource-intensive, especially for large databases, even when using super cool tools like Ola Hallengren’s maintenance solution.

To address this challenge, I developed a TSQL script for performing incremental integrity checks, which significantly optimizes the process and reduces its impact on the server.

Click through for that script and notes from Eitan.

I’ll also shill for Minion CheckDB whenever I can. I was an early beta tester for the product and it was designed specifically for dealing with large databases.

Comments closed