When querying a clustered table, the order of the default query results must be sorted by the clustered index order when the sorting sequence is not explicitly specified

In SQL Server, if a table has a clustered index, in most cases, if a select * from tablename query is made, the default return order is returned in the order in which the columns are clustered.

However, when a table has a clustered index, it does not necessarily mean that all cases are arranged in the order of the clustered index columns.

Start testing below

CREATE TABLE Testdefaultorder (Id int identity (primary) key,--a clustered index is established by default on the primary key Col2 char (5), COL3 char (5))-- Write 100,000 test data insert into Testdefaultorder  values (SUBSTRING (CAST (NEWID () as varchar), 1,5), SUBSTRING (CAST ( NEWID () as varchar (), 1,5)) go 100000

There is no problem with the following query, as you would expect, sorted by the column (ID) where the clustered index is located, there is no problem at all.

--Create a table of the same table testdefaultorder_contrast (Id int identity (primary) key,--The clustered index is established by default on the primary key Col2 char (5), COL3 CHAR (5))--Writes the data in the Testdefaultorder table, and currently the data and index structure of the two tables are identical insert into Testdefaultorder_contrast (COL2,COL3)  Select Col2,col3 from testdefaultorder--only creates a nonclustered index on the comparison table, which is the only difference between create index idx2 on Testdefaultorder_contrast (Col2, COL3)

The next query, maybe 1.1 points to your surprise,

As stated above, the data of two tables is identical, and the clustered index structure is the same, but the comparison table is more than one nonclustered index

The results of the lookup table are returned in the order that they are returned by the sort of the clustered index.

So where is the reason? We want to analyze the space situation of different indexes, and through Dm_db_index_physical_stats discovery, when the data data is identical,

Because testdefaultorder_contrast the nonclustered index on this table, it takes up less space (248 page, and Testdefaultorder's clustered index is 285 page).

Because of this, SQL Server selects a less expensive index (for scanning) when a full table scan is performed.

Because there is only one clustered index on the Testdefaultorder table, the query is performed by a clustered index scan, and the order of the returned results is sorted by the clustered index column.

But Testdefaultorder_contrast is different, because on the nonclustered index IDX2, all the data (COL2,COL3 and the ID that points to the clustered index key value) is included.

However, the IDX2 index is less space-intensive, so the query for Testdefaultorder_contrast is scanned by idx2 this nonclustered index.

Because, on the Testdefaultorder_contrast table,

Direct SELECT * Testdefaultorder_contrast for enquiries,

SELECT * Testdefaultorder query against table Testdefaultorder

is done in two completely different ways, and the results are naturally different.

In fact, when SQL Server queries the Testdefaultorder_contrast, by walking idx2 This index scan, the cost is really more than the testdefaultorder of the clustered index scan, the cost is small

If interested, analyze again why the same data is stored (clustered index on Testdefaultorder and testdefaultorder_contrast nonclustered index IDX2),

The clustered index on the Testdefaultorder table is greater than the idx2 on the Testdefaultorder_contrast (Create index idx2 on Testdefaultorder_contrast (col2,col3 ) is a big space to occupy.

The reason for this is that a clustered index on a table (compared to a nonclustered index), in addition to the data to be stored, the information to maintain more metadata information, take up more space naturally a little more

SQL Server always chooses a relatively low-cost approach when querying.

Summary: Do not think, as long as the table on the establishment of a clustered index, in the query, the return result of the default sorting method, according to the clustered index

PostScript: Why study this problem?

Some queries do not explicitly specify the sort order, but with a clustered index on the table, the results are returned in a desired sequence.

This situation can actually be a potential problem, if a similar situation occurs, you want to query results in the order of the clustered index, and do not explicitly set the sorting sequence, the order in which the query results are displayed, is not necessarily.

When querying a clustered table, the order of the default query results must be sorted by the clustered index order when the sorting sequence is not explicitly specified