CODEWITHSUNDEEP
c#sqlsql-serverinsertrecord
marina Nishani
marina Nishani

Reputation: 11

Inserted records missing

I use sql server express as my backend and insert records through C#, in ASP Net. In the table . i find only few records are not getting saved while other records get saved in the same table. say my auto identity column looks as 1,2,3,5,8,9,10,13 etc. I have given the identity seed correctly. also application does not throw out any errors.

Please note i m not using any table locks while inserting the records. Is it required to use a table lock or what could be the issue

any insight in this regard is appreciated.

Thanks

Upvotes: 1

Views: 245

Answers (2)

Igor Borisenko
Igor Borisenko

Reputation: 3866

Your problem (actually it's not a problem) doesn't related to locks.

The most possible reason for it can be deleting of records. When a record is deleted the gap in the identity is not filled when a new record is added.

Also, it is possible that query, adding records into the table, uses IDENTITY_INSERT ON, which allows to insert values into an identity column.

Also, query results are not sorted by identity column unless it's specified in ORDER BY clause.

Anyway, if you think that something going wrong, you can run trace in SQL Server Profiler and see what is going on the server.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270823

Records in a table are not guaranteed to be in order. Try running the query:

select *
from t
order by <auto-identity column>

Also, deleting records will create holes in the primary keys.

Upvotes: 1

Related Questions