I found this great program that puts together a custom internet radio station for you.

Check out www.pandora.com

This program will take a single request of an artist or song and create an entire radio station of music that you will love, from various artists; some of which you probably never heard of.

I am a SQL server architect and programmer. I enjoy discovering new methods and approaches to everyday problems in using SQL Server.

If anyone requires assistance, advise, or simply would like to know how to deal with or approach a problem please let me know.

I would be happy to assist and publish these methods here on my blog.

I can cover anything related to SQL server 2000, 2005 and I am presently playing with MS SQL 2008.

I wrote this code to determine if a given time / date fell within the scope of a set period. This was used in a scheduling system to determine availability of a desired appointment time.  After searching the net I was unable to find pre-constructed code. Being that the time / date format in SQL server can be a real pain to work with I thought I would publish my work for the benefit of others.

The following code will provide an accurate time dimension.

/***************************************
Script: Creates and Populates an Hour Dimension Table

Note: This script does hours and minutes. If seconds are needed,
then modify the datepart on the last line that increments the date.
Obviously the table will be bigger when you include seconds.

Author: Jeffery Williams
Date: 11/11/2007

You May need to use a dimention to calculate if a scheudule rides into a
TimePeriod.

****************************************/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dimHour]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[dimHour]
GO

CREATE TABLE dimHour
(
    HourID       INT  IDENTITY (1, 1)   NOT NULL    PRIMARY KEY
,   TheDate      DATETIME   NOT NULL
,   TheTime      Datetime   NOT NULL
,   MilitaryHour INT NOT NULL
,   StandardHour  INT NOT NULL
,   TheMinute    INT NOT NULL
,   TheSecond    INT NOT NULL
,   Standard      varchar(2) NOT NULL
)

DECLARE    @startdate  DATETIME
DECLARE    @enddate    DATETIME
DECLARE    @date       DATETIME

SET        @startdate  =    '1/1/2005 12:00:00 AM'  
SET        @enddate    =    '1/1/2005 23:59:59 PM' 
SET        @date       =     @startdate

WHILE    @date <= @enddate
BEGIN
    INSERT INTO    dimHour (TheDate, TheTime, MilitaryHour, StandardHour, TheMinute, TheSecond, Standard)
    VALUES (
         @date                                                             --TheDate
    ,    convert(nvarchar(11), @date, 114)                                 --Time format  
    ,    DATEPART(hh, @date)                                               --Military Hour
    , CONVERT(varchar(2),
          CASE
               WHEN DATEPART([hour], @Date) > 12 THEN CONVERT(varchar(2), (DATEPART([hour], @Date) - 12))
               WHEN DATEPART([hour], @Date) = 0 THEN '12'
               ELSE CONVERT(varchar(2), DATEPART([hour], @Date))
          END)                                   -- Standard Hour
    ,    DATEPART(mi, @date)                                               --Minute
    ,    DATEPART(ss, @date)                                               --Second
    , CASE WHEN DATEPART(hh, @date) between 0 and 11 THEN 'AM' ELSE 'PM' END  
    )

    SET  @date  =    DATEADD(mi, 1, @date) -- ** Modify the datepart to ss if seconds are needed

END

How do you apply uniqueness to only non-NULL values? Find out the answer here.

As an example lets say you have a field in a table that requires a unique constraint.  In this row however you have some records with a NULL value.  With SQL server you can not directly apply a unique constraint the this row because of the NULL records that exist.

This particular issue of uniqueness around NULLs is somewhat controversial within the SQL community. Because NULL = NULL is false, how can two NULLs not be unique? In the case of SQL Server, as you've discovered, two NULLs are not considered unique, but in other databases, notably Oracle, they are. Be that as it may, there are a couple of ways to deal with this problem. I'll show you both.

Just to set the stage clearly for the problem, here's a table:

CREATE TABLE Employee (
EmployeeID INT IDENTITY NOT NULL,
LastName VARCHAR(50) NOT NULL,
FirstName VARCHAR(50) NOT NULL,
SSN CHAR(11) NULL,
CONSTRAINT PK_Employee PRIMARY KEY (EmployeeID));

CREATE UNIQUE INDEX IDX_Employee_SSN
ON Employee (SSN);

Notice that I've created a unique index on the SSN. So now insert some rows:

INSERT INTO Employee (LastName, FirstName, SSN)
VALUES ('Forte', 'Steve', NULL);

INSERT INTO Employee (LastName, FirstName, SSN)
VALUES ('Campbell', 'Richard', NULL);

The second insert fails because the second null SSN violates the uniqueness constraint.

The first solution I have to this is to use a view to get rid of the nulls, and then index the view. So the first step is to remove the unique index I created earlier, then create a view and put a unique index on the view:

DROP INDEX IDX_Employee_SSN ON dbo.Employee;

CREATE VIEW Employee_SSN_NOT_NULL
WITH SCHEMABINDING AS
SELECT LastName, FirstName, SSN
FROM dbo.Employee
WHERE SSN IS NOT NULL;

CREATE UNIQUE CLUSTERED INDEX Employee_SSN_Unique
ON Employee_SSN_NOT_NULL (SSN);

Notice the view I've created has the "WITH SCHEMABINDING" clause on it, which tells SQL Server that the underlying table cannot be modified in a way that affects the view without first dropping the view. SQL Server requires that constraint so I can execute the final statement of the three, adding an index to the view. And it's that index that provides the uniqueness constraint, but only on the rows in Employee that have an SSN.

This is a somewhat elaborate way to address the issue, but it certainly works. Some folks don't like it because they use views for other things and don't want views for uniqueness cluttering up their schema. There is a simpler, somewhat "hackier" way to work around the problem, using a thing called the "nullbuster" as coined by Steve Kass.

The simplest way to understand the nullbuster in action is to look at the code:

CREATE TABLE Employee (
EmployeeID INT IDENTITY NOT NULL,
LastName VARCHAR(50) NOT NULL,
FirstName VARCHAR(50) NOT NULL,
SSN CHAR(11) NULL,
nullbusterSSN AS
(CASE WHEN SSN IS NULL THEN EmployeeID ELSE 0 END),
CONSTRAINT PK_Employee PRIMARY KEY (EmployeeID));

CREATE UNIQUE INDEX IDX_Employee_SSN
ON Employee (SSN, nullbusterSSN);

So once again I've created the Employee table and added an index with a unique constraint on it. The difference is the nullbusterSSN column. The nullbusterSSN column is a derived column that uses a CASE statement to check to see if SSN is NULL. If it is, nullbusterSSN contains the EmployeeID. If SSN is not NULL, then nullbusterSSN contains 0. The unique index I created is a composite index containing both SSN and nullbusterSSN.

The result is that you can have as many NULL SSNs as you like and the unique constraint won't fire, but as soon as you try and write two rows with the same non-NULL SSN, you'll hit the uniqueness constraint.

So now instead of having an extra view in the database schema, I have an extra column in the table. This might annoy you even more; it's very much a personal choice. Both approaches work equally well. Choose whichever one you prefer.

We could build a more elaborate schema that had a separate table for SSNs and dealt with uniqueness that way, but I find that even more complicated and fraught with peril. As soon as you introduce another table, you run the risk that an employee has more than one SSN assigned to them. I'd prefer either one of these hacks to that.