This is a micro-post to show you how easy it is to search through all objects of a SQL Server Database for a specific string. Say that you want to rename a function and you want to know how many procedures are using this function – instead of opening all procedures trying to find the matching string, this article will give you a solution that simplifies the matter ten fold!
Archive for May, 2012
HIERARCHICAL structures are everywhere! Your family structure, Ancestral history, your phone and address books, corporate structures – all resemble a well defined hierarchy.
The simplest representation of hierarchies is in the form of an upside down ‘Tree‘. The ‘root‘ of the tree is where it all begins, the ‘root’ expands into multiple ‘tree nodes‘, these ‘tree nodes’ may contain other tree nodes; and at the end of each ‘tree node’ is a ‘leaf node‘.. for more information on data tree structures please refer to this article: Tree (data structure)
In software development, no doubt you would have come across tree structures, and specifically hierarchical data structures, in your career. One pitfall that you would have experienced is that computing the entire tree structure on the fly for any system is a costly process. As the saying goes ‘..the apple never falls far from the tree’, and thus even computing the structure of a single tree node can be just as costly.
This is where a special data type in SQL comes to play: the hierarchyid type! In this article I will explain what the herierachyid type is, and provide examples on how you can use this in your database design for a clean, succinct data structure, and how you will benefit from the massive performance gains by doing so..