Press "Enter" to skip to content

Curated SQL Posts

Encryption Options in Postgres

Published 2024-05-30 by Kevin Feasel

Umair Shahid goes through the list:

For any financial company that handles sensitive data as part of its operations, the protection of personally identifiable information (PII) is paramount. With the increasing frequency and sophistication of cyberattacks, it is crucial for these companies to implement robust security measures to safeguard PII.

This includes ensuring that even in the event of a breach, unauthorized individuals cannot read or misuse the data. One of the most effective ways to achieve this is through the encryption of data both in motion and at rest.

This blog will delve into the importance of encryption, the methods used to secure data in PostgreSQL databases, and the compliance regulations that mandate these practices.

Click through for the article. The set of capabilities are rather similar to what we have in SQL Server as well.

Comments closed

Fun with Implicit Conversion and Table Partitioning

Published 2024-05-30 by Kevin Feasel

Rod Edwards takes us through an issue:

CONVERT_IMPLICIT(nvarchar(200),[xxxxxxxxxxxxxxxxxxx].[Category,0)=[@Category] AND CONVERT_IMPLICIT(nvarchar(200),[xxxxxxxxxxxxxxxxxxx].[Id],0)=[@Id]

Oh dear, what’s all that then, we have a fat Residual Predicate, where SQL is performing an Implicit conversion on our query predicates.

Time to look at our datatypes.

This is one of several reasons why I espouse the philosophy of NVARCHAR Everywhere. You can’t have implicit conversion if you’re always using NVARCHAR over VARCHAR.

Comments closed

Using the CONVERT() Function in T-SQL

Published 2024-05-30 by Kevin Feasel

Joe Gavin shows how to use a function:

A common task while working with data in Microsoft SQL Server is converting from one data type to another. Most often, it’s done to change the way data is presented, but sometimes it is needed to make sure the right data types are being used for comparisons, joins, or sorting.

The SQL CONVERT function, which has long been part of the SQL language, and as the name implies, can be used to convert a value of one data type into a specified data type with optional formatting attributes. CONVERT gives you the ability to format, whereas the ISO Compliant CAST function does not.

My very strong recommendation for 99% or so of the audience: use TRY_CONVERT() instead. TRY_CONVERT() came out in SQL Server 2012 (sorry for the 1% stuck pre-2012) and has the same performance profile as CONVERT(), except that, when conversion fails, TRY_CONVERT() returns NULL rather than throwing an error.

There is also a TRY_CAST() that does exactly what you think it would.

Comments closed

Reviewing the Microsoft Fabric Roadmap

Published 2024-05-30 by Kevin Feasel

Paul Turley has a report:

I created this report to summarize the release status for all Fabric features that are documented in the Microsoft Fabric Roadmap. The information on this report is collected and updated frequently from the Fabric Roadmap hosted by Microsoft, and displays status information in a convenient, single-page Power BI report. Each feature area on the report has links to the detail documentation on the official roadmap site. For convenience, I’ve shortened the report path to TinyURL.com/FabricRoadmapReport.

Check out that link to see the report.

Comments closed

healthR.data Package Updates

Published 2024-05-29 by Kevin Feasel

Steven Sanderson has an update for us:

I’m excited to share the latest updates to the healthyR.data R package! This release brings new functionality and minor improvements, all aimed at making your data management tasks easier and more efficient. Here’s a breakdown of what’s new:

Read on for information on four new functions and a couple of bugfixes.

Comments closed

Diagnosing Slow Commits in Postgres

Published 2024-05-29 by Kevin Feasel

Laurenz Albe offers up some thoughts:

Sometimes one of our customers looks at the most time consuming statements in a database (either with pg_stat_statements or with pgBadger) and finds COMMIT in the high ranks. Normally, COMMIT is a very fast statement in PostgreSQL, so that is worth investigating. In this article, I will explore the possible reasons for a slow COMMIT and discuss what you can do about it.

Read on for those reasons.

Comments closed

Azure SQL Managed Instanced Update Policies

Published 2024-05-29 by Kevin Feasel

Rod Edwards is not amused:

Ah, SQL 2022, the release that finally brought box SQL and Azure managed instance closer together. We have wonderful toys such as Managed Instance Link, allowing us to connect our SQL 2022 on prem instances with Managed Instance Link. It waslike the first real effort to integrate modern Azure offerings with those who also need / prefer an On Prem presence.

Rob Litjens has a follow-up on this:

I prepared some questions:

  1. What polices does Managed Instance have?
  2. Why did Microsoft implement the ‘Always-up-to-date update policy’ Policy?
  3. Why is it named Policy?
  4. Do we need to update our Azure scripts to implement it (immediately)?
  5. Is there impact on offerings like Managed Instance Link

Do read both of these as they combine for a rounded perspective of the issue Rod brought up.

Comments closed

Recapping an Orchestration Framework

Published 2024-05-29 by Kevin Feasel

Martin Schoombee wraps up a series:

Frameworks are extremely useful when they are thoughtfully designed and implemented. I have seen both sides of the coin, but what I probably see the most of is a lack of any sort of framework. What I typically see are some naming conventions and coding standards, but many companies miss the opportunity to take it one step further and reduce the inefficiencies of repetitive tasks. There’s a ton of repetition in ETL processes, and in my opinion that gives us a really good opportunity to streamline the way in which we are doing things with a well designed framework.

Read on for Martin’s notes to keep in mind, as well as where to go from here.

Comments closed

Power BI Studio 2.0 Released

Published 2024-05-29 by Kevin Feasel

Gerhard Brueckl moves fast and doesn’t break things:

Due to the great feedback I have received for the first version of my VSCode extension to mange Power BI objects from within VSCode I decided to continue working on it and am finally happy to share that I am releasing a new version – v2.0!

If you already had the previous version installed in VSCode, you do not have to do anything as it will update automatically. If you are a new user, you can install it from the gallery or search for “Power BI Studio” in the VSCode extensions tab.

Click through for the list of updates, as well as how you can install the Visual Studio Code extension.

Comments closed