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 …
Having attempted to start an old test instance from SQL Server Configuration Manager, I received the wonderful error “The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details” More helpful than some message you can get – at least it tells …
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 …
In a previous article I described how to use a certificate to sign a stored procedure. Part of that article showed how to copy the certificate between databases by using a backup/restore method. This article shows another method – scripting.
And how to assign Server-Level permissions to a database user. Background In a previous article I gave an example of assigning permissions to a user and making use of the EXECUTE AS instruction. This is a very useful way of assigning database permissions in a very granular way, where a user should not have the …
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.
Some years ago a wrote an article about automatic data purging with partition switching. Having moved to SQL Server 2016 recently I have encountered the new ability to truncate individual partitions, rather than switching data out.
Whilst reading up on SQL Server 2016 JSON functionality I have seen many examples of extracting data from a JSON array. However, I wanted to work out how to extract data from an array within an array – for example, an array of customer data where each customer has an array of order details.
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.