Edit: Based on the index missing and index usage stats you posted (thanks!) I would say: /Edit.

JID, not DID, seems like the best clustering index key for SELECTs. We...

Jeff Moden (4/29/2014)

---

ScottPletcher (4/29/2014)

---

For only 5 chars, I wouldn't bother will all the CTEs and related folderol.

Why not just:

SELECT

ISNULL(CAST(ASCII(SUBSTRING(data, 1, 1)) AS varchar(3)), '') +

...

I believe OP said that Enterprise Edition was not an option.

Eirikur Eiriksson (4/29/2014)

---

ScottPletcher (4/29/2014)

---

Then expand the initial code to handle 10 bytes (or 20 if you're that worried about it). Yes, I'd be willing to revisit code if the...

Then expand the initial code to handle 10 bytes (or 20 if you're that worried about it). Yes, I'd be willing to revisit code if the known 5 bytes...

For only 5 chars, I wouldn't bother will all the CTEs and related folderol.

Why not just:

SELECT

ISNULL(CAST(ASCII(SUBSTRING(data, 1, 1)) AS varchar(3)), '') +

...

What indexes does SQL report are missing? What is the usage of existing indexes on that table?

There's a reasonable chance that the table should be clustered by [DID] rather...

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) + ' '...