Jag hade nyligen en kund som hade fått problem med en rapport. Jag åkte dit och tittade och kunde konstatera att denna kund, liksom många andra, förlitar sig på default-värden i T-SQL och de funktioner som erbjuds.
I just det här fallet var det en s.k Running Total som hade ställt till det för kunden. I versioner före SQL Server 2012 så har det varit omständigt att göra detta på ett bra sätt men från och med version 2012 finns det inbyggt i T-SQL.
Det är genom att används Partitioned Functions som detta gör att göra. Nu är inte detta en sak som Microsoft gör på egen hand utan dessa är definierade i ANSI SQL. Däremot är de olika tillverkarna olika snabba på att implementera funktionaliteten. Microsoft är inte en av de snabbare.
Från och med SQL Server 2005 introducerades PARTITION BY. Och nu finns det ORDER BY samt WINDOW FRAMING.
Denna kund har ca en miljon rader i en tabell som det ska göras en running total på. Eftersom de har en kolumn med Avdelning, så partitionerar de på denna kolumn så att varje avdelning hanteras separat. För att få en konsekvent running total så gör de en ORDER BY på en annan kolumn. Det gör att alla rader inom partitionen sorteras på det önskade sättet. Men sedan händer något…
I min kunds fall skrev de inte ut ROWS eller RANGE utan förlitade sig på att RANGE ska inträffa, vilket det också gör. Men det är farligt som det skulle visa sig. Se detta exempel
Som ni kan se så ska de två original-kolumnerna visas, samt tre ytterligare kolumner för att demonstrera faran i att använda default-värden.
Det här är resultatet av ovanstående kod
Som ni kan se blir running total inte som man kanske förväntar sig, när det finns dubbletter i sorteringen. Varför händer detta?
Jo, eftersom RANGE enbart hanterar UNBOUNDED PRECEDING, CURRENT ROW och UNBOUNDED FOLLOWING, dvs ett intervall, eller RANGE.
Då rad 2 processas ser SQL Server att Co1 (som det sorteras på) har värdet 2. RANGE träder i kraft och alla rader fram till och med 2 tas med.
Då kommer även rad 3 med!
Skillnaden blir tydlig när man ser hur ROWS hanterar samma data. Den uppträder som man kan förvänta sig. Dessutom finns det en klar prestandaskillnad med mellan ROWS och RANGE. Se följande bild på samma data
Om du tittar på exekveringsplanen så introduceras en Window Spool som har två olika sätt att hantera datat som flödar igenom.
- In–memory worktable
- Disk–based worktable
Som du kan se på resultet av SET STATISTICS IO ON är det en stor skillnad på prestanda. In-memory worktable används när du definierar framingen med ROWS och antalet rader är mindre än 10 000 rader. Om framingen är fler än 10 000 rader kommer Windows Spool automatiskt att slå över till disk-based worktable.
Default är Disk-based worktable (RANGE, som alltid använder disk-based worktable) och det är det som hände min kund.
Ni som är riktigt observanta la säkert också märke till att de två raderna som har Col1 = 2 bytte ordning mellan hur datasetet såg ut i min Common Table Expression och i det slutgiltiga datasetet.
