Monday, October 24, 2011

What does my SQL Server data look like over the wire?

What does my SQL Server data look like over the wire?

Thursday, September 22, 2011

Build Hierarchy table from core table

if(object_id('tempdb..#tmp')>1) drop table #tmp declare @depth int = 0 , @count int = 0 create table #tmp (parent_id int, child_id int, depth_from_parent int) SET @depth = 0; TRUNCATE TABLE #tmp; INSERT INTO #tmp(parent_id, child_id, depth_from_parent) VALUES (1, 1, @depth) WHILE @@ROWCOUNT > 0 BEGIN SET @depth = @depth + 1 INSERT INTO #tmp(parent_id, child_id, depth_from_parent) OUTPUT inserted.child_id, inserted.child_id, 0 INTO #tmp SELECT 1, ou.ou_id, os.depth_from_parent + 1 FROM #tmp os (NOLOCK) INNER JOIN ou (NOLOCK) ou ON os.child_id = ou.parent_id WHERE os.depth_from_parent = @depth - 1; END SET @depth = 0 WHILE @@ROWCOUNT > 0 BEGIN SET @depth = @depth + 1; INSERT INTO #tmp(parent_id, child_id, depth_from_parent) SELECT os.parent_id, ou.ou_id, @depth FROM #tmp os INNER JOIN ou (NOLOCK) ON os.child_id = ou.parent_id --AND os.parent_id <> ou.ou_id -- exclude circular reference WHERE os.depth_from_parent = @depth - 1 AND os.parent_id <> 1 END