Posts

SQL Server 2025 NEWS: Enhanced T-SQL Syntax for Query Modularity

Image
Enhanced T-SQL Syntax for Query Modularity One of the most interesting directions for SQL Server 2025 is the evolution of T-SQL towards more modular, readable, and composable queries . The goal is not to replace stored procedures or functions, but to allow developers to structure complex queries into logical blocks , very much like local functions or expressions, while keeping everything inside a single SQL statement. The Problem: Complex Queries Become Hard to Read Consider a simple table: ARTICO ( CODICE INT, DESCR VARCHAR(100) ) Even with a small table like ARTICO , real-world queries often grow in complexity: filters, derived columns, joins, business rules, and conditional logic all accumulate. Traditionally, we rely on deeply nested subqueries or long WITH chains, which quickly become hard to read and maintain. Traditional Approach (Classic CTE) Using today’s T-SQL, we might write: WITH ArticoliFiltrati AS ( SELECT CODICE, DESCR F...
Post a Comment
Read more

Using Constraint Logic Programming to Formally Optimize 6502/6510 Assembly Code

Image
Introduction This post is an experiment . A first step, a proof of concept, and also a personal exploration. The idea is to start talking not only about SQL Server and Database , but also about Artificial Intelligence , constraint solving , and data-driven reasoning applied to real, concrete technical problems. In this case, the problem comes from an old but fascinating domain: the MOS 6502 / 6510 CPU . Project Overview and Motivation This code represents the first concrete step of a broader project whose goal is to build a code optimizer for the MOS 6502 / 6510 CPU using Constraint Logic Programming , specifically ECLiPSe CLP(FD) . The long-term vision of the project is to explore whether formal constraint-based reasoning can be used to automatically discover provably optimal instruction sequences for real hardware, instead of relying on hand-written heuristics, pattern matching, or empirical benchmarks. Rather than simulating execution or enumerating progra...
Post a Comment
Read more

SQL Server 2025 Regex Support: Native T-SQL Expressions

Image
SQL Server 2025 Regex Support: Native T-SQL Expressions SQL Server 2025 && Regex = Less Code, Less Pain in your queries Hi Guys, Here’s some good news: after years of workarounds, hacks, and tricks, SQL Server 2025 finally ships with native regex support in T-SQL. And no, this isn’t just a nice-to-have. It’s a game changer for anyone who deals with messy data, phone numbers, emails, codes… you name it. For years we’ve had to rely on ugly patterns like LIKE '%...%' , CHARINDEX , SPLIT , or even external CLR functions. They worked, but the cost in complexity, performance, and bugs was too high. Finally: native regex in SQL Server 2025 That’s right. Now you get built-in functions such as: REGEXP_LIKE(...) REGEXP_REPLACE(...) REGEXP_SUBSTR(...) REGEXP_COUNT , REGEXP_SPLIT_TO_TABLE , REGEXP_INSTR , REGEXP_MATCHES (table-valued functions are rolling out too) All powered by the RE2 engine : fast, familiar, and compatible enough with ...
1 comment
Read more

SQL Server 2025: Faster Backups with ZSTD Compression!

Image
SQL Server 2025: Faster Backups with ZSTD Compression Hi Guys! One of the most practical improvements in SQL Server 2025 —and a very welcome one for DBAs constantly fighting backup windows—is the new support for Zstandard (ZSTD) compression. This modern compression algorithm delivers faster backups without significantly increasing CPU or sacrificing storage savings. What’s New Compared to Previous Versions Up to SQL Server 2022, backup compression relied on MS_XPRESS , with optional acceleration via Intel QAT (or software fallback using QATzip). But now with SQL Server 2025, Microsoft introduces ZSTD : an open-source, high-speed compression algorithm with flexible levels and excellent performance-to-compression ratios. How to Use It No new UI, no new tools. It’s all in T-SQL: BACKUP DATABASE YourDB TO DISK = 'YourPath\backup.bak' WITH COMPRESSION (ALGORITHM = ZSTD, LEVEL = LOW|MEDIUM|HIGH); LOW is the default and fastest MEDIUM ...
Post a Comment
Read more

SQL Server 2025 is coming!

Image
SQL Server 2025 – Currently in Preview under the Early Adoption Program Hey Guys, It’s been a while since we last caught up here on the blog, but don’t worry—I’ll tell you what I’ve been up to soon! For now, let’s dive into something exciting: the latest features of SQL Server 2025 . As always, I’ll break down the most important updates in a clear and concise way. Enjoy the read! 🚀 Enhanced Performance with Intelligent Query Processing (IQP) SQL Server 2025 introduces major enhancements to Intelligent Query Processing (IQP) , further optimizing query execution. Key improvements include: Adaptive Join Enhancements: SQL Server can now dynamically switch between Nested Loops, Hash, or Merge joins during query execution, reducing latency. Parameterized Plan Optimization: Helps mitigate parameter sniffing by generating multiple execution plans for different parameter values. Memory Grant Feedback: Now supports both row-mode and batch-mode execution, dynamically ...
Post a Comment
Read more

Cloud + Tuning = Money Saving, a big deal!

Image
Hi Guys, Welcome back! Today only a small post for a small thought. Today it is as if we were back to many years ago. I remember the first years when (at least in Italy) the connection was not absolutely flat! Every minute of internet browsing had a cost and it was not cheap. After some time things changed a bit and for Browsing internet we have to pay for the amount of data downloaded. Now with the cloud it’s a bit the same logic. If you have a database on the cloud, the more data you read, the more you pay. So that’s our "favorite weapon" to avoit to pay many money on the cloud?  Well, the tuning! it is still really useful. So in this case, we don’t just use tuning to make our queries faster and to eliminate or reduce locks and deadlocks. We can and must also use tuning to reduce the number of data to read from the database . Which is particularly welcome if our database is on the cloud. I will so a the simply example just to give to you a concept. Example Suppose...
4 comments
Read more

SQL Server 2022: Accelerated Database Recovery enhancements from SQL 2019 to SQL 2022

Image
Hi Guys,   Welcome back! You Start to be really many to read this blog! Just two words: thank you very much! Today we will talk again about the Accelerated Database Recovery (ADR) feature, we talked about the same topic already 4 years ago here  SQL Server 2019 and the Accelerated Database Recovery . Yeah, it’s been four years. In 2019 we described in detail what ADR is, now it’s time to talk about what improvements this feature has in SQL 2022. I want to remind you that SQL 2022 is the latest version of the famous RDBMS produced by Microsoft.   Accelerated Data Recovery: a short summary! As we have seen some time ago, the ADR has been introduced in SQL 2019 (15.x) with the obective of improving the database availability , especially in the presence of long running transactions.  To achieve this, the database engine recovery process has been redesigned. We do not have to scan the transaction log from and to the beginning of the oldest active transaction instead o...
3 comments
Read more

SQL Server, multiple "GROUP BY" in a single statement? The GROUPING SETS explained in a CLEAR way and with EXAMPLES!

Image
Hi Guys!  Welcome back! Today we will talk about GROUPING SETS. As usual I will try to explain what we are talking about in the clearest possible way and we will always give examples . So what is a GROUPING SET? GROUPING SETS can be considered an extension of the GROUP BY clause in fact it gives you the possibility to combine multiple GROUP BY clauses . How many times do we have to show in video a series of data that maybe adding them up and regrouping them for more than one field? How many times have we had to use UNION clauses? Often many clauses union together make the code less readable and less maintainable. The solution is to use GROUPING SETS. Enjoy the reading!   GROUPING SETS  As said, a grouping set can be considered as an extension of the GROUP BY clause. Speaking in a technical way, SQL grouping sets are a way to group data in SQL queries by multiple dimensions. Now, in a typical SQL GROUP BY clause, data is grouped by one or mor...
1 comment
Read more

"SQL Query Design Patterns and Best Practices" My review!

Image
Hi Guys! It’s been a while since the last post ...it’s been a lot of work! In the meanwhile SQL Server 2022 get its second CU ( download ) and SSMS has reached version 19.02 ( download ) However we are here and so … welcome! I must say I like to review books because this is also a good way to stay updated. For this I say a big thanks both the publisher Packt and the kind Nivedita Singh for offering me, once again, the opportunity to talk about their new book. Packt is an English publishing house known worldwide and specialized in books related to information technology , including programming, web design, data analysis and hardware . This new book will be published on March 22nd ( but yes you can preorded on Amazon here: https://packt.link/AYWje ) and it is related to the topic writing of Queries in SQL Server . Its title is " SQL Query Design Patterns and Best Practices " and the authors are: Steve Hughes, Dr. Ram Babu Singh, Leslie Andrews, Dennis Neer, Shabbir H. ...
Post a Comment
Read more

How to get the n-th row in a SQL table ..in 4 different ways! Simple, clear and with example!

Image
Hi guys, After the deep previous post about the SQL statistic today we will talk about a more more easy topic. Have you ever needed to extract from a table the second row, or the third or the n-th row? This post is for you!    Extract ..Get..Select... the n-th row in a table We can solve this task in more than one way. To follow my example you can simply execute the following command that create and fill the Ord table with some data Create Table Ord (Id int identity (1,1) primary key , Code varchar (10)) Insert into Ord (Code) values ( 'A' ),( 'B' ),( 'C' ),( 'D' ),( 'E' ),( 'F' ),( 'G' ),( 'H' ),( 'I' ) This is our table:   The first way I seen many times is using a derivate table .   Yes, you can solte the request by split the problem in two parts. In the first part you can get the first n-th rows. This will be the derivate table.   Then you can get first row of this derivate table orde...
3 comments
Read more