Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #731

Re: Explicit IN clause vs JOIN. Plan tanks with Join, Why?

Show all headers | View raw

bill (billmaclean1@gmail.com) writes:
> But a reasonable question some may ask me is "why the ROW_NUMBER()
> function in the first place?"  I realize that order is not
> relationally significant.  However, I am working with a commercial
> system, and unfortunately order IS significant for parts of it, and I
> can't avoid the issue.  The ROW_NUMBER() takes care of the problem.

What problem? You row_number() because you want number rows in your result 
set, or you use the number to filter later. If you are using row_number() 
with the intention that you will get a certain order in the output, you
have a bug you need to fix.
 
> I think with a hard coded WHERE clause, the ROW_NUMBER() operator was
> limiting itself to just the values listed in the partition.  With the
> join, the ROW_NUMBER() was operating on the entire result set, even
> though it should have limited to jus the values in some_table.

It is impossible to tell without seeing the query, but adding row_number
in a CTE can prevent the optimizer from recasting computation order, 
since that would affect the result from row_number().


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Explicit IN clause vs JOIN.  Plan tanks with Join, Why? bill <billmaclean1@gmail.com> - 2011-09-30 00:24 -0700
  Re: Explicit IN clause vs JOIN.  Plan tanks with Join, Why? "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2011-09-30 07:45 -0400
  Re: Explicit IN clause vs JOIN.  Plan tanks with Join, Why? Hugo Kornelis <hugo@perFact.REMOVETHIS.info.INVALID> - 2011-09-30 21:27 +0200
  Re: Explicit IN clause vs JOIN. Plan tanks with Join, Why? Erland Sommarskog <esquel@sommarskog.se> - 2011-09-30 23:49 +0200
    Re: Explicit IN clause vs JOIN. Plan tanks with Join, Why? bill <billmaclean1@gmail.com> - 2011-10-03 22:02 -0700
      Re: Explicit IN clause vs JOIN. Plan tanks with Join, Why? Erland Sommarskog <esquel@sommarskog.se> - 2011-10-04 07:22 +0000
        Re: Explicit IN clause vs JOIN. Plan tanks with Join, Why? bill <billmaclean1@gmail.com> - 2011-10-04 23:23 -0700

csiph-web