In my table the PK is ORDER_NUMBER,LINE_NUMBER. I have routine that will insert a line in between other lines. So if the user wants to insert a new line 10 the below will run. I am curious as to why this works.
set LINE_NUMBER = LINE_NUMBER + 1
where ORDER_NUMBER=1000 and LINE_NUMBER >= 10
How does SQL number to go to the last line and increment that number and then work backwards? If it did not do that then there would be a PK duplicate record violation. Is there a better more proper way to accomplish this? In the sp I pass down the value of order and line numbers.