Sunday, April 9, 2017

SQL Server partitioning & result set ordering

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.


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