rwitt 95744 (5/7/2014)

---

I think placing this in my test data script will resolve the issue (or rather, cover it, for both situations):

DECLARE @DELETEDFROM_<tablename> bit = 1

IF (SELECT IDENT_CURRENT('<tablename>') = 1...

Jeff Moden (5/6/2014)

---

In that case, here we go… let’s test the “Original” code, Scott’s code, and my humble submittal. With any luck at all, someone will...

I use CONTEXT_INFO() to control triggers in those cases. If you disable the trigger, of course other deletes also won't process the trigger, when you might have wanted them...

No, don't think any other settings affect that.

Is there maybe something in the process where that table is truncating rather than deleting? Perhaps a missing foreign key constraint causing...

No reason for the overhead of reading a table just for that. Or to calculate, say, the first Tuesday or last Friday, etc., of a month. Calendar tables...

I would never use a table variable for this -- if something goes wrong after the DELETEs, but before the output has been processed, the data is gone, with no...

I suggest making your code as self-documenting as possible; then you need fewer additional comments. Naturally that means avoiding variable "names" like "E", "N" and so on. Example...

The description is vague. Is "new environment" a different server? Is it a different edition of SQL? Are the db files on SAN?

If, for example, the dbs...

As others have noted, never create indexes just because dta recommends them.

More importantly, though, the single most important performance factor is first getting the best clustered index. (Barring some...

SELECT TOP (1000) --remove after testing!

t.OPENDATE, t.ASSNDATE,

CAST(minutes_diff / 60 AS varchar(3)) + ':' +

...

Steve-0 (5/5/2014)

---

Hi Everyone thanks for the replies.

For the first 2, I'm not using static dates as per your queries.

Scott, your query is just casting values for actual field...

SELECT

OPENDATE, ASSNDATE,

CAST(minutes_diff / 60 AS varchar(3)) + ':' +

RIGHT('0' + CAST(minutes_diff % 60...

DECLARE @date_with_month datetime

SET @date_with_month = GETDATE()

;WITH

cteDays AS (

SELECT 1 AS day# UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL

SELECT...

I'd try to do this in the most straightforward way possible. Therefore, I'd go with db snapshots unless the modification activity on the dbs during the ETL process was...

Look at existing index usage and missing index stats. The first thing is to verify that you have the best clustered index. Once that is done, you can...