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.

Tales from the crypt: – Passing multiple int values into a variable

By | October 15, 2019

Reference Post https://social.msdn.microsoft.com/Forums/en-US/7c04a296-3c26-4a43-ae63-bc90139f4ab3/error-converting-data-type-nvarchar-to-int?forum=sqlgetstarted I was browsing through the forums and found this question being asked. While the reason for the error is fairly obvious I felt it needed further investigation so here it is. The question being asked is why does the below query work While the next one fails Naturally anyone with any TSQL… Read More »

Generating TPC H data for testing

By | October 14, 2019

A common requirement I have is to generate test data. Sometimes for trainings and sometimes for consulting work. As you may have seen from many previous video and blogs typically use Airlines Ontime dataset for large databases and Mockaroo for smaller datasets. However recently I wanted to try and see if I could achieve comparable… Read More »

Writing MDX the T-SQL way

By | September 15, 2019

  With all the focus on data science I felt it would be a good idea to revisit a little used Query Language called MDX in SSAS ( analysis Services). Why? Because despite the usage of Hadoop and Polybase and all the enhancements in Columnstore indexes there is still a good use case for Analysis… Read More »

Tuple Mover Basics and the Updateable Columnstore index

By | July 9, 2019

When Microsoft introduced Columnstore indexes in SQL server 2012 a key limitation was the table could only be read from and not inserted. While the limitation itself has been overcome in subsequent versions of SQL server we need to understand the impact of how they work to explain performance issues when it comes to accessing… Read More »

Adaptive Joins SQL 2017

By | June 20, 2019

  select a.StateName , a.CityName , sum(flights) from Airports a inner join normalization_index n on a.AirportSeqId = n.OriginAirportSeqID where a.AirportSeqId > 1474703 group by a.StateName , a.CityName     select a.StateName , a.CityName , sum(flights) from Airports a inner join normalization_index n on a.AirportSeqId = n.OriginAirportSeqID where a.AirportSeqId=1451202 group by a.StateName , a.CityName    … Read More »

94% Compression???? SQL Server from 1GB to 65 MB

By | June 11, 2019

In this video we go about taking data that is little over 1 GB in size and incrementally reducing its size all the way down to 65MB. This is mostly to highlight that good design can significantly reduce the memory footprint of the data. With tools like PowerbI etc. the push is towards storing the… Read More »

A Case for Classroom trainings

By | April 22, 2019

Every once in a while I see an advertisement on LinkedIn about companies that conduct online Trainings. Each time I see such ads I am reminded of how people have adopted technology to make their lives better as well as worse. In this post I am going to dissect what is wrong about online trainings.… Read More »