深入讲解SQL Server 2008的新版CTP特性

CREATE TABLE Organization 
( 
NodeLevel hierarchyid, 
EmployeeID int, 
OrgLevel as NodeLevel.GetLevel(), 
EmployeeName nvarchar(50) NOT NULL 
) ; 
GO 
insert into Organization 
(NodeLevel, EmployeeID, EmployeeName) 
values 
(hierarchyid::GetRoot(),0, 'Bob') 
go 
Declare @Manager hierarchyid 
SELECT @Manager = hierarchyid::GetRoot() 
FROM Organization ; 
insert into Organization 
(NodeLevel, EmployeeId, EmployeeName) 
values 
(@Manager.GetDescendant(null, null), 1, 'Joe') 
go 
Declare @Manager hierarchyid 
declare @NodeLevel hierarchyid 
select @NodeLevel = NodeLevel 
from Organization 
where EmployeeName = 'Joe' 
SELECT @Manager = max(NodeLevel) 
FROM Organization 
where NodeLevel.GetAncestor(1) = @NodeLevel 
insert into Organization 
(NodeLevel, EmployeeID, EmployeeName) 
values 
(@NodeLevel.GetDescendant(@Manager, null),2, 'Sarah') 
go 
select NodeLevel.ToString()as NodeLevel_String, * 
FROM Organization 
go 
drop table Organization 
go

NodeLevel_StringNodeLevelEmployeeIDOrgLevelEmployeeName 
/0x00Bob 
/1/0x5811Joe 
/1/1/0x5AC022Sarah

Create a user-defined data type
 with a single column. 
Develop a procedure with a table 
variable as an input parameter. 
Declare a table variable of the type 
of the user defined data type. 
Loading 10 records into the table variable 
and pass the table variable to the stored procedure. 
create type tt_example AS TABLE 
(spid int) 
go 
create procedure usp_example 
@spids tt_example READONLY 
AS 
SELECT * 
FROM @spids 
GO 
declare @spids tt_example 
insert into @spids 
select top 10 spid 
from sys.sysprocesses 
exec usp_example @spids=@spids