You probably want to do the grouping in the inner query rather than the outer query. Also, can limit parameter values to 1-4 in the inner query itself.

SELECT a.BoxId,...

below86 (4/23/2014)

---

Lynn Pettis (4/23/2014)

---

below86 (4/23/2014)

---

Wouldn't it be better to use the BETWEEN with these dates?

WHERE (ath_postype = 'NTC' OR ath_postype='NTD')

AND ath_postdate BETWEEN '2013-01-01 00:00:00.000' ...

Btw, you should < on date/datetime, not <=, as below. Otherwise you risk missing rows, especially if the data type of the underlying column changes (to datetime from smalldate,...

So how on earth are you supposed to properly tune a table when you can't change its indexes?? :w00t:

Edit: Moved the emoticon after the ??.

Lowell (4/21/2014)

---

this query would be an order of magnitude faster (at least!) for getting row counts per table.

something like this is what you'd wnat to wrap with sp_msForEachdb:

...

It depends. If you reduce the log file size and it grows back to the original size again, you've just wasted resources, and re-fragmented the file as well.

Therefore, unless...

Bouke Bruinsma (4/21/2014)

---

A question was brought up about TempDB best practices. There is a lot to be said about this. Not knowing your entire physical hardware platform, keep in mind...

case

when pwdLastSet0 is null then 'Never'

when pwdLastSet0 = '0' then 'Must change at next logon'

ELSE convert(varchar(10), DATEADD(mi,(cast(pwdLastSet0 as bigint) / 600000000) - 157258080

+ DATEDIFF(Minute,GetUTCDate(),GetDate()),0) , 101) + ' '...

IF OBJECT_ID('tempdb.dbo.#row_counts') IS NOT NULL

DROP TABLE #row_counts

CREATE TABLE #row_counts (

db_name nvarchar(128) NOT NULL,

table_name nvarchar(128) NOT NULL,

...

Maybe this:

case

when pwdLastSet0 is null then 'Never'

when pwdLastSet0 = '0' then 'Must change at next logon'

ELSE convert(varchar(10), cast (DATEADD(mi,(cast(pwdLastSet0 as bigint) / 600000000) - 157258080

+ DATEDIFF(Minute,GetUTCDate(),GetDate()),0) as varchar(32)), 101)...

Change Tracking won't do what you've described. You would have to use CDC. Please note that CDC is only available on Enterprise Edition.

Sqlraider (4/21/2014)

---

ScottPletcher (4/18/2014)

---

Try this:

SELECT

emp.eid, emp.agentid,

info.emailid,

mail.EmailAddr, mail.LName, mail.FName

...

Try this:

SELECT

emp.eid, emp.agentid,

info.emailid,

mail.EmailAddr, mail.LName, mail.FName

FROM employees...

sql_variant would be a good data type for this.

You don't want to modify the target table structure, as that will have lots of overhead.

Does the target table have a unique key column(s)? If not, can you add...