Click here to Skip to main content
15,877,857 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:

I am using SQL 2005 Express.

I have a table with an auto increment field named rec_id. This table has 5 'funny' records, rec_id 33941 to 33945. Fyi, the table has no problem adding records and the rec_id now has gone beyond that 5 records, 34000+. We only discovered these 5 records today.

If I do a "Select rec_id from masterreceipt", that set of 5 records will always remain at the bottom 5, even if I added "order by rec_id" to the command. The result will look something like this.

But if I further add "desc" to the same command, that set of 5 records will not appear at all.

And also, that 5 records cannot be 'inner join' by other tables. It will not return the rows.

I am pretty sure that set of records has never been thru an update or a delete command. I've checked the content as well, nothing out of the ordinary.

Can anybody please tell me what is going on?

Thank you.
Kuthuparakkal 4-Oct-12 8:41am    
stop sql server service and then start to see if any problem
Zakirom 4-Oct-12 8:47am    
Tried but no effect.

Also, that 5 records cannot be deleted or updated
Zakirom 4-Oct-12 8:48am    
Thank you btw.
Kuthuparakkal 4-Oct-12 8:51am    
try : detach and attach your db
if not working, take back up, reinstall sql, and restore your db
Zakirom 4-Oct-12 9:00am    
Also tried, no effect. I've also backup the db and restore it at another server. Same problem persist.

1 solution

I modified the table, turning off the IsIdentity property of the said field. When I checked, the 5 records were back in place, at the order of entry. After turning back on the IsIdentity property, everything works fine again.

But I wonder what causes it. I need to explain it to my customers coz this seemingly simple problem caused a customer service failure resulting in a complain. I need to tell them that it is not my program that causes it.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900