Damir Matešić .blog

Main articles

  1. 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.
  2. 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.
  3. FIRST_VALUE, LAST_VALUE - Another cool function (actually two of them) introduced in MS SQL 2022 are FIRST_VALUE and LAST_VALUE.
  4. 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.
  5. 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.
  6. GREATEST() - MS SQL GREATEST() 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.
  7. 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.
  8. 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.
  9. 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).
  10. 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.
  11. 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.
  12. 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.
  13. 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.
  14. 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. This feature is also known as T-SQL Scalar UDF Inlining.
  15. 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).
  16. 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.
  17. 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”.
  18. 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.
  19. STRING_ESCAPE - MS SQL STRING_ESCAPE introduced in MS SQL 2016 escapes special characters in texts and returns text with escaped characters.
  20. 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.
  21. The WINDOW Clause - The WINDOW clause allows you to shorten your code by avoiding the repetition of identical parts of your window specifications
  22. TRANSLATE - Read more about MS SQL TRANSLATE function introduced in MS SQL 2017. Find samples and explanations.
  23. 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.
  24. 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.