Updated table has changed the row order

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

1/
I inserted new data to the database using SQLDataAdapter. All data are
inserted, but the order of rows in db table is not identical with the order
in DataTable used for the batch update.

No sorting is made.

This occures when I insert more then 800 or 1000 rows.

2/
I tried to use ExecuteNonQuery in a loop also, and the result was the same.
If I inserted more rows then approximatelly 800, the row order in target
table has been changed.

Is it caused by some kind of caching? How to avoid this?

Thanks,

Lubomir
 
IMO rows are really inserted using the order you gave but generally speaking
when you query rows from a database wihtout specifying a particular order
they are returned as the DB wants (for example rows that are stored
physically on the same pages are likely kept together). If you want a
particular order, you have to specify the order in your data retrieval
operation (for example using the automatically incremented pk if you want
them by chronological order).
 
Ah, the order in which the rows are stored or returned is not guaranteed in
any (serious) relational engine. The ONLY way to ensure that the rows are
physically ordered is to create (in SQL Server) a clustered index. The best
way to ensure that rows are returned in a logical order is to use an ORDER
BY clause in the SQL query.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
 
Thank you for help.

Lubomir



William (Bill) Vaughn said:
Ah, the order in which the rows are stored or returned is not guaranteed in
any (serious) relational engine. The ONLY way to ensure that the rows are
physically ordered is to create (in SQL Server) a clustered index. The best
way to ensure that rows are returned in a logical order is to use an ORDER
BY clause in the SQL query.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
Click to expand...
 
Back
Top