Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!aioe.org!eternal-september.org!feeder.eternal-september.org!.POSTED!not-for-mail From: Erland Sommarskog Newsgroups: comp.databases.ms-sqlserver Subject: Re: Explicit IN clause vs JOIN. Plan tanks with Join, Why? Date: Tue, 4 Oct 2011 07:22:18 +0000 (UTC) Organization: Erland Sommarskog Lines: 29 Message-ID: References: <36300203-0fcb-4e25-b476-210034ac5f07@db5g2000vbb.googlegroups.com> <838fa98d-9594-4624-9b39-e8715aa8b793@e9g2000vby.googlegroups.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Injection-Date: Tue, 4 Oct 2011 07:22:18 +0000 (UTC) Injection-Info: mx04.eternal-september.org; posting-host="G7+Jz22XqYCG8C6rb1H3YA"; logging-data="4065"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX19PvIfyvomNQvzwnC5bh5RN" User-Agent: Xnews/2005.10.03 Mime-proxy/1.4.c.4 (Win32) Cancel-Lock: sha1:syv0hwAPeAykoIKWTgWOyMuSHIo= Xref: x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:731 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