Great... thanks for the feedback, Magnus...

I created an 8 million row table and have tried all the scenarios you have... I cannot get it to fail in the manner which you speak of.  I think...

Yes, but not for dynamic SQL...

The error that you are getting is becaue you are using EXEC the wrong way to execute dynamic SQL... you have...

EXECUTE @STR

... and it should be...

EXECUTE...

Ah... how rude of me... forgot to post how long my code took... Using SQL Server Developer's Edition (same as Enterprise Edition), sp4, 1.8 Ghz single cpu, 1 Gig RAM,...

p.s.

Heh... looks like David and I got our rounding examples from the same source...

http://en.wikipedia.org/wiki/Bankers%27_rounding#Round-to-even_method

Heh... I've been all through this on one unbelievably long post before and then I struck gold quite by accident... Unbeknowst to most, the STR function does Banker's rounding "auto-magically"... ...

>>Wait..don't tell me both columns are NULLable, too...;-))

Heh... dunno... like I said, they cut off my ability to pry any further... but the way they do things in that particular...

Very cool... thanks for sharing!

Yes, please post the query... chances are, the criteria in the WHERE clause is what's keeping the index from being used...

Heh... you DO have a way with words!  Good thing I had a DBA with the same type of extremely dry humor you do

Can't test it, but that's SO easy!  Way to go, Peter... can't wait to get my hands on 2k5!

Haven't tested it but learned a long time ago never to mix aggragation and formatting in a single select...

 SELECT d.Cases,d.Status

   FROM

        (

        SELECT

        count(r.status_id) As...

Peter,

Now you know why I asked...   was pretty sure the Order By wouldn't do it...

Heh... that's funny... but I get what Peter is suggesting with that simple query... pass the data to the client box and let the client box work on the solution. ...

Will that put it in the order the OP requested?