Re: SQL Server 2000 - Hierarchical data - Versus - Post Relational Databases
From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 03/28/04
- Next message: Luqman: "Re: How to use VFP OLEDB Provider in openrowset ?"
- Previous message: Aaron Bertrand [MVP]: "Re: Distinguish User Objects and System objects"
- In reply to: Russell Mangel: "SQL Server 2000 - Hierarchical data - Versus - Post Relational Databases"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 28 Mar 2004 06:56:35 -0500
Currently, SQL Server doesn't handle hierarchies out of the box. My
co-author - Itzik Ben-Gan - wrote a chapter specifically to handle
hierarchies in our book "Advanced Transact-SQL for SQL Server 2000".
http://www.apress.com/book/bookDisplay.html?bID=72
That said, the next release of SQL Server - code-named Yukon - does handle
hierarchies very well. It probably comes as no surprise that Itzik wrote a
great white paper on it in MSDN:
It's a very powerful feature that lets you manage hierarchies in code
without having to store extra information within the table.
-- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinnaclepublishing.com/sql . "Russell Mangel" <russell@tymer.net> wrote in message news:u%23zx2hKFEHA.1368@TK2MSFTNGP11.phx.gbl... I have been working on a new SQL Server 2000 database system for the last three months. The database is storing hierarchical-based data from Exchange Server, it is almost complete and it seems to be performing reasonably well. However, during many phases of development, I feel like I have successfully stuffed a "round peg" in a "square hole". The SQL2000 database stores messages from Exchange Server, here are a couple things I didn't like, but had to implement: Complaint #1: I was forced to use a Link Table to relate a recipient to a message, as it is a many-to-many relationship. So for every 250,000 rows of messages there will be 1,000,000 rows in the link table, assuming our messages hold a 4 recipients per message average, and this concerns me as the link-table can grow very fast. Complaint #2 Messages are stored in Folders, and so the Folders Table represents another hierarchy, this table gets queried a lot and so I am currently testing adjacency model (ParentID for each folder), and Nested Sets. I like Joe Celko's nested set model because conceptually it matches the hierarchical representation of Folders better, but it looks like updates/deletes are even more miserable than simple adjacency model. Querying this Folders table, is not trivial, and forces SQL 2000 to do things it was not really designed to do. The questions? When dealing with hierarchal data, would any of the new post-relational databases eliminate any of my previous complaints? Like PostgreSQL, Cache (Intersystems), or Matisse? These vendors are yapping about how good their database is, but I fail to see their "Killer-App". Will SQL Server 2005, offer any relief here? Thanks An aspiring RDBMS apprentice Russell Mangel Las Vegas, NV
- Next message: Luqman: "Re: How to use VFP OLEDB Provider in openrowset ?"
- Previous message: Aaron Bertrand [MVP]: "Re: Distinguish User Objects and System objects"
- In reply to: Russell Mangel: "SQL Server 2000 - Hierarchical data - Versus - Post Relational Databases"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
