Related Discussion Connection: I have long said: About the storage and maintenance of tree-type structure data http://expert.cbs.net/expert/topicview1.asp?id=1677669 storage adoption: Tree (id) If only for storage, it is undoubtedly the most economical! But use this structure to provide some application performance forms of application performance based on slightly complex points should be quite low! Such as: Query the path of a node, etc. ! If we want to query, we can work hard when maintaining data! We take a tree structure forum as an example: Tree (ID, ParentID, Rootid, OrderID, Maxid, Indenter, Title, Content, Remark) ID: Integer post idparentid: INTEGER Ponenti ID ORDERID: INTEGER Renal ID ORDERID: Integer In the same root post, post order id maxid: integer is used to make new posted at the top Indenter Title: varchar post Title Content: varchar Remark: VARCHAR In addition to ID, ParentID's Post Corps This design will increase efficiency for the query as long as it is maintained! Please see the maintenance procedure below: - ========== ========================================= alter procedure appsp_addnew @ id integer, @ title varchar (8000) = null, @content VARCHAR (8000) = NULLAS - DECLARE @ID int - set @ id = 0if @ id = 0 Begin Insert Into Tree (ParentID, OrderID, Indenter (0, 0, 0, @ title, @ Content) - Top the post to the top: update tree set rootid = id, maxid = (select max (id) from tree) Where rootid is null endelse Begin - Adjust the same "root post", internal order: update tree set orderid = Orderid 1 where rootid = (Select rootid from tree where id = @ID) and ORDERID> (Select Orderid from tree id = @
ID) - Insert a reply post while maintaining rootid, parentid, orderid, indent, remark, title, content insert Into Tree (rootid, parentid, orderid, indent, remark, title, content) Select Rootid, @ id, orderid 1, Indent 1, Case When Remark Is Null Then Cast (PARENTID As Varchar) Else Remark '-' Cast (Parentid As Varchar) End (Parentid As Varchar) End (Parentid As Varchar) End (Parentid As Varchar) End (Parentid As Varchar) End (Parentid As Varchar) End (Parentid As Varchar) End (Parentid As Varchar) End (Parentid As Varchar) End (Parentid As Varchar) End (Parentid As Varchar) End (Parentid As Varchar) End (Parentid As Varchar) End (Parentid AS VARCHAR) End (Parentid As Varchar) End (Parentid As Varchar) End (Parentid As Varchar) End (Parentid As Varchar) End (Parentid As Varchar) End (@ Title, 'Re:' Title) Where id = @ id - Top the post to the top: update tree set maxid = (select max (id) from tree) where rootid = (select rootid from tree where id = @ id) end - ====== ================================== This program is used for 1. Add new post: Appspsp_addnew 0, 'first A problem ',' Earth is round? '2. Reply to post: Appspus_addnew 1,' RE: The first question ',' Earth is round! 'This is just simple query: select *, remove ' - ' Cast (Parentid As Varchar) ' - ' Cast (ID As Varchar), Space (Indent) ' [ '
From TreeOrder by maxid desc, ORDERID can efficiently implement a list of posts and their clues, level, etc. Although some workloads have been added! - Related DDL scripts: create table [tre] ([id] [int] Identity 1, 1) NOT NULL, [PARENTID] [INT] null, [otid] [int] null, [Maxid] [INT] null, [Indenter] NULL, [Indenter] NULL, [TITLE] [varchar] (50), [content] [varchar] (200), [transark] [varchar] (250), constraint [pk_tree] Primary Key Clustered ([ID] related discussion connection: I have long said that I will talk about: About Storage and maintenance of tree-type structure data http://expert.cbs.net/expert/topicview1.asp?id=1677669 Storage of Tree Structure Data Adoption: Tree (ID, ParentID, Remark) If only for storage, It is undoubtedly the most economical! But use this structure to provide some application performance form efficiency based on a slightly complex point, should be quite low! Such as: Query the path of a node! If you want to query, we can maintain data I don't have a job! We take a tree structure forum as an example: Tree (ID, ParentID, rootid, orderid, maxid, indent, title, content, remark) ID: integer post IDParentId: Integer Pupire ID rootid: integer ID ORDERID: Integer In the same root post, post order ID maxid: integer is used to make new stickers at the top Indenter Title: varchar post title Content: varchar post content Remark: varchar in addition to ID, ParentID The design such as the child is as long as it is maintained for each field to show the efficiency of the query! Please see the maintenance procedure below: - ====================== ===================== alter Procedure appsp_addnew @ id integer, @ title varchar (8000) = null, @ Content varchar (8000) = NULLAS - DECLARE @ID INT - set @ id = 0if @ id = 0 Begin Insert Into Tree (ParentID, OrderID, Indent , Title, Content) Values (0, 0, @ title, @ Content) - Top the post to the top: Update Tree set rootid = id, maxid = (select max (id) from tree) Where rootid is null endelse Begin - Adjust the same "root post", internal order of posts: update tree set orderid = OrderID 1 where rootid =
(Select rootid from tree where id = @ID) And ORDERID> (Select Orderid from Tree Where id = @ID) - Insert a reply post while maintaining rootid, parentid, orderid, indent, remark, title, content insert Into Tree (Rootid, Parentid, Orderid, Indenter, Remark, Title, Content) Select Rootid, @ ID, ORDERID 1, Indent 1, Case When Remark Is Null Then Cast (Parentid As Varchar) Else Remark '-' Cast Parentid as varchar) End, isnull (@ title, 'RE:' TITLE), @ Content from tree where id = @ id - Top the post to the top: Update Tree set maxid = (SELECT MAX (ID) from Tree) WHERE rootid = (select rootid from tree where id = @ id) end - =============================== ========= This program is used for 1. Add new stickers: Appspus_addnew 0, 'first problem', 'Earth is circle?' 2. Reply to post: Appspus_addnew 1, 'Re: first A problem ',' Earth is round! 'This, just simple query: SELECT *, REMARK ' - ' CAST (PARENTID AS VARCHAR) ' - CAST (ID as varchar), Space (Indent) '['
