MSDN Magazine - September 2008 - (Page 58)

HIERARCHY ID Model Your Data Hierarchies With SQL Server 2008 Kent Tegels The manufacturing system behind automobiles; the organization of a country into states, counties, cities, and postal codes; the description of a home entertainment system—what do these things have in common? The simple answer is that each de scribes a hierarchy. SQL Server® 2008 supports a new data type, HierarchyID, that helps solve some of the problems in modeling and querying hier archical information. I will introduce you to this data type by dis cussing a pattern commonly used in manufacturing known as bill of materials (BOM), or bills. Starting with a brief discussion This article is based on a prerelease version of SQL Server 2008 RC0. All information herein is subject to change. of BOMs, I will illustrate how this kind of data can be modeled. I will also present an implementation of this model in SQL Server 2005. Then I will show you how the HierarchyID data type can be used to implement the model in SQL Server 2008. Hierarchical Data This article discusses: • Modeling hierarchical data • Creating a bill of materials systemt • Moving to HierarchyID • Testing the system Technologies discussed: SQL Server 2008 Code download available at: msdn.microsoft.com/magazine/cc135911 58 msdn magazine Automobiles are amalgamations of many components, such as engines, drivetrains, electronics, and steering. In the United States, our geographic territories are divided into states and are then sub divided into jurisdictions called counties. Counties are then fur ther subdivided in different ways by different agencies. The United States Census Bureau, for example, composes them from Census Tract Areas. The U.S. Postal Service routes mail delivery by Zone Improvement Plan (ZIP) codes. Geographic information systems (GIS) may aggregate census tracts and ZIP codes together to pro vide users with a familiar spatial reference for an area. A recent trip to a local electronics store to evaluate a replacement home entertainment system pointed to a similar sort of hierarchi cal system—all the combinations of possible components and op tions left my head spinning! I wondered how such systems could be modeled and implemented in a database system. The relationship between an automobile and its engine represents a hierarchy: the automobile contains the engine. The relationship is the same for the drivetrain, the electronics, and the steering. The relationship is containment. A similar hierarchy can be observed http://msdn.microsoft.com/magazine/cc135911

Table of Contents Feed for the Digital Edition of MSDN Magazine - September 2008

MSDN Magazine - September 2008 Contents Toolbox CLR Inside Out Data Points Advanced Basics Office Space Cutting Edge Hierarchy ID New Features for Microsoft SQL Server 2008 Prism Data Services Advanced WPF Test Run Security Briefs Foundations { End Bracket }

MSDN Magazine - September 2008

For optimal viewing of this digital publication, please enable JavaScript and then refresh the page. If you would like to try to load the digital publication without using Flash Player detection, please click here.