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.
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.
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).
With basic Linux knowledge let’s try to step by step install MS SQL Server on a Linux server (Ubuntu).