Monday, June 23, 2003

Source: Keith's Weblog; 6/23/2003; 7:39:11 AM Trees in SQL. It turns out there's another way to store hierarchical data in SQL that I didn't know existed. Basically, rather than storing the "parent" for each record (with the root having a NULL parent), it turns out you can store a tree by storing "left" and "right" values for each element of the tree, where left and right are numbers that would be assigned by a preorder traversal of the tree. Via Simon, check out this article from SitePoint.com by Gijs Van Tulder: Storing Hierarchical Data in a Database, and via a comment on Simon's blog, check out database guru Joe Celko's explanation of the same thing. The first article has more of a tutorial flavor, and has nice diagrams, but Celko's article goes a little more in depth. Also, Gijs only used a binary tree in his example, so I wasn't sure if it'd work for more-than-binary trees, but Celko's example shows that it can. The only downside to this technique is that it's more of a pain to insert new rows or delete existing ones, but the benefits seem to outweigh the downsides, especially for large or heavily nested recordsets. This is really a great technique, and I'm glad that it's now in my repetoire. I didn't realize this was possible. Thanks for finding this, Simon [Keith's Weblog] 7:45:02 AM    trackback []     Articulate []

Enum Extended Stored Procedures Digging a little deeper this week into extended stored procedures, we find the xp_enum procedures. These procedures require no parameters but provide a handy bridge to the operating system and is extremely helpful for developers. Xp_enumgroups will display all NT groups that are currently on your SQL Server. It requires no parameters are the data is loaded into a varchar (255) format (perfect for loading into a temp table for usage). Xp_enumdsn will display all the DSNs installed on the SQL Server, not the client workstation. Xp_enum_activescriptengines is also a nice extended stored procedure that will display all the scripting engines installed on your server currently. SQL Server uses this to provide you a list of scripting engines in DTS Designer. Keep in mind that in any extended stored procedure, SQL Server will use the security for whichever account is starting SQL Server. If security is a concern with these extended stored procedures, make sure you lock them down. Brian Knight SQLServerCentral.com Co-founder 7:29:13 AM    trackback []     Articulate []