Shulou(Shulou.com)05/31 Report--

This article introduces what hierarchical data types there are in SQL Server 2008. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

What are the native hierarchical data types of SQLServer2008

If we are in SQLServer2000, we have to use recursion when we need to read hierarchical data. In SQLserver2005, we can use CTE, of course, good data structure design can prevent you from using CTE, see here: http://www.cnblogs.com/downmoon/archive/2009/10/23/1588405.html.

If you are a novice database designer, you can use the new native hierarchical data hierarchyid in sqlserver2008.

For a detailed description of it, see MSDN:

Http://msdn.microsoft.com/zh-cn/library/bb677173%28v=sql.100%29.aspx

Http://64.4.11.252/zh-cn/library/bb677173.aspx

Http://msdn.microsoft.com/en-us/magazine/cc794278.aspx#id0090037

It should be noted that it can be transformed from traditional data tables. But it is not possible to convert to and from XML.

What are the native hierarchical data types of SQLServer2008

Let's look at an example:

IfOBJECT_ID ('[Category_hierarchyid]') isnotnulldroptable [Category _ hierarchyid] go-- create table CREATETABLE [Category _ hierarchyid] (Category _ hierarchyid), 'vegetable' unionallselect'/1/',' root vegetables', 'vegetable' unionallselect'/1/',' root vegetables', 'vegetables' unionallselect'/2/',' leafy vegetables', 'vegetables' unionallselect'/1/',' root vegetables', 'vegetables' unionallselect'/2/',' leafy vegetables' 'vegetable 'unionallselect'/3/',' eggplant fruit', 'vegetable' unionallselect'/4/',' cabbage', 'vegetable' unionallselect'/1/1/',' radish', 'vegetable' unionallselect'/1/1/',' carrot', 'vegetable' unionallselect'/4/2/',' cabbage', 'vegetable' unionallselect'/2/4/',' cauliflower', 'vegetable' unionallselect'/3/3/',' eggplant' 'vegetable 'unionallselect'/3/3/',' tomato', 'vegetable' unionALLselect'/1/1/5/',' white radish', 'vegetable' go

Native hierarchical data in SQLserver2008: hierarchyid

-- View all categories

SELECT* From [Category _ hierarchyid] / * H_IDC_IDC_NameTitle0x1000 vegetables 0x581001 root vegetables 0x681002 leafy vegetables 0x781003 eggplant fruits vegetables 0x841004 cabbage vegetables 0x5AC01005 carrots vegetables 0x85A01007 cabbage vegetables 0x6C201008 cauliflower vegetables 0x7BC01009 eggplant vegetables 0x7BC01010 tomato vegetables 0x5AE31011 turnip vegetables * /

-- View all categories and levels

SELECT*,H_ID.GetLevel () ASLevelFrom [Category _ hierarchyid] / * H_IDC_IDC_NameTitleLevel0x1000 vegetables 00x581001 root vegetables 10x681002 leafy vegetables 10x781003 eggplant fruits vegetables 10x841004 turnip vegetables 20x5AC01006 carrots vegetables 20x85A01007 cabbage vegetables 20x6C201008 cauliflower vegetables 20x7BC01009 eggplant vegetables 20x7BC01010 tomato vegetables 20x5AE31011 white radish vegetables

-- View root vegetables and their subcategories

DECLARE@ParenthierarchyidSELECT@Parent=H_ IDFROM [Category _ hierarchyid] WHEREC_ID=1001----PRINT@Parent.ToString () SELECT*,H_ID.GetLevel () ASLevelFROM [Category _ hierarchyid] WHEREH_ID.IsDescendantOf (@ Parent) = 1/*H_IDC_IDC_NameTitleLevel0x581001 root vegetables 10x5AC01005 radish vegetables 20x5AC01006 carrot vegetables 20x5AE31011 white radish vegetables

-- View carrots and all their parent categories

DECLARE@SonhierarchyidSELECT@Son=H_ IDFROM [Category _ hierarchyid] WHEREC_ID=1006SELECT*,H_ID.GetLevel () ASLevelFROM [Category _ hierarchyid] WHERE@Son.IsDescendantOf (H_ID) = 1/*H_IDC_IDC_NameTitleLevel0x1000 vegetables 00x581001 root vegetables 10x5AC01005 radish vegetables 20x5AC01006 carrot vegetables 2cm /.

On which hierarchical data types in SQL Server 2008 are shared here, I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.