I recently read an article where Itzik Ben-Gan showed that a clustered index does not guarantee the order of records for a SELECT. It had to do with an additional...

What the query does, is that it select all records where coID is not NULL and where ccbID = 100000.

For this set of records, the query then group them over...

Thank you for the feedback.

It's nice to see the query went from about 800 ms / 15500 reads down to 15 ms / 56 reads.

And as realistic as possible with index

CREATE TABLE#Original

(

RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,

coID INT,

cotID INT,

ccbID INT,

mcT CHAR(1)

)

INSERT#Original

(

coID,

cotID,

ccbID,

mcT

)

SELECTTOP 2402039

ABS(CHECKSUM(NEWID())) % 88589,

ABS(CHECKSUM(NEWID())) % 93500,

99932 + ABS(CHECKSUM(NEWID())) % 136,

CHAR(88 + ABS(CHECKSUM(NEWID())) %...

And the final test, as realistic as possible without index.

CREATE TABLE#Original

(

RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,

coID INT,

cotID INT,

ccbID INT,

mcT CHAR(1)

)

INSERT#Original

(

coID,

cotID,

ccbID,

mcT

)

SELECTTOP 2402039

ABS(CHECKSUM(NEWID())) % 88589,

ABS(CHECKSUM(NEWID())) % 93500,

99932 + ABS(CHECKSUM(NEWID())) % 136,

CHAR(88...

And for closure, this is a comparison between all queries with and without indexes.

CREATE TABLE#Sample_noindex

(

coID INT,

cotID INT,

ccbID INT,

mcT CHAR(1)

)

INSERT#Sample_noindex

SELECT1, 1, 100000, 'R' UNION ALL-- Test case 1 pass

SELECT2, 1, 100000,...

I hope this settles things!

CREATE TABLE#Sample

(

coID INT,

cotID INT,

ccbID INT,

mcT CHAR(1)

)

INSERT#Sample

SELECT1, 1, 100000, 'R' UNION ALL-- Test case 1 pass

SELECT2, 1, 100000, 'y' UNION ALL-- Test case 2 fail

SELECT3, 1, 100000,...

Marios Philippopoulos (9/4/2008)

---

SELECT

D.COID

,MIN( D.COTID )

FROM

dbo.tblName AS D

LEFT JOIN

dbo.tblName AS D2

ON

D.COID = D2.COID

WHERE

D.CCBID = 100000 AND

D.CCBID = D2.CCBID AND

D2.COID IS NULL AND

D.COID IS NOT NULL AND

D2.MCT...

Grant, it is not enough with a WHERE clause.

If the LEFT JOIN find a coID with any of it's record set to mcT = 'Y' the complete coID should be...

Try this

SELECTcoID,

MIN(cotID)

FROMdbo.tblName

WHEREccbID = 100000

GROUP BYcoID

HAVINGMAX(CASE WHEN mcT = 'Y' THEN 1 ELSE 0 END) = 0

Also add a covering index on the table like this

CREATE NONCLUSTERED INDEX IX_tblName ON dbo.tblName...

Without feedback for the first formula, the Annualized Return?

Do you have some sample data to verify with?

DECLARE@Sample TABLE

(

Period TINYINT,

Rate SMALLMONEY

)

INSERT@Sample

SELECT1, 0.0500 UNION ALL

SELECT2, 0.0450 UNION ALL

SELECT3, 0.0362 UNION ALL

SELECT4, 0.0411

SELECTEXP(SUM(LOG(POWER(1 + Rate, Period)))) - 1

FROM@Sample

Are you running the package in same database as you use when trying in QA?

A CLR procedure maybe?