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.
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.
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.
Now when we know more about hierarchies in MS SQL Server and we overviewed existing methods for the HierarchyID data type it’s time to discuss about the benefits of using HierarchyID data type.
In my previous article that you can find here I introduced you to the Hierarchical data in MS SQL. HierarchyID data type is a cool feature if you need to store and handle hierarchical data in your database (e.g. organizational chart). Here we will see methods exposed against the HierarchyID data type.
The usual solution how to handle hierarchy in a database was to use two columns. The first column contains the ID of the hierarchical member (in much cases also the primary, unique ID), and the other, the ID of its parent hierarchical member. From SQL Server 2008 we can use the HierarchyID data type that is a data type written in .NET and exposed in SQL Server. HierarchyID offers us some performance benefits and simplified code. There are also available many functions that can be used for example to determine the ancestors and descendants of a hierarchical member, as well as determine other useful information, such as the level of a specific hierarchical member within the hierarchy.
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.
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.
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.
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.