Index for Database Driven Menu

Question

Index for Database Driven Menu

james00harper

SSC Enthusiast

Points: 171
More actions
March 3, 2007 at 2:49 pm

#172618

Hi,
I have a database driven menu behind a web app that I am building. The database has been created in SQL Server 2005. I'm having trouble creating an index for my menu structure. Below is the script to create my menu and populate it with some sample data.
CREATE TABLE [Menu] (
[MenuID] [int]
IDENTITY (1, 1) NOT NULL ,
[Text] [varchar]
(50) ,
[Description] [varchar]
(255) ,
[ParentID] [int]
NULL ,
CONSTRAINT [PK_Menu] PRIMARY KEY CLUSTERED
(
[MenuID]
) ON [PRIMARY]
)
ON [PRIMARY]
GO
INSERT
INTO MENU
Select
'Product','A List of Products', NULL
UNION
ALL Select 'Applications','Appliations',NULL
UNION
ALL Select 'Document','Documentation', NULL
UNION
ALL Select 'Support','Support', NULL
UNION
ALL Select 'Download','Download', NULL
UNION
ALL Select 'Background','ProductBackground', 1
UNION
ALL Select 'Details','Product Details', 1
UNION
ALL Select 'Mobile Device','Mobile DeviceApplications', 2
UNION
ALL Select 'Portal','Portal Applications',2
UNION
ALL Select 'Web Applicaitons','WebApplications', 2
UNION
ALL Select 'Demo','Demo Applicaitons', 2
UNION
ALL Select 'Performance Tests','ApplicationPerformance Tests', 2
UNION
ALL Select 'Tutorials','TutorialDocumentation', 3
UNION
ALL Select 'Programmers','ProgrammDocumentation', 3
UNION
ALL Select 'FAQ','Frequently AskedQuestions', 4
UNION
ALL Select 'Forum','Forum', 4
UNION
ALL Select 'Contact Us','Contact Us', 4
UNION
ALL Select 'InternetRestrictions','Internet Restrictions', 6
UNION
ALL Select 'Speed Solution','Speed Solutions',6
UNION
ALL Select 'Application Center Test','Application Center Test Results', 12
UNION
ALL Select 'Modem Results','Modem Results',12
GO

I want to create a query to generate the index whereby the MenuId is self-joined to the ParentId. The index needs to look something like this.
1 Product
1.1 Product Background
1.2 Product Details
2 Applications
...
... and so on
I've managed to get this far, but it isn't that flexible as it presumes that the menu structure will have a maximum of 3 levels. I need to generate something more dynamic
SELECT
MMM.MenuId ParentParentMenuId, MM.MenuId ParentMenuId, M.MenuId, M.Text
FROM
Menu M
LEFT
JOIN Menu MM ON M.ParentId = MM.MenuId
LEFT
JOIN Menu MMM ON MM.ParentId = MMM.MenuId
ORDER
BY ParentParentMenuId, ParentMenuId, M.MenuId

I'd be really grateful if anyone can help me out with a better solution. thanx

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Look in books online for recursive common table expressions. They're exactly what you're looking for. Here's quick example. Should give you an idea.

WITH

MenuCTE(ParentID, MenuID, MenuText, MenuOrder) AS

(

SELECT NULL AS ParentID, MenuID, [Text] AS MenuText, CAST(row_number() OVER (ORDER BY MenuID) AS VARCHAR(10)) AS MenuOrder

FROM Menu

WHERE ParentID IS NULL

UNION ALL

SELECT Menu.ParentID, Menu.MenuID, Menu.[Text] AS MenuText,

CAST(MenuCTE.MenuOrder + '.' + CAST(row_number() OVER (PARTITION BY (Menu.ParentID) ORDER BY menu.MenuID) AS VARCHAR(3)) AS VARCHAR(10)) AS MenuOrder

FROM Menu INNER JOIN MenuCTE ON Menu.ParentID = MenuCTE.MenuID

)

SELECT MenuOrder, MenuText FROM MenuCTE ORDER BY MenuOrder

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

james00harper SSC Enthusiast Points: 171 More actions · Answer 2

Thanks very much for your reply, I'll give that a go. My boss in work always tells me how good Books Online is and I never bother to look.

james00harper SSC Enthusiast Points: 171 More actions · Answer 3

Just tried it. Oh my god, this works amazingly well. I've brielfy heard of CTEs before, but never had a good use for them until now.

I'm impressed!!!

james00harper SSC Enthusiast Points: 171 More actions · Answer 4

Just tried it. Oh my god, this works amazingly well. I've brielfy heard of CTEs before, but never had a good use for them until now.

I'm impressed!!!

james00harper SSC Enthusiast Points: 171 More actions · Answer 5

Just tried it. Oh my god, this works amazingly well. I've brielfy heard of CTEs before, but never had a good use for them until now.

I'm impressed!!!

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 6

Is there a need to create an index on the menu table? It should not be a lot of records in this table?

Regards,Yelena Varsha

james00harper SSC Enthusiast Points: 171 More actions · Answer 7

The scenario is purely hypothetical. I have a document library I am building with thousands of records and the index would be very useful to be presented on a webpage

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 8

Pleasure. This is one of the first real uses I've seen for them too.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass