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).
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.
Let’s find out how MS SQL 2019 handles the problem that generates the commonly known error “String or binary data would be truncated”.
Database normalization is a process used to organize a database into tables and columns. The idea is that a table should be about a specific topic and that and only supporting topics included. Normalization is a systematic approach of decomposing tables to eliminate data redundancy.