I'm not aware how deep my tree will be. So I think the NSM is fit for me, reading some docs. In sql, this model suppose I'm using an integer value as primary key. I thought to create a twin table only to store the ints (PK,left,righ) connected by a relation one-to-one with the real table. Things are complicating and it is a waste of space disk, especially when the server is not mine and I have to pay each megabyte. Help!!
Excellent! Fabolous!! Thanks Macka and Bill, I could skip reading a whole book, for now. Celko is a future order on Amazon. ;-)
It doesn't matter what type your primary key is as the left/right values will still be integers. eg.
CREATE TABLE [dbo].[Demo]( [ID] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_Demo_ID] DEFAULT (newid()), [Name] [varchar](50) NOT NULL, [Lft] [int] NOT NULL, [Rgt] [int] NOT NULL, CONSTRAINT [PK_Demo] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
--add some test data
INSERT INTO demo(name,lft,rgt) SELECT 'node1',1,6 UNION SELECT 'node2a',2,3 UNION SELECT 'node2b',4,5
--check it works
SELECT * FROM demo WHERE lft>=2 ORDER BY lft