Friday, March 22, 2013

Getting to know the hierarchyid Data Type


In SQL Server 2008 Microsoft added the hierarchyid data type, one of those things that I had in mind but never got to use, till recently...

So I sat down just to play & getting to know how to use it.

Started with my favorite subject: Music Library.

So I Added a sample DB with Artist, Album & Song tables.

MSDN tutorials contained samples where the hierarchy was used in the same table, that's great for an hierarchy where the hierarchy is within the same entity like employees sample (manager is an employee who manages other employees...).

I wanted an hierarchy between different types of entities, so the hierarchy will be handled in the "MusicLibrary" table and the Artist, Album & Song tables will point to it.

The GlobalEntityType table will contain a lookup table for all the entity types:

CREATE TABLE [dbo].[GlobalEntityType](
 [Code] [smallint] IDENTITY(1,1) NOT NULL,
 [Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_GlobalEntityType] PRIMARY KEY CLUSTERED 
(
 [Code] ASC
)
) ON [PRIMARY]
GO

INSERT INTO GlobalEntityType (Name) VALUES ('Artist')
INSERT INTO GlobalEntityType (Name) VALUES ('Album')
INSERT INTO GlobalEntityType (Name) VALUES ('Song')
GO


The MusicLibrary will contain the hierarchy:

CREATE TABLE [dbo].[MusicLibrary](
 [MusicLibraryId] [int] IDENTITY(1,1) NOT NULL,
 [LibraryHierarchyId] [hierarchyid] NULL,
 [LibraryHierarchyLevel]  AS ([LibraryHierarchyId].[GetLevel]()) PERSISTED,
 [EntityTypeCode] [smallint] NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[MusicLibrary]  WITH CHECK ADD  CONSTRAINT [FK_MusicLibrary_GlobalEntityType] FOREIGN KEY([EntityTypeCode])
REFERENCES [dbo].[GlobalEntityType] ([Code])
GO

ALTER TABLE [dbo].[MusicLibrary] CHECK CONSTRAINT [FK_MusicLibrary_GlobalEntityType]
GO

The Artist table:

CREATE TABLE [dbo].[Artist](
 [ArtistId] [int] IDENTITY(1,1) NOT NULL,
 [Name] [nvarchar](50) NOT NULL,
 [MusicLibraryId] [int] NULL,
 CONSTRAINT [PK_Artist] PRIMARY KEY CLUSTERED 
(
 [ArtistId] ASC
)
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Artist]  WITH CHECK ADD  CONSTRAINT [FK_Artist_MusicLibrary] FOREIGN KEY([MusicLibraryId])
REFERENCES [dbo].[MusicLibrary] ([MusicLibraryId])
GO

ALTER TABLE [dbo].[Artist] CHECK CONSTRAINT [FK_Artist_MusicLibrary]
GO

The Album table:
CREATE TABLE [dbo].[Album](
 [AlbumId] [int] IDENTITY(1,1) NOT NULL,
 [Name] [nvarchar](50) NOT NULL,
 [MusicLibraryId] [int] NULL,
 CONSTRAINT [PK_Album] PRIMARY KEY CLUSTERED 
(
 [AlbumId] ASC
)
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Album]  WITH CHECK ADD  CONSTRAINT [FK_Album_MusicLibrary] FOREIGN KEY([MusicLibraryId])
REFERENCES [dbo].[MusicLibrary] ([MusicLibraryId])
GO

ALTER TABLE [dbo].[Album] CHECK CONSTRAINT [FK_Album_MusicLibrary]
GO


The Song table:
CREATE TABLE [dbo].[Song](
 [SongId] [int] IDENTITY(1,1) NOT NULL,
 [Name] [nvarchar](50) NOT NULL,
 [MusicLibraryId] [int] NULL,
 CONSTRAINT [PK_Song] PRIMARY KEY CLUSTERED 
(
 [SongId] ASC
)
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Song]  WITH CHECK ADD  CONSTRAINT [FK_Song_MusicLibrary] FOREIGN KEY([MusicLibraryId])
REFERENCES [dbo].[MusicLibrary] ([MusicLibraryId])
GO

ALTER TABLE [dbo].[Song] CHECK CONSTRAINT [FK_Song_MusicLibrary]
GO



Fill it with some data...and finally start using the hierarchyid to build the hierarchy.


We'll start by adding a root node:

INSERT INTO [dbo].[MusicLibrary]
           ([LibraryHierarchyId])
     VALUES
           (hierarchyid::GetRoot())
GO

Now we'll build an helper stored procedure to add an artist, album & song to the library.

Use the hierarchyid methods (GetRoot, GetAncestor & GetDescendant in this case) to get the last child of the root and add the new artist under that id.

CREATE PROCEDURE [dbo].[spAddArtist]
  @ArtistId AS INT 
 AS

 IF (NOT EXISTS(SELECT ArtistID FROM Artist WHERE ArtistId=@ArtistId AND MusicLibraryId IS NOT NULL))
 BEGIN 

  DECLARE @ARTIST_TYPE smallint = 1

  DECLARE @root AS HIERARCHYID, @hid AS HIERARCHYID,
  @last_child_hid AS HIERARCHYID;

  SET @root = HIERARCHYID::GetRoot();

  SET @last_child_hid =
   (SELECT MAX(LibraryHierarchyId) FROM dbo.MusicLibrary
    WHERE LibraryHierarchyId.GetAncestor(1) = @root);

  SET @hid = @root.GetDescendant(@last_child_hid, NULL);

  DECLARE @MusicLibraryId int

  SET XACT_ABORT ON 
  BEGIN TRAN
   INSERT INTO dbo.MusicLibrary(LibraryHierarchyId,EntityTypeCode)
    VALUES(@hid, @ARTIST_TYPE)

   SET @MusicLibraryId = SCOPE_IDENTITY()

   UPDATE Artist
    SET MusicLibraryId = @MusicLibraryId
   WHERE ArtistId = @ArtistId
  COMMIT TRAN

 END

Similar logic when adding album & song to the library, but this time we'll send the 'parent' artist/album id to the stored procedure:

CREATE PROCEDURE [dbo].[spAddAlbum] 
  @AlbumId AS INT, @ArtistId AS INT
AS

DECLARE @albumHierarchyId AS HIERARCHYID, @artistHierarchyId AS HIERARCHYID,
  @last_child_hid AS HIERARCHYID;

  DECLARE @ARTIST_TYPE smallint = 1
  DECLARE @ALBUM_TYPE smallint = 2

  IF (NOT EXISTS(SELECT AlbumId FROM Album WHERE AlbumId=@AlbumId AND MusicLibraryId IS NOT NULL))
 BEGIN 

   SET @artistHierarchyId = (SELECT LibraryHierarchyId 
      FROM dbo.MusicLibrary
      INNER JOIN Artist ON 
                                                      Artist.MusicLibraryId = MusicLibrary.MusicLibraryId
            AND MusicLibrary.EntityTypeCode = @ARTIST_TYPE
       WHERE Artist.ArtistId = @ArtistId);

   SET @last_child_hid =
  (SELECT MAX(LibraryHierarchyId) FROM dbo.MusicLibrary
   WHERE LibraryHierarchyId.GetAncestor(1) = @artistHierarchyId);

   SET @albumHierarchyId = @artistHierarchyId.GetDescendant(@last_child_hid, NULL);


   DECLARE @MusicLibraryId int

   SET XACT_ABORT ON 
   BEGIN TRAN

    INSERT INTO dbo.MusicLibrary(LibraryHierarchyId,EntityTypeCode)
     VALUES(@albumHierarchyId, @ALBUM_TYPE)

    SET @MusicLibraryId = SCOPE_IDENTITY()

    UPDATE Album
     SET MusicLibraryId = @MusicLibraryId
    WHERE AlbumId = @AlbumId

   COMMIT TRAN
 END

Now use these stored procedures to connect all the entities together:

exec spAddArtist 1
exec spAddArtist 2

exec spAddAlbum 1,1
exec spAddAlbum 2,1

exec spAddAlbum 3,2
exec spAddAlbum 4,2

exec spAddSong 1,3
exec spAddSong 2,3

exec spAddSong 3,4
exec spAddSong 4,4
exec spAddSong 5,4

exec spAddSong 6,1
exec spAddSong 7,1
exec spAddSong 8,1

exec spAddSong 9,2
exec spAddSong 10,2
exec spAddSong 11,2

And final step, lets see how we can query the hierarchy
DECLARE @ARTIST_TYPE smallint = 1, @ALBUM_TYPE smallint = 2, @SONG_TYPE smallint = 3


DECLARE @SampleArtist hierarchyid

SELECT @SampleArtist = MusicLibrary.LibraryHierarchyId
FROM Artist 
  INNER JOIN MusicLibrary ON 
            Artist.MusicLibraryId = MusicLibrary.MusicLibraryId
            AND EntityTypeCode = @ARTIST_TYPE
WHERE ArtistId = 2

-- Albums of specific artist
SELECT Album.*
FROM Album 
  INNER JOIN MusicLibrary ON 
         Album.MusicLibraryId = MusicLibrary.MusicLibraryId 
         AND EntityTypeCode = @ALBUM_TYPE
 WHERE LibraryHierarchyId.IsDescendantOf(@SampleArtist) = 1

 --Songs of specific artist
SELECT Song.*
FROM Song 
  INNER JOIN MusicLibrary ON 
              Song.MusicLibraryId = MusicLibrary.MusicLibraryId 
              AND EntityTypeCode = @SONG_TYPE
 WHERE LibraryHierarchyId.IsDescendantOf(@SampleArtist) = 1


So you may ask: "what do I need this complexity? we can add a reference from song to album, from album to artist and that's it..."

Well...it's not a better solution, just a different one, I can think of two advantages:

1. What if we add a 'single', a 'single' is a song that is a child of an artist not an album.
If we use foreign keys we'll add another key to song pointing the artist table and than the song table would have sometimes an albumId with a value and artistId with null or vice versa - doable but not so elegant.
With hierarchy we can add a song under an artist without adding another key, just relate the song under the required artist...

2. Isn't this elegant :

SELECT CASE 
 WHEN Artist.Name IS NOT NULL THEN 
             Artist.Name
 WHEN Album.Name IS NOT NULL THEN 
             REPLICATE('     ', LibraryHierarchyLevel) + Album.Name
 WHEN Song.Name IS NOT NULL THEN 
             REPLICATE('     ', LibraryHierarchyLevel) + Song.Name
      END AS Name
FROM MusicLibrary
  LEFT JOIN Artist ON Artist.MusicLibraryId = MusicLibrary.MusicLibraryId 
                      AND EntityTypeCode = @ARTIST_TYPE
  LEFT JOIN Album ON Album.MusicLibraryId = MusicLibrary.MusicLibraryId 
                     AND EntityTypeCode = @ALBUM_TYPE
  LEFT JOIN Song ON Song.MusicLibraryId = MusicLibrary.MusicLibraryId 
                     AND EntityTypeCode = @SONG_TYPE
WHERE EntityTypeCode IS NOT NULL
ORDER BY LibraryHierarchyId



Till next time..
Diego