Press "Enter" to skip to content

Month: November 2024

Making a SQL Server Table Read-Only

Published 2024-11-21 by Kevin Feasel

Chad Callihan has no need to write:

There are situations when you need to set a database to read-only, but what do you do when you need a single table to be read-only? It’s not as straightforward as setting the entire database read-only, but it can still be done.

Let’s take a look at the steps necessary to set both a database and a single table to read-only.

Another option is to put the table into a filegroup that you make read-only, especially if this is a permanent change. That way, you can rebuild the table’s indexes with a fill factor of 100% and reduce the number of pages. In addition, you can back up that read-only filegroup separately from active filegroups and restore filegroups individually. Now, if your read-only filegroup is a large percentage of total data in the database, you don’t need to back it up nearly as frequently because the data isn’t changing.

Leave a Comment

E-Mailing Query Results in Snowflake

Published 2024-11-20 by Kevin Feasel

Kevin Wilkie gussies up an e-mail:

In our last post, we discussed the most basic of all ways how in Snowflake you can send out a basic email. It was pretty simple, straight text – nothing to really grab the attention of our readers – which we know is the way to craft an email, right?

To do this, we’re going to have some fun in Python. Yes, delve deep into your bag of Python tricks as we get up to some shenanigans with Snowflake and Python.

Read on for a procedure to e-mail the prior result set in HTML format.

Leave a Comment

Creating a Parquet File in Python

Published 2024-11-20 by Kevin Feasel

Ed Pollack has part one of a two-parter:

This article dives into the Apache Parquet file format, how it works, and how it can be used to export and import data directly to SQL Server, even when a data platform that supports Parquet files natively is unavailable to assist.

In the second part of this article, customizations and more advanced options will be highlighted, showing the flexibility of Python as a tool to solve analytic data movement challenges.

I like how Ed covers the Parquet file format, as it’s not all that complicated but it does give you an idea of how so many operations on Parquet data can be so fast.

Leave a Comment

SQL Database in Microsoft Fabric

Published 2024-11-20 by Kevin Feasel

Nikola Ilic covers a new addition to the Microsoft Fabric family:

Now, let’s get back to the previous point: SQL database in Fabric is a SaaS Azure SQL DB…Generally speaking, in SaaS solutions, “everything just works” (or at least should work) – without (too much) intervention from your side.

In the context of the SQL database in Fabric, creating a database is probably the most straightforward process of database creation you will ever experience, as I’ll show you in the “HOW TO” section of this article. From that point, everything happens automatically: the database will be automatically configured and will automatically scale both in terms of compute resources and storage. In addition, database backups are performed automatically, indexing also happens the same way, as well as all patches and software/hardware fixes. You want more? No more complex firewall rules and permission settings – this time, everything is done via Fabric workspace roles and item permissions, while the well-known SQL native features allow for more granular control.

This is more of a head-scratcher for me than a brilliant solution. I get that there’s a challenge in figuring out what you want with Azure SQL Database: single database or elastic pool, serverless or provisioned, vCore or DTU-based pricing model, General Purpose or Hyperscale or Business Critical (for vCore), Basic or Standard or Premium (for DTU), one of about five separate hardware configurations, etc.

From the standpoint of “I just want a database, please,” Fabric SQL Database is a lot easier. The problem comes in when you hit the use cases that necessitated all of these options to begin with, at which point you’re back to the original creation screen and outside of Fabric once more.

Leave a Comment

What Happens upon Row Modification in Postgres

Published 2024-11-20 by Kevin Feasel

Semab Tariq takes us on a journey:

But behind the scenes, it’s more complex to ensure that data remains consistent and accessible. In today’s blog, I’ll answer some frequently asked questions from our customers and dive into why PostgreSQL relies on a process called VACUUM to efficiently manage data updates.

Updating a row in PostgreSQL isn’t as straightforward as directly changing the existing data. Instead, PostgreSQL avoids in-place updates, meaning it doesn’t overwrite rows directly. 

