A very interesting tool to understand the items being affected by an Insert, Update or Deletion

Several times we have been asked to make some amendments directly in a Production database and we are afraid of making some incorrect modifications. Before to begin, even if it looks like you are 100% sure, I would recommend you to make a previous copy of the table being affected for the changes.


Then, let’s introduce the clause “OUTPUT TO” that was introduced in SQL Server 2008 as a tool to show or save the records being affected.

As first example that can be run in the typical AdventureWorks example DB, we are going to show first the pure output on screen. On this case, we are starting a transaction that will rollback afterwards to prevent the changes to be physically applied. While we run the UPDATE command, after the SET we use the OUTPUT clause where we can include any field from the two ficticiuos tables INSERTED (new values) and DELETED (old values). For the fields that are not changing is the same if we use INSERTED or DELETED but it is quite different in the fields that are changing: ListPrice_BeforeChanges (= Deleted.ListPrice) and ListPrice_AfterChanges (= Inserted.ListPrice).

We can do the same for the commands INSERT or DELETE with the difference that in Insert we can use only INSERTED and in DELETE we can access only to DELETED tables.

Now, let’s do a more advanced usage for this powerful tool. On this case we will record the results in a table memory that we can potentially use to register in a log, save or record in a log table.

First, we define the table with the structure required, then, we add the INTO clause to redirect the OUTPUT to the memory table. Last, we use the memory table to show the results. Important note: as the memory tables are not affected by the transactions, even if we rollback the current one, the memory table still keeps the contents. 

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.