Shortest Path for Friend Connections
An interesting problem to solve is finding relation paths in contact management systems. This is a limited case of the Dijkstra’s algorithm for finding the shortest path in a graph. Here we always have cost of 1 for each path and it is a two-way relation between the nodes. To put this in human readable […]
Column Alias Based on Variable
Although formatting output belongs to the reporting or front-end interface, occasionally there could be the need to change a column alias based on variable. Since T-SQL does not support proving variable as column alias in a query, here are two methods to handle that. CREATE TABLE Foo ( keycol INT PRIMARY KEY, datacol CHAR(1)) INSERT INTO Foo VALUES […]
Performing UPSERT in T-SQL
Very often there is the need to check if a key value exists to perform an update, and if it does not exist to insert new data. The upcoming SQL Server 2008 provides the MERGE statement (MERGE actually allows to do more: simultaneous UPDATE, INSERT and/or DELETE operations on the table), but until it is […]
Table Value Constructors in SQL Server 2008
One of the new features of SQL Server 2008 is the support for table value constructors (part of ANSI SQL). Here are a couple quick examples of using them. — Populate sample table CREATE TABLE Foo ( keycol INT PRIMARY KEY, datacol VARCHAR(30)); In the past, populating table rows was done like this: INSERT INTO Foo […]
Calculating Work Hours
Calculating work hours is a very frequent need in scheduling, billing, payroll, and time and attendance applications. Normally this is best done using an auxiliary Calendar table which provides easy way for calculation and flexibility to account for holidays and other events. However, sometimes the requirements are simplified and the point of interest is only […]
Unique Column with Multiple NULLs
A very frequent need is to define unique column that allows multiple NULL values. Since the UNIQUE constraint considers two NULL values the same, it allows only a single NULL value. Here is one solution for this problem using a view filtering on non NULL values with UNIQUE index on the view. CREATE TABLE dbo.Foo […]