Tag Archives: T-SQL

Using EXECUTE AS to control data access

17 Apr

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. Continue reading

Shredding JSON Nested Arrays

5 Feb

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.
Continue reading

SESSION_CONTEXT – Basic Examples

1 Feb

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.
Continue reading

TABLESAMPLE – Basic Examples

24 Jan

Whilst reading an article on Quora I saw mention of TABLESAMPLE. I had never heard of it and upon investigation it piqued my interest.
Continue reading

Preserving White Space in ‘Empty’ XML Elements

29 Dec

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.
Continue reading

OPENXML – Basic Examples

22 Dec

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.
Continue reading

Explicit Transactions

19 Jul

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 one of the areas that appear to cause the most confusion (and I wanted to make sure I understood it too). However, in looking into this I have also investigated other areas related to explicit transactions where I believe some simple examples could help to clarify.
This article will look at the various properties and usage of explicit transactions, as well as some behaviours that are frequently misunderstood.
Continue reading