row_number query

If i run the query
with test as
(select *, row_number() over (order by user_name()) as cnt
from table1)
select * from test order by cnt
Will i always get rows in same order every time when I run the query?shahdha...@.gmail.com wrote:
> If i run the query
> with test as
> (select *, row_number() over (order by user_name()) as cnt
> from table1)
> select * from test order by cnt
> Will i always get rows in same order every time when I run the query?
no, it is not guaranteed to be the same.|||No, you're ordering by a constant.
If you want to return the rows in an order that represents something in the
data, why don't you order by one of the data columns?
<shahdharti@.gmail.com> wrote in message
news:1150815893.641027.46750@.b68g2000cwa.googlegroups.com...
> If i run the query
> with test as
> (select *, row_number() over (order by user_name()) as cnt
> from table1)
> select * from test order by cnt
> Will i always get rows in same order every time when I run the query?
>|||No. You use a function in the OVER clause for the ORDER BY which resolves to
the same value for
every row. This means that SQL Server can access the data in any way it find
s most efficient. In
short, the ROW_NUMBER function is not deterministic unless you specify a col
umn which is unique.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<shahdharti@.gmail.com> wrote in message news:1150815893.641027.46750@.b68g2000cwa.googlegrou
ps.com...
> If i run the query
> with test as
> (select *, row_number() over (order by user_name()) as cnt
> from table1)
> select * from test order by cnt
> Will i always get rows in same order every time when I run the query?
>|||<shahdharti@.gmail.com> wrote in message
news:1150815893.641027.46750@.b68g2000cwa.googlegroups.com...
> If i run the query
> with test as
> (select *, row_number() over (order by user_name()) as cnt
> from table1)
> select * from test order by cnt
> Will i always get rows in same order every time when I run the query?
>
No. You get what you ask for. Add the key of Table1 to the OVER ORDER BY
clause and it will be fine.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--