Thought to myself today: a partitioning scheme with one row per partition, partitioned on clustered index, still wouldn't guarantee order of result set. Turns out harder to create a counter-example than I thought.
Rats! I expected to get 8 returned by both 'SELECT TOP (1)' queries. In the first query, because of the specified ORDER BY. In the second query, because that page satisfied the query (which has no ORDER BY) and was already in the database cache. Alas, the first query returned 8 and the second returned 1.
Back to the drawing board :-)
CREATE PARTITION FUNCTION lkn_pf (int)
AS RANGE LEFT FOR VALUES (1, 2, 3, 4, 5, 6, 7, 8) ;
GO
CREATE PARTITION SCHEME lkn_ps
AS PARTITION lkn_pf ALL
TO ([primary]) ;
GO
CREATE TABLE lkn_pt (col1 int PRIMARY KEY clustered)
ON lkn_ps (col1) ;
INSERT INTO lkn_pt VALUES
(8),
(7),
(6),
(5),
(4),
(3),
(2),
(1)
CHECKPOINT
DBCC DROPCLEANBUFFERS
SELECT TOP (1) * FROM lkn_pt ORDER BY col1 DESC
SELECT TOP (1) * FROM lkn_pt
Rats! I expected to get 8 returned by both 'SELECT TOP (1)' queries. In the first query, because of the specified ORDER BY. In the second query, because that page satisfied the query (which has no ORDER BY) and was already in the database cache. Alas, the first query returned 8 and the second returned 1.
Back to the drawing board :-)
No comments:
Post a Comment