Sure... the following code creates a million row "clean" hierarchy in the form of a classic Adjacency List. I have it as a stored procedure so that I can just call it with the number of rows I want the hierarchy to contain for different experiments...

CREATE PROCEDURE dbo.BuildTestHierarchy

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

Create a randomized "clean" hierarchy. Each EmployeeID (except the first one, of course) is assigned a random

ManagerID number which is always less than the current EmployeeID. This code runs nasty fast and is great for

testing hierarchical processing code.

Usage: (both examples build a million row Adjacency List Hierarchy)

EXEC dbo.BuildTestHierarchy 1000000

Revision History:

Rev 00 - 28 Apr 2010 - Jeff Moden - Initial creation and test.

Rev 01 - 15 May 2010 - Jeff Moden - Abort if current DB isn't "tempdb" to protect users that want to "play".

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

--===== Declare the I/O parameters

@pRowsToBuild INT

AS

--===== Make sure that we're in a safe place to run this...

IF DB_NAME() <> N'tempdb'

BEGIN

RAISERROR('Current DB is NOT tempdb. Run aborted.',11,1);

RETURN;

END

;

--===== Conditionaly drop the test table so we can do reruns more easily

IF OBJECT_ID('TempDB.dbo.Employee','U') IS NOT NULL

DROP TABLE TempDB.dbo.Employee

;

--===== Build the test table and populate it on the fly.

-- Everything except ManagerID is populated here.

SELECT TOP (@pRowsToBuild)

ISNULL(ROW_NUMBER() OVER (ORDER BY (SELECT 1)),0) AS EmployeeID,

CAST(0 AS INT) AS ManagerID,

CAST(NEWID() AS VARCHAR(36)) AS EmployeeName,

(ABS(CHECKSUM(NEWID()))%12+1)*1000 AS Sales

INTO TempDB.dbo.Employee

FROM Master.sys.All_Columns ac1

CROSS JOIN Master.sys.All_Columns ac2

;

--===== Update the test table with ManagerID's. The ManagerID is some random value which is always less than the

-- current EmployeeID to keep the hierarchy "clean" and free from "loop backs".

UPDATE TempDB.dbo.Employee

SET ManagerID = CASE

WHEN EmployeeID > 1

THEN ABS(CHECKSUM(NEWID())) % (EmployeeID-1) +1

ELSE NULL

END

;

--===== Add some indexes that most folks would like have on such a table

ALTER TABLE TempDB.dbo.Employee ADD CONSTRAINT PK_Employee PRIMARY KEY CLUSTERED (EmployeeID);

CREATE INDEX IX_Employee_ManagerID ON TempDB.dbo.Employee (ManagerID);