Introduction Most systems that I have worked with make use of the XML data type. I don’t find it to be the most intuitive data type to work with but most aspects of it can be built upon from a few basic examples. This will be part of a developing series that will cover XML …

Introduction As part of my research into the various methods of protecting sensitive data, I’ve taken a look at dynamic data masking. A short demonstration and some observations are in the following article.

A column set is an untyped XML column that can be used to update and select all sparse columns defined in the associated table. This XML is not physically stored in the table – it is in effect a calculated column that can be used to update. Microsoft recommend its use for tables that contain …

Introduction Ownership chaining is one process that SQL Server uses to allow stored procedures access to tables where the user might not have permission. It has issues with stored procedures that have dynamic sql – EXECUTE AS is one solution.

Background For a long time, web developers have been able to store session information. SQL Server has CONTEXT_INFO, which is a very poor implementation of a session variable. However, with SQL Server 2016 there is now a far more flexible SESSION_CONTEXT feature available.

Introduction Whilst reading Itzik Ben Gan’s excellent book on T-SQL I came across the COMPRESS and DECOMPRESS functions. These could be quite useful for some situations that I have, so decided to have a closer look.

This article first appeared on SQL Server Central on 30 Dec 2015. Frequently there are questions relating to transactions posted on various forums and although the questions show a basic misunderstanding of this aspect of SQL Server, sometimes the answers show some misunderstanding also. I initially started an article about nested transactions, because that is …

Recently I’ve been making a lot of use of the OUTPUT option for insert and update actions. From this work I’ve already posted about the behaviour of IDENTITY and suchlike. Now I have looked at the behaviour of OUTPUT within the MERGE command.