Press "Enter" to skip to content

Day: March 17, 2025

SQL Server Views and Implicit Data Types

Published 2025-03-17 by Kevin Feasel

Kendra Little takes a peek at a view:

Views let you do dumb things by accident in SQL Server. Then they make you have to think way too hard to fix them.

Most of the time when people create views, they start by refining a SELECT query, then turn it into a view. People also often create multiple views that pull different slices of data and UNION the results together.

Combined, these two things easily lead to undeclared datatypes in views with problematic implicit conversions.

Read on for an example of this problem in action. Kendra’s example involved a view and a separate table, but you can also see this kind of thing pop up in a view that itself contains set operators like UNION.

Leave a Comment

SQL Server Performance Office Hours

Published 2025-03-17 by Kevin Feasel

Erik Darling answers a set of user questions:

You have said that table variables, CTEs, Change Tracking, and Azure Managed Instances all suck. Do you have a full list of “features” to avoid?

Click through for a video of Erik answering questions around deadlocks, terrible things, UTF-8, and more. And I like the nuance behind Erik’s answer of this particular question. It’s easy to say “this thing is awful” and be done with it, but often times, the answer is more of “In this particular circumstance, don’t use this thing because of reasons X, Y, and Z; instead, use this thing.” That’s a rather different answer.

Leave a Comment

Saving an Azure Database for PostgreSQL Backup to a Storage Account

Published 2025-03-17 by Kevin Feasel

Josephine Bush wants an extra copy of the backup:

This may or may not be helpful in the long term, but since I’m doing it to be super cautious, I figured I would blog about it. We migrated to Flex last week, and to be abundantly cautious, we’re putting the last single server backup into cold storage. You could also use this same process to offload Flex if you were going to delete a server and want to save a final backup or have some use case for saving backups to storage longer term.

Read on for the process. It’s not as simple as running a command or two, but Josephine does take us through the process.

Leave a Comment

Asynchronous Commits in PostgreSQL

Published 2025-03-17 by Kevin Feasel

Shayon Mukherjee discusses trade-offs:

I was recently looking into some workloads that generate a lot of I/O and CPU contention on some very high-write code paths and came across synchronous_commit (https://www.postgresql.org/docs/current/wal-async-commit.html). It can be very tempting to turn this off globally because the performance gains in terms of I/O, CPU, and TPS (transactions per second) are very hard to overlook. I noticed I/O completely gone, CPU down 20% (at peak), and a 30% increase in TPS. However, this comes with important trade-offs that are worthwhile keeping in mind.

Click through for more information. This sounds a lot like the delayed durability feature in SQL Server, though you have more fine-grained control in PostgreSQL versus it being a database-level setting in SQL Server.

Leave a Comment

Digital Signatures on SQL Server Stored Procedures

Published 2025-03-17 by Kevin Feasel

Rick Dobson signs a procedure:

This tip introduces the basics of digital signatures based on self-signed certificates for selected T-SQL objects (stored procedures, functions, and triggers) with T-SQL samples. Learn how to detect what happens to a digital signature when a stored procedure is accidentally altered by a database administrator or intentionally modified by a bad actor.

Click through for the process.

Leave a Comment