I created a temp table with an order by clause. If later on I query it, would my result set still follow the order by sort or is it going to be indeterminate like querying an actual table? This is hard to test because 999 of 1000 times I might get an ordered result set.
There is no such thing as an ordered table, temporary or otherwise. If your query doesn't have an ORDER BY clause then the order is undefined and could possibly be different each time the query is used.
CREATE TABLE dbo.order_by (
id int identity(1,1) PRIMARY KEY CLUSTERED
, some_field char(1)
INSERT INTO dbo.order_by (some_field) VALUES ('B')
INSERT INTO dbo.order_by (some_field) VALUES ('A')
CREATE NONCLUSTERED INDEX ni_order_by_some_field ON dbo.order_by (some_field ASC)
DROP TABLE dbo.order_by
are you sure? someone answered differently from another (codeproject) forum.
What order do you expect if you don't specify ORDER BY? Since there is no way to define an "ordering" for a table how can you expect to know in what order rows are stored or retrieved? Even if you insert rows one at a time in sequence there is no guarantee they are actually stored in any predictable order (because page splits may occur for example) and even if they were there is no way to instruct SQL Server to use that ordering again because the optimiser decides whether to scan indexes using either index or allocation order, or possibly using a so-called "merry-go-round" scan. (Google it)
Also, Books Online specifically says: "When used together with a SELECT...INTO statement to insert rows from another source, the ORDER BY clause does not guarantee the rows are inserted in the specified order."