Table Value Constructors in SQL Server 2008

/2 Comments/in /by

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 VALUES (1, 'Books');
INSERT INTO Foo VALUES (2, 'CDs');
INSERT INTO Foo VALUES (3, 'DVDs'); 
 
-- or 
 
INSERT INTO Foo (keycol, datacol)
SELECT 1, 'Books'
UNION ALL
SELECT 2, 'CDs'
UNION ALL
SELECT 3, 'DVDs';
 
-- or using on the fly
 
SELECT keycol, datacol
FROM ( SELECT 1, 'Books'
       UNION ALL 
       SELECT 2, 'CDs'
       UNION ALL
       SELECT 3, 'DVDs') AS Foo (keycol, datacol);

Here is how the same can be done with SQL Server 2008 table value constructors:

INSERT INTO Foo (keycol, datacol)
VALUES (1, 'Books'), (2, 'CDs'), (3, 'DVDs');
 
-- or using on the fly
 
SELECT keycol, datacol
FROM ( VALUES (1, 'Books'),
              (2, 'CDs'),
              (3, 'DVDs') ) AS Foo (keycol, datacol);
 
-- and CTE version
 
WITH Foo (keycol, datacol)
AS( SELECT *
    FROM (  VALUES  (1, 'Books'),
                    (2, 'CDs'),
                    (3, 'DVDs') ) AS F (keycol, datacol))
SELECT keycol, datacol
FROM Foo;

Another interesting option is to derive a row value from a subquery, like this:

INSERT INTO Foo (keycol, datacol)
VALUES ((SELECT MAX(keycol) + 1 FROM Foo), 'Tapes');

Still not possible, but maybe in the next version (or Service Pack) we can see vector expressions in predicates and UPDATE, like:

SELECT keycol, datacol
FROM Foo
WHERE (keycol, datacol) IN (SELECT keycol, datacol FROM Foo2);
 
-- or
 
UPDATE Foo
SET (keycol, datacol) = (SELECT keycol, datacol FROM Foo2);

Calculating Work Hours

/17 Comments/in /by

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 the work hours between two dates, accounting only for weekends, without holidays. Here is one way to perform this calculation using the date and time functions in SQL Server. Note that for this example the week start day is logically set to Monday by adding (@@DATEFIRST – 1) to the start date.

CREATE FUNCTION dbo.CalcWorkHours (
 @start_date DATETIME,
 @end_date DATETIME) 
RETURNS INT
AS
BEGIN
  RETURN
  (SELECT ((total_days / 7) * 5 + total_days % 7 - 
           CASE WHEN 6 BETWEEN start_weekday AND end_weekday
                THEN 1 ELSE 0 END -
           CASE WHEN 7 BETWEEN start_weekday AND end_weekday
                THEN 1 ELSE 0 END) * 8
    FROM (SELECT total_days, start_weekday,
                 start_weekday + total_days % 7 - 1
          FROM (SELECT DATEDIFF(day, @start_date, @end_date) + 1,
                       DATEPART(WEEKDAY, @start_date + @@DATEFIRST - 1)
               ) AS T(total_days, start_weekday)
         ) AS D(total_days, start_weekday, end_weekday)
  );
END
GO
 
SELECT dbo.CalcWorkHours('20080401', '20080421') 
 
/*
 
-- Result
120
 
*/

Unique Column with Multiple NULLs

/1 Comment/in /by

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 (
 keycol INT NULL,
 datacol CHAR(1));
 
GO
 
CREATE VIEW UniqueFoo WITH SCHEMABINDING
AS
SELECT keycol, datacol
FROM dbo.Foo
WHERE keycol IS NOT NULL;
 
GO
 
CREATE UNIQUE CLUSTERED INDEX ix_UniqueFoo
ON UniqueFoo(keycol);
GO
 
-- OK, multiple NULL values allowed
INSERT INTO dbo.Foo VALUES(1, 'a');
INSERT INTO dbo.Foo VALUES(NULL, 'b');
INSERT INTO dbo.Foo VALUES(NULL, 'c');
 
GO
 
-- Error, attempt to insert duplicate keycol value 
INSERT INTO dbo.Foo VALUES(1, 'd');
 
/*
 
Msg 2601, Level 14, State 1, Line 2
Cannot insert duplicate key row in object 'dbo.UniqueFoo' with unique index 'ix_UniqueFoo'.
The statement has been terminated.
 
*/

In SQL Server 2008 this can be accomplished much easier using UNIQUE filtered index.

CREATE UNIQUE NONCLUSTERED INDEX ix_Foo
  ON Foo (keycol)
  WHERE keycol IS NOT NULL;