Hi all,

I just started learning HierarchyID and copied the the following code from http://stackoverflow.com/questions/443930/generating-hierarchyid and Chapter 7 Hierarchical Data and the Relational Database of the book "Programming Microsoft SQL Server 2012", written by L. G. Lobel and A. J. Brust (Published by Microsoft Press):

--  shcHierarchyid_1.sql
--   19 August 2015  13:15 PM

USE master
GO
IF EXISTS(SELECT name FROM sys.databases WHERE name = 'shcDB')
 DROP DATABASE shcDB 
GO
CREATE DATABASE shcDB
GO

USE shcDB
GO

CREATE TABLE OrgChart
(
  Position hierarchyid,
  Title nvarchar(50)
)

INSERT into OrgChart(Position, Title)
   values (hierarchyid::Parse('/'), 'CEO'),
          (hierarchyid::Parse('/1/'), 'Purchase Manager'),
	      (hierarchyid::Parse('/1/1/'), 'Purchase Executive'),
          (hierarchyid::Parse('/2/'), 'Sales Manager'),
          (hierarchyid::Parse('/2/1/'), 'Sales Executive')

SELECT Position.ToString(), * FROM OrgChart

/*
	C L E A N U P
*/

DROP TABLE OrgChart
GO

I executed the code in my Microsoft SQL Server 2012 Management Studio (SSMS2012).  It worked nicely. But, I don't understand the following 2 things:

#1: In the Results, I got:           (No column name)     Position    Title

                                         1       /                                            CEO

                                         2      /1/                         0x58         Purchase Manager

                                         3      /1/1/                      0x5AC0     Purchase Executive

                                         4      /2/                         0x68         Sales Manager

                                         5     /2/1/                       0x6AC0     Sales Executive

   I don't know how to give a column name for the /, /1/, /1/1/, /2/, /2/1/ (NodePath (?) I guessed). Please tell me how I can give a column name to that column.

#2:   The book lists "USE master  GO .....".  I wonder whether I can do the "hierarchyid" thing in the private database I created!!??  Please tell me whether I can do the "hierarchyid" thing in the private database or not.

Please kindly help, advise and respond.

Thanks in advance,

Scott Chang   

• Edited by Scott H Chang 11 hours 46 minutes ago

1. Simply use

SELECT Position.ToString() AS NodePath, * FROM OrgChart

/*
	C L E A N U P
*/

DROP TABLE OrgChart

2. The table was created in your own database, not in the master DB. In order to create tables in your own DB just make sure this DB is selected and you can do

USE myDB

go

before trying to create any new

You may create hierarchyid columns in any database.  In fact, the example you show creates the table in a user database named shcDB, not in master.  The code only uses master to create the new database.

To assign a column name, just specify the name you want in the select statement, e.g.,

SELECT Position.ToString() As WhatEverNameIWant, * FROM OrgChart