But what does this actually mean? 

Read on to learn what that actually means.

Leave a Comment

Metadata-Only NOT NULL Column Insertion

Published 2024-11-20 by Kevin Feasel

Andy Brownsword has the need for speed:

When adding a new column and wanting to default the value for existing records, it used to be a painful task. As of SQL Server 2012 that became much easier.

But nobody told me, until Simon casually mentioned it in conversation recently. I had to see it for myself, so I thought I’d share for those who weren’t aware.

Read on to see how. I rarely self-promote in other people’s blog posts (hush, person who knows all the times I’ve done it), but I do have a talk on the topic of near-zero downtime database deployment strategies which includes this and quite a few other notes on what you can do without blocking others. For these sorts of changes, what you’re looking for is asynchronous processing and a Sch-M (schema modification) lock at the very end, such as when rebuilding an index with ONLINE = ON in Enterprise Edition. Alternatively, look for a Sch-M lock only on a metadata table and not the actual data. Andy’s post is an example of the latter.

Leave a Comment

What’s New in SQL Server 2025

Published 2024-11-20 by Kevin Feasel

Brent Ozar has a list:

Today at Microsoft Ignite, Microsoft announced SQL Server 2025. The biggest new features focus on AI and bringing the latest Azure SQL DB features down to your own servers. Here are the top features:

Of this list, I’m excited for regular expression support and optimized locking. On the latter, I would like RCSI to be the default, but I can understand that this would have a significant “Who moved my cheese?” factor for DBAs, and there are just enough cases when RCSI introduces actual change into an environment.

Fabric mirroring is also fine, and sp_invoke_external_rest_endpoint is something that can be useful, not just for making external calls. As for storing vector data, I will want to see its performance—both in terms of how quickly it loads & retrieves data, as well as how accurate the results are—before I get too excited. DiskANN is a good algorithm and it’s in Cosmos DB right now, but I haven’t been that impressed with the quality of results so far from it in Cosmos.

2 Comments

Debugging in Databricks

Published 2024-11-19 by Kevin Feasel

Chen Hirsh enables a debugger:

Do you know that feeling, when you write beautiful code and everything just works perfectly on the first try?

I don’t.

Every time I write code It doesn’t work in the beginning, and I have to debug it, make changes, test it…

Databricks introduced a debugger you can use on a code cell, and I’ve wanted to try it for quite some time now. Well, I guess the time is now 

I’m having trouble in finding the utility for a debugger here. Notebooks are already set up for debugging: you can easily add or remove cells and the underlying session maintains state between cells.

Leave a Comment

Querying a Fabric KQL Database via REST API

Published 2024-11-19 by Kevin Feasel

Sandeep Pawar grabs some data:

I have previously explained how to query a KQL database in a notebook using the Kusto Spark connector, Kusto Python SDK, and KQLMagic. Now, let’s explore another method using the REST API. Although this is covered in the ADX documentation, it isn’t in Fabric (with example), so I wanted to write a quick blog to show how you can query a table from an Eventhouse using a REST API.

Click through to see how you can do it. Sandeep’s code is in Python but because this is just hitting a REST API rather than using a library, you could also use some tool like Postman.

Leave a Comment

Aggregate Functions in Power BI

Published 2024-11-19 by Kevin Feasel

Hristo Hristov writes some DAX:

At times when using Power BI, you want to combine your data to produce an aggregated value. The aggregation is performed over some criteria – frequently this may be time (year, month, date) or a categorical value. Some popular aggregation functions to apply can be Sum, Average, Maximum, Minimum, or Count. Typically, Power BI applies certain aggregations by default when adding data fields to visualizations. What if you wanted to create your own data aggregations? To achieve better understanding of the underlying data, how can you attain fine-grained control over the aggregations?

Read on for several DAX measures, including totals, running totals, moving averages, and day over day changes.

Leave a Comment