Creating a rule to ensure an ordered sequence

[GSXR179]

Hi,

I'm trying to add a rule in a MS SQL 2000 database that will ensure a set order in a table.

I have a table that has a sequence column, I would like to prevent any gaps or duplicate entries into this column.

For example. If I have five rows and they are ordered 1,2,3,4,5 and I remove number 3 and place it after 5, I want to make sure that I don't end up with 1,2,4,5,6

Thanks in advance for your response.

Mike

 

[tlbroadbent]

First, I have to ask why you need or want to do this?

Is the column an identity column?

Do you intend to resequence all rows when one row is renumbered? Thus if you have rows 1 to 5000 and decide to move row 200 to 5000, all rows from 200 to 5000 would have to be updated. This could be a costly process and seems totally unnecessary in a relational database.
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[rkelly95]

why do you want to do this,it would be easier to answer, so far I would not recommend doing it, because you will end up with clusters if you don't know what you are doing. which in turn will take away the second most important functionof a database..........SPEED!!!

 

[SQLSister]

Hey I'm with Terry on this one. We've had a real rash of these type requests lately. Why is everyone so concerned about gaps in their number sequences? In the example above an order by phrase would still put the records in the same order if they are 12456 or 12345, so why the unnecessary work? And if you think this is slow when 4800 out of 5000 records have to be updated, wait til you change record 12 of 5.6 million!!

 

[GSXR179]

Thank you all for responding,

The column is not an idenity column.

The purpose of this is to organize questions on an evaluation form by sequence. If I did not have a sequence column, then they would be ordered by ID number.

Is there a better way of organizing these questions?