I'vs actually got this going, but it is worse!

Trying to paste HTML Code ... will it work?

This one works, in that it build dynamically & calls it with sp_executesql, but...

Mark, you are spot on.  12,000 is definately better than over 4 million, hang on.. I'll dig out the figures...

--bad
Table 'CLI_T'. Scan count 260, logical reads 1927, physical reads 0,...

Mark,

thanks for the response, two points:

1. Your code reproduces the behavior of the original query, but you couldn't know that as I didn't post it

That's what I want to do, as per an example in the BOL:

DECLARE @IntVariable INT
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)

/* Build the SQL string...

Take a look at http://www.ngssoftware.com/sqlcrack.htm

Here's my stab at it.

<

/* Created by free online sql formatter: http://www.wangz.net/gsqlparser/sqlpp/sqlformat.htm */

/*This sets up a quick test table*/

IF object_id('DJmailDetails_T') > 0

  DROP TABLE djmaildetails_t

CREATE TABLE djmaildetails_t (

  id           INT  NOT NULL  IDENTITY( 1  , 1  ),

  firstname    VARCHAR(30)  NOT NULL,

  lastname     VARCHAR(30)  NOT NULL,

  emailaddress VARCHAR(100)  NOT NULL,

  sendmail     BIT  NOT NULL  DEFAULT (0) )

INSERT INTO djmaildetails_t

           (firstname,

            lastname,

            emailaddress,

            sendmail)

VALUES     ('dave',

            'jackson',

            'me@nonworkingExample.co.uk',

            1)

INSERT INTO djmaildetails_t

           (firstname,

            lastname,

            emailaddress,

            sendmail)

VALUES     ('fred',

            'bloggs',

            'you@nonworkingExample.co.uk',

            1)

INSERT INTO djmaildetails_t

           (firstname,

            lastname,

            emailaddress,

            sendmail)

VALUES     ('joe',

            'smith',

            'him@nonworkingExample.co.uk',

            0)

INSERT INTO djmaildetails_t

           (firstname,

            lastname,

            emailaddress,

            sendmail)

VALUES     ('mike',

            'jones',

            'her@nonworkingExample.co.uk',

            1)

/*This selects from...

My favourite editor does colour prints. emEditor Professional, not free, but very cheap. I've paid for it for the macro functionality which is excellent. http://www.emeditor.com

I'm NOT...

Taking the non clustered index has got this going, albiet at the cost of increasing query times for any SP's that access that particular table.

As I said, an...

😉

The cost of the SP With parallelism & the index which causes the problem (non clustered) is 16

without parallelism - 33

without the index, with parallelism - 133! (Why we put...

No, it is the second.

Actually, we think we have found the problem, & a workround.

The title of the page at http://support.microsoft.com/default.aspx?scid=kb;en-us;814509#kb4

says it all really, "A Parallel Query with a...

Hello

I followed the above, and I can now see where I am counting rows twice. So suddenly I'm a lot more comfortable that my db is not corrupt. ...

Thanks for the response

I had a simliar one, but for completness:

select count(*) from Table_t

select count(*) from Table_t where datearchived is null

select count(*) from Table_t where (batch is not null or...

I wrote a vbs script to do this, more for recovery that anything esle. It's posted on this site at http://www.sqlservercentral.com/memberservices/updatescript.asp?Approve=y&scriptid=1151 but you will find a *new improved* ;-)...