use tempdb

create table pages
(
	pageID int identity,
	ordinal int
)

insert pages ( ordinal ) values ( 1 )
insert pages ( ordinal ) values ( 2 )

declare @Ordinal int
select @Ordinal = max(Ordinal) + 1 from	Pages

insert pages ( ordinal ) values ( @Ordinal )

select * from pages order by ordinal

--existing table:
create table OrdinalFactory
	(Ordinal int identity(1,1)
	,active bit)

--in your insert SP:

declare @Ordinal int
insert OrdinalFactory (active) values(1)
Select @Ordinal = scope_identity()

insert pages ( ordinal ) values ( @Ordinal )

quote:

---

So after the table has been "reordered" by updating the ordinal column, and new records are added with incrementing ordinal values, aren't those new records out of order (until the next re-ordering)?

---

declare @Ordinal int
select @Ordinal = max(Ordinal) from	Pages

insert pages ( ordinal )
select
	max(Ordinal)+1
from
	Pages
having
	max(Ordinal) = @Ordinal

quote:

---

Originally posted by X-Factor

Take the following..

use tempdb

create table pages
(
	pageID int identity,
	ordinal int
)

insert pages ( ordinal ) values ( 1 )
insert pages ( ordinal ) values ( 2 )

declare @Ordinal int
select @Ordinal = max(Ordinal) + 1 from	Pages

insert pages ( ordinal ) values ( @Ordinal )

select * from pages order by ordinal

The value of the ordinal is used to order the pages and the pages have to have a unique ordinal value else some code which is used to change the ordinal will not work.

So I'm using the 'max' function to work out what the next value will be for a new page's position and, as you can see if you run the code, it works.

However, what if a concurrent transaction, B, is running at the same time as transacion, A, and uses max just after A has but before A has done its insert? In this situation, A and B will use the same ordinal value for the insert.

So the question is, how can I lock the max ordinal value after its been read until the end of a transaction?

Cheers,

XF

---

quote:

---

well those kind of things usually get solved preety nicely with an extra status column.
so until the transaction is finished put the status to say 2 and when it's finished put it to 3 (or whatever you like).

---

quote:

---

Ok, how about this. Since the ordinal column is just used for ordering the display, do away with Unique constraints and contention issues all together. Just allow new records to be instered with a NULL ordinal value. When you select out of that table, order by isNull(Ordinal,100000000). Since the newly inserted records would just be ordered randomly anyway it shouldn't matter.

---

quote:

---

You could do it this way:

declare @Ordinal int
select @Ordinal = max(Ordinal) from Pages

insert pages ( ordinal )
select
max(Ordinal)+1
from
Pages
having
max(Ordinal) = @Ordinal

---

quote:

---

Or I could just put a real unique constraint on the ordinal column and handle a clash. I'd handle it by having it try the insert up to a 1000 times and then throw an exception if it still hadn't worked.

I have not put a unique constraint on yet because when you swap values, the first update would result in the page x's and page y's ordinal value being the same. But I figure I could get around this by temporarily negating the value being swapped for one of the pages.

---

quote:

---

Originally posted by X-Factor

Ok. Then I'd try calling the sp again from the app.

---