Row_Number() Sorting
in [Programming SQL]
|
Prev: Query Optimization
Next: Get Total Rows.
From: tshad on 26 Mar 2010 14:01 I am a little confused as to what Row_Number is doing based on the execution plan. If I have a table: CREATE TABLE T (PK INT IDENTITY, A INT, B INT, C INT, D INT) CREATE UNIQUE CLUSTERED INDEX TPK ON T(PK) INSERT T VALUES (0, 1, 8, 3) INSERT T VALUES (0, 3, 6, 5) INSERT T VALUES (0, 5, 4, 0) INSERT T VALUES (0, 7, 2, 8) INSERT T VALUES (0, 9, 0, 2) INSERT T VALUES (1, 0, 9, 9) INSERT T VALUES (1, 2, 7, 4) INSERT T VALUES (1, 4, 5, 1) INSERT T VALUES (1, 6, 3, 6) INSERT T VALUES (1, 8, 1, 7) And I do SELECT *, ROW_NUMBER() OVER (ORDER BY B) AS RowNumber FROM T order by D' The Results I get are: PK A B C D RowNumber ----- ----- ----- ----- ----- ----------- 3 0 5 4 0 6 8 1 4 5 1 5 5 0 9 0 2 10 1 0 1 8 3 2 7 1 2 7 4 3 2 0 3 6 5 4 9 1 6 3 6 7 10 1 8 1 7 9 4 0 7 2 8 8 6 1 0 9 9 1 The showplan_text gives me: StmtText ------------------------------------------------------------------------ SELECT *, ROW_NUMBER() OVER (ORDER BY B) AS RowNumber FROM T order by D (1 row(s) affected) StmtText --------------------------------------------------------------------------------- |--Sort(ORDER BY:([tempdb].[dbo].[T].[D] ASC)) |--Sequence Project(DEFINE:([Expr1003]=row_number)) |--Segment |--Sort(ORDER BY:([tempdb].[dbo].[T].[B] ASC)) |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[T].[TPK])) (5 row(s) affected) The results look like the Sord by D is done last. Is this plan read in reverse order? It looks like it does a Clustered Index scan first,then sort by B, the Segment (what is this?), then assigns the row numbers to the last row and then sorts by D. Is that right? Thanks, Tom
From: Gert-Jan Strik on 26 Mar 2010 14:40 Tom, You are reading the plan correctly. Typically, you will see the sorting on the ORDER BY expressions as the last sorting in the query plan, because this is the order in which the results need to be returned. It seems the Segment operator is used to partition the results. Since your OVER () clause does not have a partitioning function, the Segment operator doesn't show much. If you change your query to SELECT *, ROW_NUMBER() OVER (PARTITION BY A ORDER BY B) AS RowNumber FROM T order by D you will see column A in the argument of the operator. -- Gert-Jan > tshad wrote: > > I am a little confused as to what Row_Number is doing based on the > execution plan. > > If I have a table: > > CREATE TABLE T (PK INT IDENTITY, A INT, B INT, C INT, D INT) > CREATE UNIQUE CLUSTERED INDEX TPK ON T(PK) > > INSERT T VALUES (0, 1, 8, 3) > INSERT T VALUES (0, 3, 6, 5) > INSERT T VALUES (0, 5, 4, 0) > INSERT T VALUES (0, 7, 2, 8) > INSERT T VALUES (0, 9, 0, 2) > INSERT T VALUES (1, 0, 9, 9) > INSERT T VALUES (1, 2, 7, 4) > INSERT T VALUES (1, 4, 5, 1) > INSERT T VALUES (1, 6, 3, 6) > INSERT T VALUES (1, 8, 1, 7) > > And I do > > SELECT *, ROW_NUMBER() OVER (ORDER BY B) AS RowNumber FROM T order by > D' > > The Results I get are: > > PK A B C D RowNumber > ----- ----- ----- ----- ----- ----------- > 3 0 5 4 0 6 > 8 1 4 5 1 5 > 5 0 9 0 2 10 > 1 0 1 8 3 2 > 7 1 2 7 4 3 > 2 0 3 6 5 4 > 9 1 6 3 6 7 > 10 1 8 1 7 9 > 4 0 7 2 8 8 > 6 1 0 9 9 1 > > The showplan_text gives me: > > StmtText > ------------------------------------------------------------------------ > SELECT *, ROW_NUMBER() OVER (ORDER BY B) AS RowNumber FROM T order by > D > > (1 row(s) affected) > > StmtText > --------------------------------------------------------------------------------- > |--Sort(ORDER BY:([tempdb].[dbo].[T].[D] ASC)) > |--Sequence Project(DEFINE:([Expr1003]=row_number)) > |--Segment > |--Sort(ORDER BY:([tempdb].[dbo].[T].[B] ASC)) > |--Clustered Index > Scan(OBJECT:([tempdb].[dbo].[T].[TPK])) > > (5 row(s) affected) > The results look like the Sord by D is done last. Is this plan read > in reverse order? > > It looks like it does a Clustered Index scan first,then sort by B, the > Segment (what is this?), then assigns the row numbers to the last row > and then sorts by D. > > Is that right? > > Thanks, > > Tom >
From: tshad on 26 Mar 2010 15:43 I didn't realize that the plans were read in reverse order. I want to be able to say give me row 100 - 150 so that is why I am using Row_Number(). But confused as to why the number has to be on an ordered column. So if you normally do an order at the end of the select statement, you would really want to move that order by to the OVER() clause or else you are ordering twice. Seems like a lot of overhead just to get the rows numbered. Thanks, Tom "Gert-Jan Strik" <sorrytoomuchspamalready(a)xs4all.nl> wrote in message news:4BACFFA3.331A5277(a)xs4all.nl... > Tom, > > You are reading the plan correctly. Typically, you will see the sorting > on the ORDER BY expressions as the last sorting in the query plan, > because this is the order in which the results need to be returned. > > It seems the Segment operator is used to partition the results. Since > your OVER () clause does not have a partitioning function, the Segment > operator doesn't show much. If you change your query to > > SELECT *, ROW_NUMBER() OVER (PARTITION BY A ORDER BY B) AS RowNumber > FROM T order by D > > you will see column A in the argument of the operator. > > -- > Gert-Jan > > >> tshad wrote: >> >> I am a little confused as to what Row_Number is doing based on the >> execution plan. >> >> If I have a table: >> >> CREATE TABLE T (PK INT IDENTITY, A INT, B INT, C INT, D INT) >> CREATE UNIQUE CLUSTERED INDEX TPK ON T(PK) >> >> INSERT T VALUES (0, 1, 8, 3) >> INSERT T VALUES (0, 3, 6, 5) >> INSERT T VALUES (0, 5, 4, 0) >> INSERT T VALUES (0, 7, 2, 8) >> INSERT T VALUES (0, 9, 0, 2) >> INSERT T VALUES (1, 0, 9, 9) >> INSERT T VALUES (1, 2, 7, 4) >> INSERT T VALUES (1, 4, 5, 1) >> INSERT T VALUES (1, 6, 3, 6) >> INSERT T VALUES (1, 8, 1, 7) >> >> And I do >> >> SELECT *, ROW_NUMBER() OVER (ORDER BY B) AS RowNumber FROM T order by >> D' >> >> The Results I get are: >> >> PK A B C D RowNumber >> ----- ----- ----- ----- ----- ----------- >> 3 0 5 4 0 6 >> 8 1 4 5 1 5 >> 5 0 9 0 2 10 >> 1 0 1 8 3 2 >> 7 1 2 7 4 3 >> 2 0 3 6 5 4 >> 9 1 6 3 6 7 >> 10 1 8 1 7 9 >> 4 0 7 2 8 8 >> 6 1 0 9 9 1 >> >> The showplan_text gives me: >> >> StmtText >> ------------------------------------------------------------------------ >> SELECT *, ROW_NUMBER() OVER (ORDER BY B) AS RowNumber FROM T order by >> D >> >> (1 row(s) affected) >> >> StmtText >> --------------------------------------------------------------------------------- >> |--Sort(ORDER BY:([tempdb].[dbo].[T].[D] ASC)) >> |--Sequence Project(DEFINE:([Expr1003]=row_number)) >> |--Segment >> |--Sort(ORDER BY:([tempdb].[dbo].[T].[B] ASC)) >> |--Clustered Index >> Scan(OBJECT:([tempdb].[dbo].[T].[TPK])) >> >> (5 row(s) affected) >> The results look like the Sord by D is done last. Is this plan read >> in reverse order? >> >> It looks like it does a Clustered Index scan first,then sort by B, the >> Segment (what is this?), then assigns the row numbers to the last row >> and then sorts by D. >> >> Is that right? >> >> Thanks, >> >> Tom >>
From: Plamen Ratchev on 26 Mar 2010 17:10 The ROW_NUMBER function is based on ordering of rows based on the ORDER BY clause of OVER. This does not guarantee ordering of the result set, but only ordering for the generations of ROW_NUMBER. The final query ORDER BY is used to order the results set (and if the TOP option is used to service that). If your ROW_NUMBER ORDER BY expressions are the same as the query ORDER BY expressions then the chances are that you will see only one sort in the query plan. -- Plamen Ratchev http://www.SQLStudio.com
From: tshad on 26 Mar 2010 18:14
If it is based on an ordering, how do you set it up when you have a Query order by. There is no unique column in the select statement In my case, I have an ORDER BY that uses 4 columns some text, integers and booleans. Select ... ORDER BY IsNull(VenName, 'zz9'),EnvID, IsLocked Desc, IsNull(OtherName,'zz9'), Name, Desc How would I set up my Row_Number() Over(Order By) here? Would I do the same thing as the query ORDER BY? I need it to be in this order and the RowNumbers need to be in this order as I am getting the 1st 100 rows then the 2nd hundred rows etc. Thanks, Tom "Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message news:u4qdnf00fNBJvzDWnZ2dnUVZ_j2tnZ2d(a)speakeasy.net... > The ROW_NUMBER function is based on ordering of rows based on the ORDER BY > clause of OVER. This does not guarantee ordering of the result set, but > only ordering for the generations of ROW_NUMBER. The final query ORDER BY > is used to order the results set (and if the TOP option is used to service > that). If your ROW_NUMBER ORDER BY expressions are the same as the query > ORDER BY expressions then the chances are that you will see only one sort > in the query plan. > > -- > Plamen Ratchev > http://www.SQLStudio.com |