Another method

SELECT TOP(1)COUNT(*) OVER () AS Items

FROMdbo.tblHhiQuotes

WHEREsGlDate <= '28 Oct 2010'

AND sEffectiveDate BETWEEN '01 Nov 2010' AND '30 Nov 2010'

AND cTransType = 'FQ'

GROUP BYvSurname,

cPostcode,

cCoverType

SELECTSUM(1) AS Items

FROMdbo.tblHhiQuotes

WHERE sGlDate <= '28 Oct 2010'

AND sEffectiveDate BETWEEN '01 Nov 2010' AND '30 Nov 2010'

AND cTransType =...

3 minutes 20 seconds for "15 record table to 31100 record table"-join is WAY to long if you ask me.

Do you have the same amount of memory? Same storage devices?

Are...

Per se, the queries don't "break", they just happened to be slower in your case.

The internal query optimizer has changed 3 times since SQL Server 2000. Changes were made in...

What if you write a better performing query?

SELECTreg.registrarId,

reg.ianaId,

reg.registrarName,

reg.clientId,

reg.[enabled],

txn.balance,

reg.alertBalance,

reg.disableBalance,

et.enabledTypeName

FROMRegistrar AS reg

INNER JOINEnabledType AS et ON et.enabledTypeCode = reg.[enabled]

LEFT JOIN(

SELECTregistrarId,

ISNULL(balance, 0) AS Balance,

ROW_NUMBER() OVER (PARTITION BY registrarId ORDER BY transactionid...

I can take this on.

Just recently posted some here http://www.sqlservercentral.com/Forums/Topic1010974-338-1.aspx

and blogged a lot of them here

http://weblogs.sqlteam.com/peterl/archive/2009/11/03/Get-the-Nth-weekday-of-any-arbitrary-period.aspx

http://weblogs.sqlteam.com/peterl/archive/2009/06/18/How-to-get-the-Weekday-and-Nth-from-a-date.aspx

http://weblogs.sqlteam.com/peterl/archive/2009/06/18/Extended-Get-Nth-Weekday-of-period.aspx

Let me know what you think.

Bummer... Late for the party, again...

http://weblogs.sqlteam.com/peterl/archive/2009/03/18/A-general-approach-to-sort-different-dateformats-correctly.aspx

Pulling ranks, huh? 😀

Email me the full sample data to peso(at)developerwork(dot)net.

Post some more sample data to test with.

Also, post for several customers (ie add customer column) to the sample data.

Meanwhile, test this...

-- Prepare sample data

DECLARE @Sample TABLE

...

;WITH ctSource(Data)

AS (

SELECT1

FROMtblHhiQuotes

WHEREsGlDate <= '28 Oct 2010'

AND sEffectiveDate BETWEEN '01 Nov 2010' AND '30 Nov 2010'

AND cTransType = 'FQ'

GROUP BYvSurname,

cPostcode,

cCoverType

)

SELECTSUM(Data)

FROMcteSource

Does it really matter since the clustered key is an ever-increasing identity value?

DECLARE@Sample TABLE

(

PageID INT

)

INSERT@Sample

VALUES(1), (2), (3), (4), (51), (53), (54), (55)

;WITH cteSource(PageID, RecID)

AS (

SELECTPageID,

ROW_NUMBER() OVER (PARTITION BY PageID / 50 ORDER BY PageID) AS RecID

FROM@Sample

)

SELECTPageID

FROMcteSource

ORDER BYRecID,

PageID

If you do, you can...

See "formatting stuff" here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254