Returning Menu Items in Hierarchical Order

EDITOR'S NOTE: Send your T-SQL questions to SQL Server MVP Itzik Ben-Gan at [email protected].

In a SQL Server 2000 database, I have a table of menu items containing the menuNumber and menuText text fields. The records in this table are in no particular order. How can I write a SELECT statement that returns the menu items in the order that Table 1 shows? I know that a sort on the menuNumber text field isn't sufficient because the sort would be character-based. For example, the sort would place 11 lower than 2 .

To write a query that returns the menu items in the order you want, you can create a user-defined function (UDF) that accepts a menu item, then prefixes each part in the item with zeros to make all parts the same length. When all parts are the same length, the character-based sort behaves the same as a numeric sort (e.g., the sort compares the ones digit with the other value's ones digit). The CREATE FUNCTION statement in Listing 1 creates the dbo.fn_stuffzeros() UDF. When you use this UDF in the SELECT statement

SELECT dbo.fn_stuffzeros('1.23.456.7890')

you produce the following output:

0000000001.0000000023.0000000456.0000007890

After creating and populating table T1 by running the code in Listing 2, you can achieve the desired results by using the UDF in an ORDER BY clause, as the following query shows:

SELECT *FROM T1ORDER BY dbo.fn_stuffzeros(menuNumber)

Table 2 shows this query's output.

If performance isn't satisfactory when you use this UDF, you can add the UDF to the table as a computed column, then create a clustered index on that column. Because the clustered index arranges the table's data in the computed column's order and because the computations are precalculated, the table is in an optimal state for the specified sorting requirements. The following code adds the computed column to the table and creates a clustered index on it:

ALTER TABLE T1   ADD sort_col AS dbo.fn_stuffzeros(menuNumber) UNIQUE CLUSTERED

Because you added the clustered index on the computed column, the following query should perform well:

SELECT menuNumber, menuTextFROM T1ORDER BY sort_col

If you want to indent the menuText values to illustrate the menu item's nesting level, you can add spaces to match the number of periods in the menuNumber value, multiplied by a certain factor. To count the number of periods in the menuNumber value, use the T-SQL expression

LEN(menuNumber) - LEN(REPLACE(menuNumber, '.', ''))

The following query returns the proper indentation:

SELECT  SPACE((LEN(menuNumber) - LEN(REPLACE(menuNumber, '.', '')) - 1) * 2)  + menuText AS menuTextFROM T1ORDER BY sort_col