Taking a higher-level view, what's most important for performance of very large tables is making sure the clustered index is based on the best column(s). That can often eliminate...

Are you just trying to determine the relative Saturday# within the month? If so, there are methods with vastly less difficulty and overhead.

Does the read query use "WITH (NOLOCK)"?

Is snapshot isolation in use?

Minor correction:

t.TransactionDate >= 'Apr 2014' AND

t.TransactionDate < DATEADD(mm,1,'Apr 2014') --"<" rather than "<="

If you basically just want to copy/clone one table but without the identity, why not just "cancel" the identity as part of the SELECT ... INTO the new table? ...

Yes, because SQL DELETE | INSERT | UPDATE...

Verify that those indexes are actually being used; an estimated query plan is enough for

You...

If you are using a native SQL account, you can check in:

sys.server_principals

There is no date specifically for password changes, but the modify_date does get reset when the password is changed.

Jeff Moden (6/13/2014)

---

Jake Shelton (6/12/2014)

---

Hi all,

I'm looking for the most time-efficient way to backup db's to a local drive and then move the .bak files across to a new server,...

I deferred converting the date to character until the very end to avoid having to convert every date and to avoid grouping on a char/varchar.

SELECT right('0' + cast(month(ClosingMonth) as...

You also need to get rid of all the ISNULL()s in the WHERE clause. You should follow this simple rule:

Never use ISNULL() in a WHERE clause.

The quick way would be switch the "=" and "<" in the current code 😉 :

delete table

from table t join

(select col1,col2,col3,col4, from table

where convert(varchar,insertdate,112) = convert(varchar,getdate(),112)

) y

on t.col1...

I also suggest considering average wait time as opposed to only using total wait time:

select

case when waiting_tasks_count = 0 then 0 else

...

It deletes only today's date, but only if there are matching records before today.

Apparently, based on the code, only the earliest matching row, across the 4 columns compared, is needed...

It deletes rows with the current date/day if the table contains any row with a date before today's date where the 4 column values match today's 4 column values.

Edit: btw,...