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.
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.
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.
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.