Learned something the hard way recently: just because a table has a clustered index doesn't mean a SELECT without an ORDER BY will return the rows in order. For example, given this table:
CREATE TABLE MyTable
(
ID INT IDENTITY (1, 1) NOT NULL
CONSTRAINT PK_tblMyTable PRIMARY KEY CLUSTERED (ID)
)
... if you say:
SELECT * FROM MyTable
... then the rows will be returned in undefined (that is, random) order.
"Clustering", I've learned, is a physical construct, not a logical one. The rows are laid out in physical order on the hard drive, but SQL Server is free (in the abscence of an ORDER BY clause) to return them in whatever order it thinks most efficient.
As developers, we don't usually see this behavior, because we tend to have fresh, well-maintained tables. In fact, I only discovered it in a customer's database that hadn't had any maintenance done to it in years. So, the bottom line is, as always, "no ORDER BY, no ordering."
