SQL Server by Harsh Kumar

Zstandard Backup Compression in SQL Server 2025  In SQL Server 2025, Microsoft has introduced a highly anticipated enhancement - support for the Zstandard (ZSTD) compression algorithm in native database backups. This marks a major leap forward in backup efficiency, delivering faster processing speeds and smaller file sizes without significantly increasing CPU usage. Whether you're managing on-premises SQL Servers or hybrid cloud environments, understanding and implementing Zstandard compression can help you optimize both performance and storage costs. What is Zstandard? Zstandard (ZSTD) is a modern, open-source lossless data compression algorithm developed by Facebook. It’s designed to offer: High compression ratios (comparable to zlib/gzip) Lightning-fast compression and decompression Low memory and CPU usage Unlike legacy algorithms like MS_XPRESS , Zstandard excels at balancing speed and size—making it ideal for high-throughput, enterprise-grade workloads. Why SQL...

Optimized Halloween Protection in SQL Server 2025 The Halloween Problem  is a classic and subtle issue in relational database systems that arises during certain data modification operations—specifically UPDATE, INSERT, DELETE, or MERGE statements. The core of the problem is that, under specific circumstances, a row can be modified more than once during a single statement execution, even though the intention is for each row to be updated only once. How Does the Halloween Problem Occur? The issue typically surfaces when: * An update operation modifies a column that is also used in the index being scanned to identify the rows to update. * As the update progresses, changing the indexed column can cause the physical location of the row in the index to change. * This movement can cause the row to reappear ahead of the scan cursor, meaning the same row is encountered and updated again—potentially leading to multiple updates or even infinite loops. Halloween Protection: The Traditional So...

Accelerated Database Recovery (ADR) is one of the most transformative features in recent SQL Server releases, and with SQL Server 2025, it’s more powerful and flexible than ever. ADR is a SQL Server feature designed to drastically improve database availability by reengineering the recovery process. Traditional recovery could take a long time, especially with long-running transactions, because SQL Server had to scan the transaction log from the oldest uncommitted transaction. ADR changes this by introducing a new architecture that makes recovery and rollback nearly instantaneous, regardless of transaction length or database activity. Key Benefits : Fast and consistent recovery : Recovery time is no longer affected by the number or size of active transactions. Instantaneous rollback : Even very long-running transactions can be rolled back instantly. Aggressive log truncation : The transaction log is truncated more aggressively, preventing log growth even with active long transactions. H...

Standard Developer and Enterprise Developer edition in SQL Server 2025 In SQL Server 2025, two new Developer editions are introduced: Standard Developer and Enterprise Developer. These editions are designed for development and testing purposes and are licensed for free. The Standard Developer edition includes all the features of the Standard edition, while the Enterprise Developer edition offers the complete functionality of the Enterprise edition.  Standard Developer Edition Free licensing: It can be used without incurring any licensing costs.  Matches Standard edition: It mirrors the features and capabilities of the SQL Server Standard edition.  Ideal for teams: It's particularly valuable for teams whose production workloads run on Standard edition.  Narrowing the gap: It helps to minimize the difference between development and production environments, especially for applications that rely on Standard or Web/Express editions.  Improves code predictability: It ...