Archive | SQL RSS feed for this section

MERGE, OUTPUT and $Action

19 May

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

Primary Key With Non Clustered Index

28 Apr

As a default, SQL Server creates a Primary Key column with a Clustered Index. However, this doesn’t have to be the case.
Continue reading

The Joys of @@IDENTITY, Scope_Identity and IDENT_CURRENT

31 Mar

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

Checking SQL Backups Automatically

25 Jan

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 needed that they’re useless.
Continue reading

LDAP – Using SQL to get Active Directory details – Basic Examples

14 Jul

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 to think it wasn’t but, that is how it looks. Continue reading

Partitioned Views

17 Dec

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

Forwarding Pointers and Fragmentation on a Heap Table

3 Dec

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 location and leaves a pointer (called a forwarding record) to show where it has been moved to.

Continue reading