INSERT that automatically increments sub sequences and generates "hierarchical" string, keeping in mind concurrent invocations

Question

INSERT that automatically increments sub sequences and generates "hierarchical" string, keeping in mind concurrent invocations

boeledi

Ten Centuries

Points: 1151
More actions
November 19, 2013 at 8:42 am

#300285

Hi,
In order to write a post system that accepts multi-level answers, I am looking for an efficient "INSERT" statement that would answer the following...
Table structure I was considering might look like:
post_id big integer autoincrement
parent_post_id big integer
sequence_nr big integer
sequence_path varchar(256)
message nvarchar(MAX)
explanation:
parent_post_id refers to the post_id the message is answering to
sequence_nr children sequence
sequence_path string which should be generated at time of inserting the new record and based on both "parent".sequence_path concatenated with "new record".sequence_nr
In order to illustrate this, let's take this example
An initial post would lead to having this record:
post_id 1
parent_post_id NULL
sequence_nr 1
sequence_path '0001'
message 'my initial post'
then someone answers to this post, this gives
post_id 2
parent_post_id 1
sequence_nr 1 -- since this is the first child
sequence_path '001.001'
message 'my answer to post 1'
if I have another answer to the same post, I obtain
post_id 3
parent_post_id 1
sequence_nr 2 -- since this is the second child
sequence_path '001.002'
message 'my second answer to post 1'
by extension, if I am answering a post which answers another post.... (let's say level 5)
post_id 25
parent_post_id 22
sequence_nr 1 -- since this is the first child
sequence_path '001.003.002.002.001'
message 'my answer to several answers'
So my problem is "How do I build such INSERT statement that automatically computes the next sequence_nr and generates the sequence_path accordingly", taking into consideration the fact several messages could be "posted" almost at the same time (potential synchronization issue...)?
Many thanks in advance for your help.

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

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Save yourself months of anguish and use the hierarchyid datatype. This is essentially what you are trying to recreate on your own.

http://technet.microsoft.com/en-us/library/bb677290.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

boeledi Ten Centuries Points: 1151 More actions · Answer 2

Thank you very much.

I just had a look at some sample and came across the following page: [/url]

If I look at the code sample (4th bullet point), I see:

--Insert Child (Mary)

DECLARE @Mgr hierarchyid

DECLARE @LastEmp hierarchyid

SELECT @Mgr = [HierarchyID] FROM hierarchies WHERE employeename = 'Nick'

SELECT @LastEmp = Max([HierarchyID].ToString()) FROM hierarchies WHERE [HierarchyID].GetAncestor(1) = @Mgr

INSERT INTO hierarchies ([HierarchyID], EmployeeID, EmployeeName, Title)

VALUES (@Mgr.GetDescendant(@LastEmp, NULL), 3, 'Mary', 'General Manager') ;

The question I now have is the following:

If I am considering potential concurrent calls to the stored procedure that will insert the posts, with the code here above, wouldn't there be any issue with the fact it is first fetching @LastEmp, then insert the data.

If 2 calls to the stored procedure are made at the same time, @LastEmp might be the same for both calls.

How could I prevent this from happening?

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Something like this?

begin try

begin transaction

--Insert Child (Mary)

DECLARE @Mgr hierarchyid

DECLARE @LastEmp hierarchyid

SELECT @Mgr = [HierarchyID] FROM hierarchies WHERE employeename = 'Nick'

SELECT @LastEmp = Max([HierarchyID].ToString()) FROM hierarchies WHERE [HierarchyID].GetAncestor(1) = @Mgr

INSERT INTO hierarchies ([HierarchyID], EmployeeID, EmployeeName, Title)

VALUES (@Mgr.GetDescendant(@LastEmp, NULL), 3, 'Mary', 'General Manager') ;

commit transaction

end try

begin catch

--Maybe return/raise an error?

rollback transaction

end catch

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

boeledi Ten Centuries Points: 1151 More actions · Answer 4

Am I wrong but we need to make the assumption that SQL Server ensures some kind of unicity constraints.

Therefore, in case of error, I will need to test the error type and loop until the insertion is successful ?

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

boeledi (11/19/2013)
Am I wrong but we need to make the assumption that SQL Server ensures some kind of unicity constraints.
Therefore, in case of error, I will need to test the error type and loop until the insertion is successful ?

Yes sql server has unique constraints. Not sure what you mean about looping until the insertion is successful. If an insert doesn't work, trying it again isn't going to make it work. They fail because something in the insert violates a rule (constraints, datalength, missing columns, etc)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 6

Sean Lange (11/19/2013)
Save yourself months of anguish and use the hierarchyid datatype. This is essentially what you are trying to recreate on your own.
http://technet.microsoft.com/en-us/library/bb677290.aspx

BWAAA-HAAAA!!!! Save yourself continuous anguish and store it as an Adjacency List and build Nested Sets on the fly. 😛

http://www.sqlservercentral.com/articles/Hierarchy/94040/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

Jeff Moden (11/19/2013)
Sean Lange (11/19/2013)
Save yourself months of anguish and use the hierarchyid datatype. This is essentially what you are trying to recreate on your own.
http://technet.microsoft.com/en-us/library/bb677290.aspx
BWAAA-HAAAA!!!! Save yourself continuous anguish and store it as an Adjacency List and build Nested Sets on the fly. 😛
http://www.sqlservercentral.com/articles/Hierarchy/94040/

That would be even better but wasn't sure how to maintain the nesting like the OP stated they wanted.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 8

Sean Lange (11/19/2013)
Jeff Moden (11/19/2013)
Sean Lange (11/19/2013)
Save yourself months of anguish and use the hierarchyid datatype. This is essentially what you are trying to recreate on your own.
http://technet.microsoft.com/en-us/library/bb677290.aspx
BWAAA-HAAAA!!!! Save yourself continuous anguish and store it as an Adjacency List and build Nested Sets on the fly. 😛
http://www.sqlservercentral.com/articles/Hierarchy/94040/
That would be even better but wasn't sure how to maintain the nesting like the OP stated they wanted.

An Adjacency List hierarchy is easy for humans to understand. That also makes it easy to fix when something goes wrong (and things WILL go wrong :-P). The code from that article will rebuild the entire Nested Set structure on a million node Adjacency List in 54 seconds. If 54 seconds is too long a delay (it takes days using a convention push stack), then build to an alternating pair of tables and do a synonym rebuild (<1ms?) to point to the most up-to-date table. Virtually, no downtime.

I will admit that adding a row to a HierarchyID table is a whole lot faster than 54 seconds but that's just for one row and it doesn't take into consideration that most people have a hell of a time understanding the positional notation of HierarchyIDs because each node has to be aware of more than one node instead of each node only being aware of its parent node.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)