Home arrow Howto's arrow Howto - SQL
Howto - SQL
MySQL Cheat Sheet - Quick command list. PDF Print E-mail
Written by David Noel-Davies   
MySQL is a useful database server, but sometimes I forget the syntax of some commands that I use a little less often, so I decided to make myself a cheat sheet of common commands.
Read more...
 
Nondeterministic Row Numbers PDF Print E-mail
Written by David Noel-Davies   

The row_number function is an extremely powerful tool that I use to simplify and optimize solutions to many
problems. Occasionally I needed to calculate nondeterministic row numbers where order didn’t matter. For
example, suppose you have the following t1 table:

set nocount on;use tempdb;goif object_id('dbo.t1') is not null  drop table dbo.t1;gocreate table dbo.t1(col1 varchar(10) not null);insert into dbo.t1(col1) values('c');insert into dbo.t1(col1) values('a');insert into dbo.t1(col1) values('b');insert into dbo.t1(col1) values('a');insert into dbo.t1(col1) values('b');insert into dbo.t1(col1) values('b');insert into dbo.t1(col1) values('c');insert into dbo.t1(col1) values('a');insert into dbo.t1(col1) values('b');insert into dbo.t1(col1) values('a');go

You need to return all rows from t1 along with unique incrementing row numbers in no particular order. If
order doesn’t matter, of course you can always specify an existing column from the table in the row_number
function’s order by clause:

select col1, row_number() over(order by col1) as rownum

from dbo.t1;

However, the execution plan for the query would involve sorting (or an index order scan if an index exists on
the sort column). Here’s the plan you get for the above query:

  |--Sequence Project(DEFINE:([Expr1004]=row_number))       |--Compute Scalar(DEFINE:([Expr1006]=(1)))            |--Segment                 |--Sort(ORDER BY:([tempdb].[dbo].[t1].[col1] ASC))                      |--Table Scan(OBJECT:([tempdb].[dbo].[t1]))

If the order of the row numbers doesn’t matter to you, you’d probably rather not pay the sort penalty. The
problem is that SQL Server doesn’t allow a constant in the row_number function’s order by clause. Try running
the following query:

select col1, row_number() over(order by 0) as rownumfrom dbo.t1;

And you will get the following error:

Msg 5309, Level 16, State 1, Line 1Windowed functions do not support constants as ORDER BY clause expressions.

Until recently, in order to avoid sorting I defined a table expression (CTE or derived table) based on a query
that returns the table rows along with a constant (call it const). In the outer query I invoked the row_number
function with order by const:

with c as(  select col1, 0 as const from dbo.t1)select col1, row_number() over(order by const) as rownum

from c;

Here, the optimizer is smart enough to realize that sorting is not required. Here’s the plan I got for this query
(notice there’s no sort operation):

  |--Sequence Project(DEFINE:([Expr1005]=row_number))       |--Compute Scalar(DEFINE:([Expr1007]=(1)))            |--Segment                 |--Table Scan(OBJECT:([tempdb].[dbo].[t1]))

Even though this technique avoids sorting, it is a bit awkward. Recently I got a tip from a T. Wong how to
achieve the same thing without the need for a table expression—simply specify order by (select 0)!

Here’s the solution query with the new technique:

select col1, row_number() over(order by (select 0)) as rownum

from dbo.t1;

It is much more elegant than the previous technique, and also here the plan shows that the optimizer realized
that sorting is not needed:

  |--Sequence Project(DEFINE:([Expr1006]=row_number))       |--Compute Scalar(DEFINE:([Expr1008]=(1)))            |--Segment                 |--Compute Scalar(DEFINE:([Expr1005]=(0)))

                      |--Table Scan(OBJECT:([tempdb].[dbo].[t1]))

 
Powered by IT CONTRACTORS and designed by EZPrinting web hosting