Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Server 2008 T-SQL Enhancements Part - III Expand / Collapse
Author
Message
Posted Wednesday, August 26, 2009 10:47 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, March 20, 2014 9:08 AM
Points: 64, Visits: 188
Comments posted to this topic are about the item SQL Server 2008 T-SQL Enhancements Part - III

With Thanks and Regards
Arshad Ali
Microsoft India

My Blog - http://arshadali.blogspot.com/
Post #778038
Posted Thursday, August 27, 2009 4:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 29, 2009 11:09 PM
Points: 14, Visits: 22
great article....thank you very much.....the simplicity of the language is greatly appreciated...do come up with more of such articles...thank you
Post #778139
Posted Thursday, August 27, 2009 7:17 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932
Great article! Thanks!

Little hint for .NET developers:
New .NET wrapper types SqlHierarcyId, SqlGeography, SqlGeometry are not stored in System.Data.dll but in "Microsoft.SqlServer.Types.dll" in "c:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\" directory. After adding the reference to your project you can find them in namespace "Microsoft.SqlServer.Types".

Greets
Flo



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #778267
Posted Thursday, August 27, 2009 7:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 1:58 PM
Points: 176, Visits: 252
Thanks for the article, makes me look forward to our upcoming migration from 2000 to 2008.
However, I have to ask why you would actually try to use recursion for the 2000 example? Especially with the well-known issues around nesting, why provide such a solution? When I need to do hierarchies, I use something more like this:
create procedure EmployeeHierarchy 
@EmpID int
as
declare @out table (EmpID int, EmpName varchar(255), ReportsTo int)

insert into @out
select EmpID, EmpName, ReportsTo
from Employees_2000_2005
where EmpID = @EmpID

while @@rowcount > 0
insert into @out
select EmpID, EmpName, ReportsTo
from Employees_2000_2005
where ReportsTo in (select EmpID from @out)
and EmpID not in (select EmpID from @out)

select o.EmpID, o.EmpName, e.EmpName Manager
from @out o
inner join Employees_2000_2005 e
on o.ReportsTo = e.EmpID
order by 1
go

A routine like this self-limits, so no infinite loops in cases when somebody messes up the list. No problems with recursion levels either. If the @out table gets too big, convert it to a temp table with indexes.

Or am I just missing something?

Again, thanks for the article!
Post #778277
Posted Thursday, August 27, 2009 8:56 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 3:30 PM
Points: 1,525, Visits: 1,833
Arshad Ali,
Thank you for an excellent, well-written article! (Note: the code to populate your sample table is missing the declares for @CTO, etc.)

gerald.drouin,
Clever solution!
Post #778383
Posted Thursday, August 27, 2009 9:49 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, April 14, 2014 8:04 AM
Points: 442, Visits: 471
I wasn't aware of this addition to SQL 2008 until I read your article. It's interesting but somewhat kludgey.

I played around with hierarchyid after reading this article and discovered it can be surprising easy to mess up records. Specifically, the GetReparentedValue doesn't seem to be very useful if you are moving children from one parent to another parent that already has children. It will actually assign duplicate hierarchyid's.

Here's an example of it happening.
USE [Test]
GO

