That's an easy thing to do and you have much more control over what the columns may be like than you might imagine.

For example, here's the final SELECT from a CTE that I use to begin the process of converting an Adjacency List to Nested Sets (article comes out Nov 13th, 2012).

SELECT EmployeeID = ISNULL(sorted.EmployeeID,0),

sorted.ManagerID,

HLevel = ISNULL(sorted.HLevel,0),

LeftBower = ISNULL(CAST(0 AS INT),0), --Place holder

RightBower = ISNULL(CAST(0 AS INT),0), --Place holder

NodeNumber = ISNULL(ROW_NUMBER() OVER (ORDER BY sorted.SortPath),0),

NodeCount = ISNULL(CAST(0 AS INT),0), --Place holder

SortPath = ISNULL(sorted.SortPath,sorted.SortPath)

INTO dbo.Hierarchy

FROM cteBuildPath AS sorted

First, all of the ISNULL's make for NOT NULL columns in the final table. That's especially important for the EmployeeID column because it will become the PK for this table using a separate piece of code.

The other thing to notice is that it also creates 3 "place holder" columns in the table for which I no data for at this point in the process. It even controls what the datatype will be for those columns using CAST.