Damir Matešić .blog

Main articles

  1. Accelerated Database Recovery (ADR) - SQL Server 2019 introduces a new feature called Accelerated Database Recovery (ADR). ADR changes the way SQL Server maintains the durability of transactions. IT dramatically reduces the time required for crash recovery, Availability Group failover, and transaction rollback.
  2. Approximate Count in MS SQL using APPROX_COUNT_DISTINCT - There is a case when we want to get the unique number of non-NULL values in one table column. Before MS SQL 2019 to accomplish this task, we could use the COUNT(DISTINCT([Column_Name])) syntax. For larger tables this approach can generate noticeable performance problems. MS SQL 2019 introduced us the APPROX_COUNT_DISTINCT function that approximates the count within a 2% precision to the actual value but at a fraction of the time and with noticeable decrease of used resources (memory and CPU).
  3. AT TIME ZONE - MS SQL AT TIME ZONE was introduced in MS SQL version 2016 and the main purpose is to convert an inputdate to the corresponding datetimeoffset value in the target time zone.
  4. COMPRESS AND DECOMPRESS - MS SQL COMPRESS and DECOMPRESS are introduced into MS SQL version 2016. In this post we will read the overview of both functions, limitations and benefits. We will also test the performance compared to ROW and PAGE compression methods.
  5. CONCAT vs CONCAT_WS - SQL Server function CONCAT is introduced in MS SQL version 2012. Simple as that it returns a string that is the result of concatenating two or more string values. Let’s compare it with the new function CONCAT_WS which is introduced in MS SQL Server 2017.
  6. CREATE OR ALTER - CREATE OR ALTER is a new MS SQL Server syntax extension introduced from MS SQL Server 2016. Read more about it in this post.
  7. DATEDIFF_BIG - In short, introduced in MS SQL 2016, DATEDIFF_BIG is almost the same function as DATEDIFF starting from MS SQL 2008 that is commonly used already. So, let’s see how and for what is used and what are the benefits.
  8. DROP IF EXISTS (a.k.a. DIE) - DROP IF EXISTS is a new MS SQL Server syntax extension introduced from MS SQL Server 2016. Read more about it in this post.
  9. Encrypt database backup step by step guide - Sometimes you may need to protect your information from unauthorized access. Of course, it is extremely important that you regularly backup your databases. But how to protect backup files? In this article, we will explain step by step how to back up your database and how to restore it.
  10. FORMATMESSAGE - Read more about MS SQL FORMATMESSAGE function introduced in MS SQL 2016. Find samples and explanations. Let's find how to use it and what has in common with the C# String.Format() method.
  11. GENERATE_SERIES - In this post we will talk about MS SQL GENERATE_SERIES, table-valued function introduced in MS SQL version 2022 for generating a series of numbers within a given interval. The interval and the step between series values are defined by the user. We will see also how we did it before and test its performance compared to the old way.
  12. GREATEST() - This function returns the maximum value from a list of one or more expressions. Currently (in the time when writing this blog post) the function is supported only on Azure SQL (Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics - serverless SQL pool only) but we hope that in the future it will be available also in on-premise versions of SQL.
  13. HASHBYTES - Hashing in MS SQL Server - In short words, hashing is a process of generating a value or values from a string of text using a mathematical function. Let's see the usage of the MS SQL function HASHBYTES witch purpose is to hash values. MS SQL function HASHBYTES was introduced in MS SQL version 2005 supporting MD2, MD4, MD5, SHA, SHA1 hashing algorithms. From MS SQL server version 2012 additionally the SHA2_256, SHA2_512 algorithms were introduced. In this article we will discuss about hashing, what's new from SQL 2016 and see some usage examples.
  14. Historical data with MS SQL System-Versioned (Temporal) Tables - Before the existence of system-versioned temporal tables, tracking changes in a database table forced us to implement some custom solutions based on triggers, stored procedures etc. System-versioned temporal tables offer us the possibility to keep a full history of data changes and allowing us easy point in time analysis. Using this solution, we can easily time travel through the data and see what the exact state of the data in a point of time was.
  15. Install MS SQL on Linux (Ubuntu) - With basic Linux knowledge let’s try to step by step install MS SQL Server on a Linux server (Ubuntu).
  16. IS [NOT] DISTINCT FROM (The Distinct Predicate) - As you probably know comparing with NULL value is not possible. NULL is not a value. IS [NOT] DISTINCT compares the equality of two expressions and guarantees a true or false result, even if one or both operands are NULL.
  17. Last Actual Plan with sys.dm_exec_query_plan_stats - When you think about execution plans in MS SQL Server you could think that the Estimated query plan are useless. So, here I must disappoint you. All execution plans are estimated. The only difference between the “Actual” and the estimated execution plan is that the so called “Actual” have some added runtime metrics. Prior to SQL 2019 to be able to get the metrics you must execute the query to get the actual execution plan. In SQL Server 2019 thanks to the newly introduced function “sys.dm_exec_query_plan_stats” you can get the actual execution plan, the last one run on the system.
  18. LEAST() - MS SQL LEAST() returns the minimum value from a list of one or more expressions. Currently (in the time when writing this blog post) the function is supported only on Azure SQL (Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics - serverless SQL pool only) but we hope that in the future it will be available also in on-premise versions of SQL.
  19. Memory-Optimized TempDB Metadata - Temporary database (TempDB) is one of the biggest sources of latency in SQL Server. Requests for temporary data structures and maintaining its metadata is one of the most significant bottlenecks in SQL Server. Memory-Optimized TempDB solve this issue by writing TempDB metadata into memory. As a result, it greatly improves the performance of any workload that heavily use temporary data structures.
  20. Scalar UDF are (not) evil? - SQL 2019 - Sometimes using user defined functions (before SQL 2019) could lead us to performance problems. Let’s see how the usage of UDF performs in SQL 2019 and in earlier versions.
  21. SQL Advice - COUNT(*) vs EXISTS - If you want to check for data existence in a table (e.g. if there are invoices on a concrete date) you could use COUNT(*) or the EXISTS statement. I found various theories on the internet and even in some SQL books what is the best approach, so I decided to test this by myself (spoiler alert: the books are on the side of using EXISTS).
  22. SQL Advice - Use constraints - Why you should use constraints on your tables? Except the fact that you should use constraints to check your data and the integrity of them (e.g. only allow inserting of numeric value between 1 and 5 for storing vote value) you could use them also for better query executions and get some performance boost. Let’s find how.
  23. String or binary data would be truncated - Let’s find out how MS SQL 2019 handles the problem that generates the commonly known error “String or binary data would be truncated”.
  24. STRING_AGG - In this post we will learn something about MS SQL STRING_AGG function introduced in MS SQL version 2017 for string aggregation, how we did it before and test its performance compared to the old way.
  25. STRING_ESCAPE - MS SQL STRING_ESCAPE introduced in MS SQL 2016 escapes special characters in texts and returns text with escaped characters.
  26. STRING_SPLIT - Let's talk about MS SQL STRING_SPLIT table-valued function introduced in MS SQL version 2016 for splitting string values by a separator. How we did it before and test its performance compared to the old way. We will also see the pros but also cons of the function.
  27. TRANSLATE - Read more about MS SQL TRANSLATE function introduced in MS SQL 2017. Find samples and explanations.
  28. TRIM, LTRIM, RTRIM - TRIM is a new MS SQL Server function introduced from MS SQL Server 2017. It removes characters from both sides (at the beginning/left and at the end/right) of the given string value. TRIM, LTRIM and LTRIM are enhanced in MS SQL Server 2022.
  29. UTF-8 support in MS SQL 2019 - Before MS SQL 2019 storing some characters (e.g. ASCII) in MS SQL was limited. SQL Server supports Unicode characters in the form of nchar, nvarchar, and ntext data types that are using UTF-16 encoding. The penalty of this was that you need to pay the price for more storage and memory because you had to store all the data in Unicode (UTF-16), even when you needed only ASCII characters. UTF-8 database support allow application(s) internationalization without converting all strings to Unicode.