Duplicating an AG to another environment, for migration purposes.
T-SQL
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.
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.
Whilst reading an article on Quora I saw mention of TABLESAMPLE. I had never heard of it and upon investigation it piqued my interest.
Introduction When setting the value of an XML column, so-called ‘insignificant’ white space can be removed. Generally this is not an issue but if the white space is required there are a couple of ways to prevent this.
Introduction OPENXML is used to produce a rowset over an XML document. It isn’t something I’ve ever used and it took a while to understand the use of flags that it can use. The following article contains several examples, using the various flag settings as well as an example of the metaproperty attributes.
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 …
Want to delete a couple of records quickly from a large table; well DELETE is more than capable (indexing issues aside). Want to delete a couple of million from a large table quickly? Now it can get a little more interesting. Partitioning is a very useful tool for moving large amounts of data very quickly. …
Notes on SQL 