A Fine Slice Of SQL Server
Tatsuo Ishii makes me jealous:
Row pattern recognition (RPR) is a feature defined in the SQL standard. It allows to search for a sequence of rows by pattern. Since I am working on this feature for PostgreSQL, I would like to give a brief introduction to RPR.
This is something I’ve wanted to see in SQL Server for the past few years. It’s not something people might use every day, but it solves a particular class of problem extremely well.
Comments closedKunal Rathi troubleshoots an issue:
While deploying a DACPAC project to Azure SQL database using Azure DevOps, you may come across ‘A project which specifies SQL Server 2022 as the target platform cannot be published to Azure SQL Database v12.’ error. In this article, we will see how to fix this issue.
There are plenty of errors in Microsoft products that make little to no sense. This is, by contrast, a rather straightforward one, and Kunal shows how to resolve it.
Comments closedGreg Low does a bit of parsing:
There was a question on the Q&A forums today, asking how to read data using SSIS, when it’s in this format:
|Col1| |Col2|Col3|Col|
|101| |A|21|DC|One of the concerns was that there was a leading pipe. This is not a problem. When you have data like that, and you set | as the delimiter, because there are 6 delimiters, then there are 7 columns output.
Read on for Greg’s answer. This is a big part of why I recommend people to use Parquet (or ORC, if you’re an iconoclast like I am): not needing to deal with whatever silly, arbitrary file formats people come up with.
Comments closedAndy Brownsword hits upon an underrated feature in SQL Server:
Deploying database changes are relatively easy. Where things get complicated is the rollback. Sure, it’s easy to script out and revert a procedure to a previous version, but what about destructive changes?
In this post we’ll look at how we can use Database Snapshots to remove some of the headache that comes with rolling back changes.
Read on to learn more. The most exciting thing I did with database snapshots was to create a simple app for my developers to take a snapshot (without needing to know the T-SQL behind it), make whatever changes they needed to, and then roll back or roll forward the changes.
Comments closedI continue a video series on Streamlit:
In this video, I extend a Streamlit app that I created in the prior video. This extension will include retrieving data from SQL Server, caching function results, operating a set of simple filters, and displaying visuals using tabs.
Click through for the video, as well as the code that I used.
Comments closedDavid Fowler troubleshoots a wait type:
Are you struggling with a laggy redo and a build up in the redo queue on your readonly secondaries? Are you suffering with high PARALLEL_REDO_TRAN_TURN waits? Then this magic remedy could cure your ailments.
Read on to learn more about the parallel redo process and one thing you can do if it’s falling too far behind. I wouldn’t permanently switch back to serial redo, but maybe make that switch if there’s some consistent pattern to when the process falls behind.
Comments closedIn the previous article, I explained SCD (Slowly Changing Dimension) and its different types. In this article, I’ll show you how to implement SCD Type 2 (one of the most common types) using Microsoft Fabric and Power BI. This article includes using Lakehouse, Dataflow, Warehouse, Data Pipeline, SQL Stored Procedures, Power BI Semantic model, and report in Microsoft Fabric.
Click through to learn more about the structure of a type-2 slowly changing dimension, as well as how you can store and load this information to track changes over time.
Comments closedGilbert Quevauvilliers fixes an issue:
How to stop/fix Mirroring to a database in Microsoft Fabric
I recently had a challenge where the mirroring in Fabric stopped working because there was no activity.
For me to start this again when I tried to connect to a table it said the database was already being used to mirror as shown below.
Read on for a quick fix that Gilbert found.
Comments closedThere are several predefined variables that you can use within an Azure DevOps pipeline, some of which allow you to reference locations on the pipeline agent file system. Examples of when to use them might be to pick up a file from the downloaded repository or to describe where to output a build. For example the variable
$(Build.ArtifactStagingDirectory)will be replaced during the defining of the pipeline job with the respective filepath.I find it difficult at times to work abstractly with the file system, and so I use this template to visualise the file system for debugging purposes, to observe the outcome of my code, and to make sure I’m picking the right variable.
Click through for some more information about pre-defined variables and a code snippet Justin uses to view filesystem details.
Comments closedPieter Vanhove reminds me that ledger tables exist:
Ledger technology is a way of storing data that ensures its integrity, immutability, and verifiability. It can be used for scenarios where trust and transparency are essential, such as financial transactions, supply chain tracking, or regulatory compliance. Azure offers two services that leverage ledger technology to provide tamper-proof data storage: ledger in Azure SQL Database and Azure Confidential Ledger. In this blog post, we will compare these two services and help you decide which one is best suited for your needs.
Read on for the comparison. I think the answer for most cases is “neither” but there are specific times when ledger tables could make a good amount of sense.
Comments closed