Sounds trivial? Right, but different flavors of this myth still persist. Yesterday I noticed a thread on stackoverflow discussing SELECT TOP without a where clause, where it was suggested that "The order will be defined based on the clustered key in that table."
http://stackoverflow.com/questions/882195/what-does-top-1-mean-in-an-sql-query/882690#882690
Because apparently many visitors agreed with this myth, I decided to post a repro script which demonstrates that this is simply not true. Here you go:
CREATE SCHEMA Data AUTHORIZATION dbo
GO
CREATE TABLE Data.Numbers(Number INT NOT NULL PRIMARY KEY)
GO
DECLARE @ID INT;
SET NOCOUNT ON;
SET @ID = 1;
WHILE @ID < 100000 BEGIN
INSERT INTO Data.Numbers(Number)
SELECT @ID;
SET @ID = @ID+1;
END
CREATE TABLE Data.WideTable(ID INT NOT NULL
CONSTRAINT PK_WideTable PRIMARY KEY,
RandomInt INT NOT NULL,
CHARFiller CHAR(1000))
GO
CREATE VIEW dbo.WrappedRand
AS
SELECT RAND() AS random_value
GO
CREATE ALTER FUNCTION dbo.RandomInt()
RETURNS INT
AS
BEGIN
DECLARE @ret INT;
SET @ret = (SELECT random_value*1000000 FROM dbo.WrappedRand);
RETURN @ret;
END
GO
INSERT INTO Data.WideTable(ID,RandomInt,CHARFiller)
SELECT Number, dbo.RandomInt(), 'asdf'
FROM Data.Numbers
GO
CREATE INDEX WideTable_RandomInt ON Data.WideTable(RandomInt)
GO
SELECT TOP 100 ID FROM Data.WideTable
1407
253
9175
6568
4506
1623
581
As you have seen, the optimizer has chosen to use a non-clustered index to satisfy this SELECT TOP query.
Clearly you cannot assume that your results are ordered unless you explicitly use ORDER BY clause.
This post continues my series on defensive database programming. My next post:
When you add an index and your query blows up