Notes on SQL

Random articles from a puzzled DBA

[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]

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

Continue reading

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 …

Continue reading

Background Untested backups. An awful lot is said about taking regular backups, although an inordinate number of people out there appear to ignore it. What is less talked about is actually checking that the backups are usable – it’s no good having a series of backups for that fateful day to discover that when they’re …

Continue reading

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 …

Continue reading

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 …

Continue reading

Optimistic locking requires the use of row versioning, where a copy of the data about to be modified is stored in TempDB. The advantage of this approach is that the number of locks are reduced and the opportunities for blocking are also reduced. The downside is that the resources required for data modifications are increased, …

Continue reading