Category Archives: Databases

Everything related to databases. We love data and yes we love our databases too. Anything we do that relates to databases can be found here.

SQL 2019:- Truncation Error message and where it fails

I was wondering if I should even bother checking this feature out since it was pretty obvious what it does and how useful it is. I can’t even begin to explain how many times I have had to troubleshoot truncations errors when working with customer data. The basic thing about this feature is that now… Read More »

SQL 2019:- Table Variable deferred compilation – PART 2

In the last post we ended with two questions remaining unanswered. Shouldn’t a more accurate row count have resulted in the correct amount of memory being granted and more importantly why didn’t intelligent Query processing detect and correct the issue in subsequent runs? We were exploring the improved performance of Table Variable deferred complication to… Read More »

SQL 2019:- Table Variable deferred compilation

One of the features being released in SQL 2019 that has got some attention is table variable deferred compilation. As its name suggests it applies to Table Variables and not temp tables, CTEs, or table datatypes. More importantly why does it need to do deferred complication for Table variables? One of the drawbacks of Table… Read More »

Using Time Series DAX functions in PowerBI

Recently at a training I was asked how to implement DAX Time Series functions like parallel period and YTD in PowerBI. I always assumed that I had something written up about it but on closer inspection found that I have missed writing about such an important piece of functionality. So here is a step by… Read More »

Tuning SQL Server – Simulating Performance issues

  Check out the second video on our series on Tuning SQL Server. After explaining the data model in the first video, where we cover the process by which we caused the performance issues. In this second video of the series I show how we can call procedures using OStress.exe to simulate hundreds of users… Read More »

Working with Filetable to upload documents into SQL Server

A simple script on how to setup and work with file tables in MS SQL Server. This basically does the bare minimum to setup and create filetables in SQL Server and allows users to directly upload files into the directory and see them reflect inside of SQL Server.

SQL 2019:- sp_estimate_data_compression_savings

This procedure allows us to estimate the potential space savings on a table before we apply a compression algorithm. It’s not exactly a new feature of SQL 2019 because it was also available in SQL 2017 however it now provides information on Columnstore indexes as well. The reason why this is important is because we… Read More »

SQL 2019:- sys.dm_db_page_info

  So we have a new DMF to allow us to view details for the page something I do very often during trainings using the DBCC Page Utility. I figured this DMF would be pretty useful since I can be queried and joined with sys.dm_db_database_page_allocations to provide some interesting insight on the underlying pages mapped… Read More »

SQL 2019:- APPROX_COUNT_DISTINCT

  A function that might prove useful when querying very large datasets, it provides a count of distinct values for any column in the database except text,ntext , image data types. Why would this be useful? In many cases we see that an approximate count of the rows is sufficient and we don’t really need… Read More »