I think we all wanted to say that :).

See my previous post.

pre- v2008 is not supported. Those links give the v2012 editions that are supported and the v2008 and 2008R2 service packs needed.

The large table has that many rows but are they all involved in the join to the temp table or does that join filter the rows accessed and if so...

How many rows does it need to access in the large table - is it most of them or a few.

Are the group by columns independent? Can some be omitted...

You can find the list for SQL Server 2012 here

http://msdn.microsoft.com/en-us/library/ms143506.aspx

The links for other versions don't seem to work. I'll have a look for them

This has a statement that v2005 is...

Oops yes

SELECT AA.[Country Code],

SUM(extendedamount) AS Amount

FROM dbo.AllActiveAuthorised AS AA

join

(

SELECT distinct address2_fax, [Country Code]

FROM dbo.AllActiveAuthorised AB

WHERE AB.dispatcheddate < CAST('20121101' as DATETIME)

AND AB.allowmail = 'Allow'

AND AB.statuscode = '1'

AND AB.brandname = 'Product...

This does something similar but without the correlated subquery.

Depends on your data as to whether it is any better

SELECT AA.[Country Code],

SUM(extendedamount) AS Amount

FROM dbo.AllActiveAuthorised AS AA

WHERE dispatcheddate >= CAST('20121101' as...

But there is a correlated subquery which could be considered a bit like a loop.

;with cte as

(

select val = 'XYZ', seq = 1

union all

select val = 'AAA', seq = 1

union all

select val = 'PQR', seq = 1

)

SELECT t.*

FROM @T t

join cte

on t.Name =...

Maybe dynamic sql for the order by

A cte with union all for each value might do it.

This was for v2000/2005 - don't know if it still applies

http://support.microsoft.com/kb/903002

It will give rows from both aliases (try it and see).

To just get those from one copy

select a.* from table1 a,table1 b

where a.column1=b.column1

Bit of an odd query though.

Maybe

; with cte as

(

select *, num = (select count(*) from @tmp t2 where t1.Postalcode = t2.Postalcode and t1.Street = t2.Street and t2.Housenumber <= t1.Housenumber and IsSeparator = 1)

from @tmp t1

)

select...

Are you sure that is causing the problem?

This should work

select convert(datetime, 'Jul 21 2010 10:15AM')

Could be due to your settings maybe.

Yep - odd. Yours wasn't visible when I typed mine in but from the post dates they are 20 mins apart.

No they are 1 min apart - sorry I didn't...