MarkusB (5/19/2008)

---

Matt,

adding another order by in the outer select will only the sort the 10 orders returned and the result will look like this:

2879All-Purpose Bike StandST-1401NULL159,00

8843Cable LockLO-C100NULL25,00

9952ChainCH-0234Silver20,24

3712AWC Logo CapCA-1098Multi8,99

6877Bike...

Lynn Pettis (5/19/2008)

---

Matt,

Here is the code I was using to do my testing:

CREATE PROCEDURE dbo.usp_ProductPagesA @firstrow int = 1, @sort varchar(50) = 'ListPrice'

AS

BEGIN

SET NOCOUNT ON

...

The only order by you have is in the ROW_NUMBER - that's not going to reorder the set. It will help you with the paging process. Unless you actually...

Here's one way....

drop table #t

create table #t(rid int identity(1,1), x xml)

insert #t(x)

select @doc2

select #t.rid, t.col.value('.','varchar(50)') c,

t.col.query('local-name(.)') j

from #t cross apply #t.x.nodes('//ROWS/*') t(col)

Grant Fritchey (5/19/2008)

---

But even the read contention is only halved unless the index is covering. If it's not covering it still has to go to the table or the cluster...

ALZDBA (5/19/2008)

---

Jeff Moden (5/19/2008)

---

...I guess that's why part of my Mantra is "do as much as you can in T-SQL instead of CLR's" and "you can do more than you...

Michael David (5/19/2008)

---

use test

select Server.DB.dbo.testTable1.*

into Server2.test2.dbo.testTable1

from testTable1

go

The Server2 must be alinked server.

BTW, Dont do that. I am talking about select into. Bad SQL practice.

Use

INSERT INTO Table1(Col1,col2...)

SELECT col1,Col2 FROM Table2

If it...

Something else is going on, because I did just that (installed a default instance after a named instances). Are you going into Advanced mode? That's the only way...

So - ROW_NUMBER will not work. Like I mentioned earlier, changing the compatibility mode is something that has potential to break some of your code, so while it's probably...

Debbie - right-click on the database, then Properties, OPTIONS. There's a compatibility setting - what is it set to (DON'T mess with it, this would be a non-trivial change)?

Jeff Moden (5/17/2008)

---

Heh... I, on the other hand, will loath...

Debbie Edwards (5/19/2008)

---

Im being told ROW_NUMBER is not a recognised function name and Im hoping to stay clear of partitioning for this particular problem.

Mainly because its a bit beyond me...

Robert Hermsen (5/19/2008)

---

Interesting diversion yet somewhat on topic. Do you take the person back who is seeking greener grass?

I don't feel the money game should be played. Counter...

rbarryyoung (5/16/2008)

---

Matt Miller (5/16/2008)

---

Hey now! where the heck did THAT come from??? :hehe::w00t:

Sheesh - travel home to see the wife, and get smacked around...

No arguing with ya there:)