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.
SQL
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 …
![[url=http://www.cartoonbucket.com/cartoons/road-runner-teasing-wile-e-coyote/][img]http://www.cartoonbucket.com/wp-content/uploads/2015/07/Road-Runner-Teasing-Wile-E.Coyote-600x489.jpg[/img][/url]](https://sqlrambling.net/wp-content/uploads/2017/06/road-runner-teasing-wile-e-coyote.jpg?w=900)
BackgroundWhen 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. This is …
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.
For several years I have happily relied on @@IDENTITY to return the latest identity of a row when inserting data. However, I have started using the OUTPUT clause and have discovered the entertainment to be had in getting the latest identity when writing to two or more tables with what can appear to be one …
Recently I have had to extract user’s details from Active Directory (AD) for certain security groups. Having looked through a slew of internet resources it is obvious that the work required to do this has changed little over many years – and it looks like is was deliberately designed to make it difficult. I’d like …
Partitioned views have been around since before SQL Server 2005 (whereupon partitioned tables were introduced) but this does not mean that they aren’t without their uses now.
A heap is a table that has no clustered index – it may have non-clustered indexes but without a clustered index it is a heap. When a row is updated within the heap, if the new data size means a row cannot be stored in the original location, SQL Server moves the row to another …
Notes on SQL 