Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server 2008 T-SQL Enhancements Part - III


SQL Server 2008 T-SQL Enhancements Part - III

Author
Message
Arshad Ali-556241
Arshad Ali-556241
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 194
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/
sanjeet.uchil
sanjeet.uchil
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
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
Florian Reischl
Florian Reischl
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1943 Visits: 3934
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
gerald.drouin
gerald.drouin
SSC-Enthusiastic
SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)

Group: General Forum Members
Points: 176 Visits: 283
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!
Carla Wilson-484785
Carla Wilson-484785
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1715 Visits: 1949
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!
LerxtDBA
LerxtDBA
SSC-Addicted
SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)

Group: General Forum Members
Points: 466 Visits: 604
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.
Arshad Ali-556241
Arshad Ali-556241
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 194
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/
Fozzie
Fozzie
Old Hand
Old Hand (387 reputation)Old Hand (387 reputation)Old Hand (387 reputation)Old Hand (387 reputation)Old Hand (387 reputation)Old Hand (387 reputation)Old Hand (387 reputation)Old Hand (387 reputation)

Group: General Forum Members
Points: 387 Visits: 1172
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? Blink

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
paolosaurus
paolosaurus
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 67
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.



arr.nagaraj
arr.nagaraj
Mr or Mrs. 500
Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)

Group: General Forum Members
Points: 558 Visits: 1588
@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

http://Strictlysql.blogspot.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search