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...

It won't?? That string doesn't look like it starts with a letter to me ......

DECLARE @parameter int

SET @parameter = 2 --NULL=ALL; spec. value = that value

SELECT

col1ID,

CASE WHEN COUNT(DISTINCT Col2ID) = 1 AND

...

I think this query will give you what you need in separate rows. If so, let us know, and we can pivot it to a single row.

DECLARE @control_date datetime

SET...

I suggest:

1) be sure to pad the month and day values so that the final format is a full yyyymmdd.

2) apply column names to make the code easier to understand...