Is Order by sorted by a primary key not specified in SQL? sqlorder

Is Order by sorted by a primary key not specified in SQL? sqlorder

If Order by is not specified in SQL, is the sorting based on the primary key? The answer is not necessarily. For example:

Queries the AttendanceEmpRank table. The primary key is the AttendanceEmployeeRankId and the primary key is the clustered index.

Execute the following statement and find that the result of not specifying Order by in the first sentence is different from that in the second sentence.

Let's look at the execution plan. We can know that the first sentence uses the Date index, and the second sentence uses the primary key index.

Let's look at another set of SQL statements and query results:

The indexes used in the execution plan are also different:

Therefore, it is concluded that if Order by is not specified, sqlserver will obtain data based on the actual query method of the Execution Plan, and the execution plan will be based on many factors in the SQL (query column, where condition, order by, etc.) and use different indexes, the final results may be different.

If SQL server does not use the order by clause, what sort of results are returned by the query?

If the order by clause is not specified, the query results are in an uncertain order. It must be based on the primary key. The query optimizer generates an execution plan based on the actual situation. Sorting costs a lot, and the cost may not be small. If the order by clause is not explicitly specified, the query results are in an uncertain order.
If you want to return results in a certain order, you must specify the order by clause.

Is the data obtained by SQL statement select sorted by the primary key id by default?

This has nothing to do with your primary key. If it is a heap organization table, the sorting is only for the primary key index, and the primary key is not sorted in the data table, the data sequence depends on the block in which your data is stored.