Insert data into sqlserver 2000 database, the order change, why ?

views:

answers:

+1 Q:

Insert data into sqlserver 2000 database, the order change, why ?

Insert 200 data througn for-loop into sqlserver 2000 database, the order change, why ?

When I use mysql, it doesn't have the matter.

i mean:

when you insert 2, then insert 3, then insert 1, in mysql you will see 2,3,1 like the order you insert. But in sqlsever2000 that may not.

+7 A:

The order in which rows are stored doesn't really matter. Actually SQL tables don't have any ordering. If order matters to you, you need to query using an ORDER BY clause.

Frank Kalis 2010-01-13 07:12:59

thanks,but when insert 'one', insert 'two', insert 'three', into sqlserver2000, it maybe two,one,three. In mysql, it must be one,two,three. T T

Keating 2010-01-13 07:44:19

Frank Kalis 2010-01-13 07:57:09

when you insert 2, then insert 3, then insert 1, in mysql you will see 2,3,1 like the order you insert. But in sqlsever2000 that may not.

Keating 2010-01-13 08:05:26

This is just a side-effect of the table storage mechanism MySQL is using for that table. It's certainly not guaranteed to come back in that order, and the order may change depending on the data in the table and the execution plan that the query optimizer chooses.

Jon 2010-01-13 16:04:49

+3 A:

There are no guarantees on the order of the results of a select statement, unless you add an ORDER BY clause. You might get the results back in the order of insertion, but it's not guaranteed.

Jon 2010-01-13 07:13:20

+1 A:

If you have an index on the table, it might appear that your data is being ordered in a way that you're not expecting. Normally, you'll just have an identity column as your surrogate primary key, which means your inserted data will show up in "order" if you just do a select *, but if you have indexes on other columns the data might be ordered differently when you do select *.

Andy White 2010-01-13 07:16:15

By Index, I hope you mean Clustered index. And for Other Column Index, you mean Non Clustered Index. Just to clarify ......

Nitin Midha 2010-01-13 07:21:24

You can control physical ordering by creating a clustered index on that columns. Only one clustered index is allowed per table. Cluster index make sure when you perform SELECT you will always get data in order which was defined when creating cluster index.

affan 2010-01-13 07:26:17

That's a common misconception. Check these ones: http://consultingblogs.emc.com/davidportas/archive/2006/05/30/4007.aspxhttp://sqlblog.com/blogs/hugo_kornelis/archive/2006/12/31/Beatles-vs-Stones.aspx

Frank Kalis 2010-01-13 08:17:14

The physical ordering a cluster mean that record of that table is clustered together on disk and does not mean they appear like in a b c on disk. There are several optimization based on data type etc which sqlserver/orcale perform at disklevel. But cluster mean grouping record at consectivly there is still a index/ BTREE to determine the correct order inside cluster. But it simily take less time for sqlserver to read data from disk. You can compare it with defragmentation process in hardrive which bring cluster of files together for fast reading.

affan 2010-01-13 08:46:58

Have you actually read my links? You can control physical ordering only at time to creating / recreating the clustered index. Thereafter or in between only the logical ordering is guaranteed to be maintained by SQL Server. This may or may not lead to index fragmentation over time depending on what you choose to be the clustered index."There are several optimization based on data type etc which sqlserver perform at disklevel"What exactly do you mean by this?

Frank Kalis 2010-01-13 10:56:05

ansaurus

tags:

views:

answers:

Insert data into sqlserver 2000 database, the order change, why ?

related questions