Numbers inserting out of sequence

[rosn459]

I have a process that inserts records into a table. One of the fields is a tinyint field. The data in this field is 1,2,3,etc. For some reason, instead of the data being inserted as 1,2,3,4, etc. it might be inserted as 0,89,4,6.

This does not happen every time data is inserted into this table - just sometimes. Is there something I can do programmaticallyto keep this from happening?

 

[jbenson001]

Well, since we can't see your code, we have no idea. Is the field an identity column? Are you generating the sequence # somewhere in a front end app or sql and then inserting?

 

[SQLSister]

Also do you havea clustered index on the table, you might be seeing the data in a differnt order than it is actually inserted.

"NOTHING is more important in a database than integrity." ESquared

 

[rosn459]

Thanks so much for the responses.

The field is not an identity column.
The sequence number is generated in the front end app.
There is not a clustered indes on the table.

Your questions have sparked something else I need to check - I need to check exactly how the front end app is generating the sequence number. We are using Cold Fusion.

 

[SQLSister]

Generating a sequence number is almost always a bad idea. If multiple requests come in at the same time, then two records might get assigned the same sequence number. Or depending on the order in which things are don it is possible I suppose for the higher sequence number to be inserted first because the lower one failed the insert based on bad data and had to be resubmitted.

"NOTHING is more important in a database than integrity." ESquared

 

[JoeAtWork]

instead of the data being inserted as 1,2,3,4, etc. it might be inserted as 0,89,4,6.

How do you know they were not inserted in order, is there a DateTime field that is populated when the record is created?

Or do you mean that when you open the table without an ORDER BY clause, it does not sort the record by that sequence? My understanding has always been that databases do not return records in the order they were added. Without an ORDER BY clause, the order of the records will be unpredictable.