This is the second article of a series dedicated to implementing Recursive Common Table Expression in different kind of scenarios, and this time I will address another use case; it basically consists in filling gaps inside a sequence of an incomplete range of dates.  I would like to comment that the next example has been taken from MSDN forum, I have chosen because I think it is an excellent way to prove how versatile the recursive CTE is, the most creative solution was proposed by Peter Larsson (swePeso) and he brought us  a valid and optimal implementation which is useful since SQL Server 2005 and up versions.

I remember the first time that I discovered the amazing feature called Common Table Expression (CTE), it was during my first read of Itzik Ben-Gan book (T-SQL Querying), maybe one of the most interesting feature together with Windows Functions.  I know, I am a declared fan of CTE, but we can start defining a CTE as a sort of temporary result set and which exists only during the lifetime of the query.

Continue reading

I have to confess; I have had traumatic experiences with variant data type in my beginning as a software developer, in fact, I always have believed that it is an anti-pattern, at least in the OOP world, on the other hand in the Database world it gains much relevance, Why? Because of every column inside a table is supposed to be a specific datatype, otherwise, it could create a great number of conflicts and uncomfortable situations.

There are some special scenarios where we require to generate a customized workload in our SQL Server instance, these could be specific T-SQL statements as simple queries or stored procedures.  In some cases, we need to simulate a workload for troubleshooting common errors related to deadlocks or race conditions, even for testing purposes in which we want to analyze the behavior of SQL Server in
presence of some conditions or data distributions.