.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

clr and using hierarchyid

Posted By:      Posted Date: October 06, 2010    Points: 0   Category :Sql Server
Just installed slq server 2008 express on my local machine (vista home premium sp2) have latest .net framework installed.  I cannot get a query in tsql to recognize any of the methods associated with hierarchyid such as GetRoot(), GetDescendent(), etc.  What do I need to do to be able to access these functions?  I have executed sp_configure to enable clr.

View Complete Post

More Related Resource Links

Menu based on Hierarchyid

I'm trying to base a Menu on the HierarchyID type.The problem is that a Menu will only accept an XML file or a Sitemap as its data source.Therefore, my question is: has anyone worked out a conversion of HierarchyID->XML or HierarchyID->Sitemap? Unfortunately, it seems that HierarchyID is a vastly underused data type and whenever I find anything on the subject on the Internet its used for something else, isn't exactly adaptable to Web Applications or simply doesn't work.

hierarchyid datatype in sql 2008

hi everyone, I wanted to know what exactly GetDescendent(null,null)method represent.what 2 parameters(null,null)takes it. How it works? Thank you

Need help selecting items using HierarchyID

Hi, I'm using HierarchyID in my ProductCategories table. I then reference the CategoryID in the Products table to determine under what category the product is to be listed. The script for the ProductCategories table is at the end of this message. Here's what I want to do: I want to select products from specified category as well as its child categories. This is especially useful if the user ended up in a parent category that doesn't have any products of its own. This generally happens if the category is only being used as a parent container e.g. Electronics. So if the user ends up in Electronics category, I want to display products from child categories of electronics e.g. MP3 players, digital cameras, etc. Could someone help me with the SQL for this SELECT statement? Here's the script for ProductCategories table: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[ProductCategories]( [CategoryID] [int] IDENTITY(1,1) NOT NULL, [Category] [varchar](100) NOT NULL, [CategoryHierarchy] [hierarchyid] NOT NULL, [StatusID] [tinyint] NOT NULL, CONSTRAINT [PK_ProductCategories] PRIMARY KEY CLUSTERED ( [CategoryID] 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 SET ANSI_PADDING OFF GO EXEC sys.sp_addextendedproperty @name=

hierarchyid - any thoughts on grouping children across multiple parents


Hey there,

I have just been playing around with the hierarchyid examples on MSDN and came across this issue in my experiments and wondered whether anyone has any thoughts on creating some sort of "grouping" of child nodes across different parents:

If I have the following two tree items:



The two DVD nodes aren't associated by anything other than a common title which makes it difficult to search for all DVDs so you could change the structure to this:


but then the problem rears it's ugly head in the same fashion when you add more data:


Now there are two Music and Movie nodes that are loosely associated by a common title.


I am not suggesting that the hierarchyid should be able to represent data like this and it is still useful for strictly hierarchical models I just wondered whether anyone had come up with a solution that overcomes this problem. My initial thoughts are that you could

Performance of CLR HierarchyID related functions


We are seeing some consistently odd behavior from the query engine when running out of the box CLR fucntions against a HierarchyID based tree. We are indexed for width and breadth similar to the technet examples for bills of lading. What we see is that the query engine is not using the indexes resulting in very high cpu cycles and memory pressure. Even more puzzling, is the the plan and dmvs showing a "missing" index that is an exact match for the indexes that are there:






CREATE TABLE [dbo].[tbl_xx_consultantHierarchyPeriodSummary](
	[nodeID] [hierarchyid] NOT NULL,
	<various attribute columns>
	[treeLevel] AS ([nodeID].[GetLevel]()),
	[parentNodeID] AS ([nodeID].[GetAncestor]((1))) PERSISTED,
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend