Archive November 2018

Recursivity in SQL Server with Common Table Expressions

Probably you never thought that there is a nice way to make recursivity in a SQL Server Query but… yes… since CTE’s were launched in version 2005 is there.


So, I was thinking to offer a good and easy example where we can take power of this capability and found the Adventure Works DB (tested in both AdventureWorks2016 & AdventureWorks2016_EXT) is a very good example as already includes the required structures because it includes a field called OrganisationLevel that links the employee with the BusinessEntityID for the corresponding line manager.

In the sample below, we are searching for all employees with the list of managers up to the CEO. I have calculated both in text (Job Title) and Business Entity ID (Manager ID sequence), both separated by ” | “.

This functionallity opens several doors for a lot of good practises. You can nest CTE’s, use the same CTE several times within a query or even use them inside Views, SP’s or Functions and give functional names to very complex subqueries to make the code much easier to understand.

In the recent past I have used several times when need to mix complex queries with tricky table & field names. Within the CTE can extract the information required and then link more than one CTE with friendly names. The final product is very readable and much easier to maintain.

Query the System Data Dictionary to find any Table, View, SP, Function or even Column

Have you had the need to find an element in the Database but you don’t know the full detail?

Often, I remember part of the data, for example, remember that the field name contains the word Packaging, Supplier… other times, I don’t know if it is a View, SP, Table or Function but I remember that contains the word Calculate… 

to easily find them I have created the attached query that accesses the Data Dictionary. Just enter in the parameters area the Table, the Column, or both and the system will find whatever there is in the current Database.

How to create a safe environment for testing

First step to create any structure starts by creating a safe sandbox area.

There are several ways, some of them are just to create dummy databases in a development machine and do the tests there what it is Ok in most of the cases.

Anyway, for the safest testing environment, we recommend to install a Virtual Machine, for example by using Oracle VM Virtual Box.

The environment where we will run most of the tests is a Virtual Machine with Windows Server 2016 Datacenter Evaluation with SQL Server 2016 and Microsoft SQL Server Enterprise Manager 17.9.1.

Here is where start the first trick, if you haven’t allocated initially enough disk to the virtual machine, you can enlarge it by following two steps (obviously the VM needs to be closed):

  • First Step is to run the MS-Dos command to enlarge the disk to 35 Gb

“C:\Program Files\Oracle\VirtualBox\VBoxManage.exe” modifyhd “C:\Users\LEN\VirtualBox VMs\Windows Server-2018-11\Windows Server-2018-11.vdi” –resize 35000

  • Second step, when opening again the Virtual Machine, you need to use the Disk Admin to assign the disk to the OS, choose the option by default to assign all available space.

And… that’s all, we have now enough space for our tests.