mtassin (12/21/2010)
These days I begin to wonder if the optimizer doesn't just return them in clustered index order when the ORDER BY clause is omitted (and a clustered index is present), and Microsoft lists in BOL that the order is arbitrary so that if they need to change the optimizer for some reason with a service pack, they can say "We told you it was arbitrary".
No, the optimizer (and storage engine) can choose whatever access path seems most efficient. For example:
CREATE TABLE dbo.Example
(
row_id INTEGER IDENTITY NOT NULL PRIMARY KEY CLUSTERED,
data AS CONVERT(VARCHAR(11), row_id) PERSISTED NOT NULL UNIQUE
);
GO
INSERT dbo.Example
DEFAULT VALUES;
GO 10
SELECT E.row_id, E.data
FROM dbo.Example E;
GO
DROP TABLE dbo.Example;
Even on a simple scan of the clustered index, there are a number of ways to show that ordering isn't guaranteed. Using parallelism is the most popular one, but this works too:
CREATE TABLE dbo.Example
(
data BIGINT NOT NULL,
padding CHAR(5000) NOT NULL DEFAULT ''
);
GO
CREATE CLUSTERED INDEX c
ON dbo.Example (data)
WITH (ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF);
GO
INSERT dbo.Example (data) VALUES (CHECKSUM(NEWID()));
GO 64
-- Clustered index scan, but not in clustered index order!
SELECT TOP (10)
data
FROM dbo.Example
GO
DROP TABLE dbo.Example;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi