T-SQL

Calculating average value of multiple columns

13. 10. 2020

It’s an easy task to calculate the average AVG() value for all rows in one or more columns. But it starts to be a little bit more interesting when we should calculate the average value for more columns on the same row. There are basically two options on how to…

Read more
T-SQL

Having fun with square brackets

11. 9. 2020
This article is just for fun. From time to time T-SQL dialect may look like little bit scary. In the example below, we will create such a monster using square brackets. We will create a sample table and remove one square bracket in the column name. Let’s see what will…
BI

Reporting Services samples for AdventureWorks2017

26. 8. 2020
AdventureWorks is the sample database for SQL Server. It is updated with every new release of SQL Server major version and you can download it from its official location. Samples for SQL Server Reporting Services were built against this database a long time ago and the most recent version can…
CI/CD

Installing RabbitMQ on Windows

6. 8. 2020
RabbitMQ is a trending message queue solution used by many software products today. From the release manager or CI/CD master point of view, it’s just another component that needs to be installed when delivering the product to the customer. Ideally, this should be automated but it depends on the customer…
Azure

Switch IP addresses between two Azure VMs

9. 7. 2020
Changing an IP address of Azure virtual machines isn’t a complicated task. Simply locate the VM in Azure Portal and change the IP of it. A little bit more complicated is the scenario when we would like to switch IP addresses between two existing virtual machines. This scenario may be…
T-SQL

Using MERGE to override OUTPUT clause limitations

7. 7. 2020

The OUTPUT clause is a very powerful extension of T-SQL included in SQL Server 2005. But from time to time it needs some tweaks or workarounds to get the expected result. One such example is using OUTPUT with INSERT statements. There is a limitation that only columns from the table…

Read more
DBA

Background process can block restore of database

5. 7. 2020
We can use database snapshots for the development of new databases or fine tunning complex upgrade scripts. All active connections must be closed before the database can be restored from a database snapshot. Closing of active connections manually via Activity Monitor isn’t an option that can be easily automated so…
T-SQL

Maximum nesting level of CASE statement

1. 7. 2020
Did you know that the CASE statement has a maximum nesting level of 10? The same limit exists for IIF() function which is just a syntactical sugar and is internally transformed into multiple CASE statements. I have prepared two samples you can play with it. Let’s start with the query…
Azure

Dynamic Data Masking in Azure SQL

31. 10. 2019

Dynamic Data Masking is available for Azure SQL Database as one of the main security features. There is more on Data Masking on Microsoft Docs. I don’t want to repeat all these technical details, but rather provide you with a simple tutorial on how to set up Dynamic Data Masking…

Read more