Brian:

There are a couple of ways to do this. One is to left-fill the individual keys before the get added into the overall sortkey. So instead of:

100.10

30.20

you would...

That's what the brackets [] are for:

DROP INDEX

[PK_PPRD_Metrics-2147478316]

ON PPRD_Metrics

Please do not cross-post.

And, if you are really hung up on using JOin, here is another example, but really, there is no reason to beleive that this query will generally perform significantly different...

EXISTS can be faster than IN (but only sometimes), so here is an example:

Select Distinct RenewalID, RenewalReference

From Renewals r1

Where NOT EXISTS( Select *

From Renewals r2

Where r2.RenewalStatusCode...

winston Smith (8/1/2008)

---

cool, thanks, the query works,however i should have been clearer. i cant do this via joins. a subquery will not be very efficient when there are many thousands...

Try this:

Select Distinct RenewalID, RenewalReference

From Renewals

Where RenewalReference NOT IN(

Select r2.RenewalReference

From Renewals r2

Where RenewalStatusCode = 'ACT'

)

Afraid that I do not know, Grant. Maybe someone else does?

Off-hand, it sounds like it is having to expand the database....

I would suggest using BULK INSERT. Check it out in BOL (Books Online), especially the XML section.

Heh,...

You can certainly get this behavior with COUNT(ColName), but it's not supposed to happen with COUNT(*).

Glad I could help.

Sounds like a bug in SQL Server to me then, unless there is more to these commands than we are being shown.

COUNT(*) is exact and correct. SysIndexes is approximate and sometimes incorrect.