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.
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.
Introduction A couple of years a go I wrote an article about monitoring the version store. With SQL Server 2017 a new DMV has been added to make this easier.
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.
Introduction SQL 2016 has introduced support for JSON data. The nature of JSON data means that an indexing strategy isn’t as obvious. This article details my initial work, testing various methods for accessing the JSON data.
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 …
Background When SQL Server extends data or log files it fills the newly-acquired space with zeroes, to overwrite any data that may have been there beforehand. This action takes place when a database is created, restored, files are extended or added. This can take a long time, depending upon the amount of space being initialised. …
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.
As a default, SQL Server creates a Primary Key column with a Clustered Index. However, this doesn’t have to be the case.