PostList

Approximate Count in MS SQL using APPROX_COUNT_DISTINCT

Approximate Count in MS SQL using APPROX_COUNT_DISTINCT

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

Read More
MS SQL Advice - COUNT(*) vs EXISTS

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

Read More
MS SQL Advice - Use constraints

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

Read More
MS SQL COMPRESS AND DECOMPRESS

MS SQL COMPRESS AND DECOMPRESS

MS SQL COMPRESS and DECOMPRESS are introduced into MS SQL version 2016. In this post we will read the overview of both functions, limitations and benefits. We will also test the performance compared to ROW and PAGE compression methods....

Read More
MS SQL STRING_SPLIT

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

Read More
MS SQL STRING_AGG

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

Read More