A while back I came across a problem a concurrency issue at work that took several iterations to determine an appropriate fix for. The basic problem was that we had a table representing a parent entity and a dependent child entity. The child entity had an integer column to specify the sort ordering of the children within the parent. For a more concrete example imagine a photo management application. You may have a gallery entity (the parent) which contains a list of photos (the child entity) which can be re-arranged by the user.
For the sake of simplicity we'll strip down a lot of the properties on these entities to the bare essentials required for our example.
CREATE TABLE Gallery (
Id INTEGER IDENTITY(1,1) PRIMARY KEY,
Name VARCHAR(50) NOT NULL
)
CREATE TABLE Photo (
Id INTEGER IDENTITY(1, 1) PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
GalleryId INT NOT NULL,
SortOrder INT NOT NULL,
CONSTRAINT FK_Ph...
SQL Server Tricks
2015-09-06
Throughout my career I've spent more than my fair share of time in the database layer. There's a current trent in the .Net world of just letting your ORM take care of everything in the data layer for you. I'm a huge proponent of using an ORM for mapping (at work we're currently using StackExchange's Dapper.net). However I've always been a little wary of letting the ORM do everything for you. As such I thought I'd jot down a few handy SQL Server tricks I've picked up over the years.
1. The Output Clause
The OUTPUT clause let's you execute a data modification statement and have it project a result set. In plain English that means that you can perform a DELETE, INSERT or UPDATE and have it return a resultset. This has been around since SQL Server 2008 but doesn't seem ...