Archive | SQL RSS feed for this section

Automatic Data Purging With Partition Truncation

21 Feb

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

XMLNAMESPACES – Basic Example

19 Dec

Introduction
I tend not to use XML very often, so have limited experience with it. Whilst reading Itzik Ben-Gan’s (b|t) book for 70-761 I came across XMLNAMESPACES for the first time.
The section on this aspect is quite brief, so this blog contains the additional work I used to ensure I understood it.
Continue reading