/****** Object: Table [dbo].[hier_test1] Script Date: 08/27/2009 11:14:30 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[hier_test1]') AND type in (N'U'))
DROP TABLE [dbo].[hier_test1]
GO

USE [Test]
GO

/****** Object: Table [dbo].[hier_test1] Script Date: 08/27/2009 11:14:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[hier_test1](
[record_id] [int] IDENTITY(1,1) NOT NULL,
[data1] [nvarchar](50) NULL,
[data2] [nvarchar](2000) NULL,
[hier_id] [hierarchyid] NOT NULL,
[position] [nvarchar](50) NULL,
CONSTRAINT [PK_hier_test1] PRIMARY KEY CLUSTERED
(
[record_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


insert into Test.dbo.hier_test1(
data1,
data2,
hier_id
)VALUES(
'/',
'This is the root record',
hierarchyid::GetRoot()
)


exec AddHierRecord 1, '/1', 'Child of Root'
exec AddHierRecord 1, '/2', 'Child of Root'
exec AddHierRecord 1, '/3', 'Child of Root'
exec AddHierRecord 2, '/1/1', 'Child of Child 1'
exec AddHierRecord 2, '/1/2', 'Child of Child 1'
exec AddHierRecord 2, '/1/3', 'Child of Child 1'
exec AddHierRecord 3, '/2/1', 'Child of Child 2'
exec AddHierRecord 3, '/2/2', 'Child of Child 2'
exec AddHierRecord 3, '/2/3', 'Child of Child 2'
exec AddHierRecord 4, '/3/1', 'Child of Child 3'
exec AddHierRecord 4, '/3/2', 'Child of Child 3'
exec AddHierRecord 4, '/3/3', 'Child of Child 3'
exec AddHierRecord 5, '/1/1/1', 'Child of Child 1 of Child 1'
exec AddHierRecord 5, '/1/1/2', 'Child of Child 1 of Child 1'
exec AddHierRecord 5, '/1/1/3', 'Child of Child 1 of Child 1'
exec AddHierRecord 6, '/1/2/1', 'Child of Child 2 of Child 1'
exec AddHierRecord 6, '/1/2/2', 'Child of Child 2 of Child 1'
exec AddHierRecord 6, '/1/2/3', 'Child of Child 2 of Child 1'


select *, hier_id.ToString()
from hier_test1

declare @oldparent hierarchyid
declare @newparent hierarchyid
select @oldparent = hier_id
from hier_test1
where record_id = 2

select @newparent = hier_id
from hier_test1
where record_id = 3


update hier_test1
set hier_id = hier_id.GetReparentedValue(@oldparent, @newparent)
where hier_id.IsDescendantOf(@oldparent) = 1
and record_id <> 2

select *, hier_id.ToString()
from hier_test1



Look at record_id's 5 and 8 in the second set of results. Then there's the issue of the "grandchildren" records. It does attempt to move these. When it does, however, it becomes impossible to distinguish which of two parents they belong to.

I've gone through the tutorials and documentation on TechNet and they don't offer any examples of moving a branch into an already populated branch.

If anyone can tell me I'm doing something wrong I'd really like to know what it is so I can make use of this new feature.


Post #778427
Posted Thursday, August 27, 2009 10:58 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, March 20, 2014 9:08 AM
Points: 64, Visits: 188
Thanks Carla,

Yeah I missed to include declare statement, sorry for inconvenience.

Reader,
Kindly add these declare statement in script existing in first table of the article.

DECLARE @CTO INT
DECLARE @DevManager INT
DECLARE @TESTManager INT
DECLARE @DevLead1 INT
DECLARE @DevLead2 INT
DECLARE @TESTLead INT


With Thanks and Regards
Arshad Ali
Microsoft India

My Blog - http://arshadali.blogspot.com/
Post #778490
Posted Tuesday, September 01, 2009 10:14 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 9:27 AM
Points: 359, Visits: 890
I really thought I'd love the use of the hierarchyid type... but things are slipping for me.. sorry if I am hijacking this a bit, but it's relevant.

I am finding it quicker to do a flattened view using ParentId -> Id than Node.GetAncestor(1) = Node

Is there a reason?

I've got a million rows in a catalgoue table that looks like this:
CREATE TABLE [dbo].[tblHierarchy](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ParentId] [int] NULL,
[Node] [hierarchyid] NOT NULL,
[ParentNode] AS ([Node].[GetAncestor]((1))) PERSISTED,
[NodeLevel] AS ([Node].[GetLevel]()) PERSISTED,
[Code] [nvarchar](64) NOT NULL,
[TypeId] [int] NOT NULL,
[TargetId] [int] NULL,
[CreateDate] [datetime] NOT NULL,
[CreateUser] [nvarchar](32) NOT NULL,
[UpdateDate] [datetime] NOT NULL,
[UpdateUser] [nvarchar](32) NOT NULL,
[UpdateCode] [nchar](1) NOT NULL,
CONSTRAINT [tblHierarchy_PK] PRIMARY KEY NONCLUSTERED
(
[Id] ASC
) ON [PRIMARY],
CONSTRAINT [tblHierarchy_AK1] UNIQUE CLUSTERED
(
[Node] ASC
) ON [PRIMARY]
) ON [PRIMARY]

Simples... I've had lots of different indexes on, but I've stripped them off to get back to basics.

The tree I am querying has 21000 records.

If I do the following:
SELECT
[H1Id] = h1.Id
,[H1Code] = H1.Code
,[H1Type] = H1.TypeId
,[H2Grp] = H2.Id
,[H2Code] = H2.Code
,[H2Type] = H2.TypeId
,[H3Grp] = H3.Id
,[H3Code] = H3.Code
,[H3Type] = H3.TypeId
,[H4Grp] = H4.Id
,[H4Code] = H4.Code
,[H4Type] = H4.TypeId
FROM dbo.tblHierarchy H0 WITH (NOLOCK)
INNER JOIN dbo.tblCATHierarchy H1 WITH (NOLOCK)
ON H0.Id = H1.ParentId
INNER JOIN dbo.tblCATHierarchy H2 WITH (NOLOCK)
ON H1.Id = H2.ParentId
INNER JOIN dbo.tblCATHierarchy H2 WITH (NOLOCK)
ON H2.Id = H3.ParentId
INNER JOIN dbo.tblCATHierarchy H4 WITH (NOLOCK)
ON H3.Id = H4.ParentId
WHERE H0.Code = 'ROOT'
AND H1.TypeId = 1

Compared to:
SELECT
[H1Id] = h1.Id
,[H1Code] = H1.Code
,[H1Type] = H1.TypeId
,[H2Grp] = H2.Id
,[H2Code] = H2.Code
,[H2Type] = H2.TypeId
,[H3Grp] = H3.Id
,[H3Code] = H3.Code
,[H3Type] = H3.TypeId
,[H4Grp] = H4.Id
,[H4Code] = H4.Code
,[H4Type] = H4.TypeId
FROM dbo.tblHierarchy H0 WITH (NOLOCK)
INNER JOIN dbo.tblCATHierarchy H1 WITH (NOLOCK)
ON H0.Node = H1.Node.GetAncestor(1)
INNER JOIN dbo.tblCATHierarchy H2 WITH (NOLOCK)
ON H1.Node = H2.Node.GetAncestor(1)
INNER JOIN dbo.tblCATHierarchy H2 WITH (NOLOCK)
ON H2.Node = H3.Node.GetAncestor(1)
INNER JOIN dbo.tblCATHierarchy H4 WITH (NOLOCK)
ON H3.Node = H4.Node.GetAncestor(1)
WHERE H0.Code = 'ROOT'
AND H1.TypeId = 1

The first query runs shed twice as fast. Even if I substitute Node.GetAncestor(1) with my persisted computed column of ParentNode.

It's important for the dataset to be flattened for exporting to other systems. If someone reading this has better plans please reply or PM me. would be grateful as always.

I guess we may need both approaches? It's incredibly powerful, but it has it's limits?

Kev
Post #780862
Posted Wednesday, February 17, 2010 5:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 09, 2013 6:58 PM
Points: 2, Visits: 41
after playing with hierarchyid and mainly it's GetReparentedValue feature i have to say I am disappointed.

As well as the problem mentioned above by kent.kester I found that using GetReparentedValue to move a record will not also move the record's children, i thought that was the whole point, why would I want orphan records in my table?

This certainly feels like a work in progress by Microsoft and hopefully SS2008 r2 will have some improvements.



Post #867630
Posted Friday, March 19, 2010 1:29 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 10:49 PM
Points: 488, Visits: 1,286
@gerald.drouin,

While loops do offer a solution for hierarchy based solution, but CTEs are simpler, cleaner way of solving the recursive problems. If correctly used, they offer better performance than while loop based solution.
Relevant read can be
http://strictlysql.blogspot.com/2010/01/recursive-cte-vs-temp-table-performance.html


Regards,
Raj

Strictlysql.blogspot.com
Post #886124
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse