This should give you the general idea:

WITH

L0 AS(SELECT 1 AS C UNION ALL SELECT 1 AS O), -- 2 rows

L1 AS(SELECT 1 AS C FROM L0...

You could do it with a second step in the job that uses robocopy in a batch file. I a lot of clients that use that. Or SCHTASKS windowing...

What is the full statement you are using for the INSERT?

Are you using SSIS to do this? It has other capabilities that could be beneficial.

Since you are on SQL...

That query pattern doesn't work (well) in SQL Server either!! 🙂 DEFINITELY need to refactor it!

You should check sys.files for EVERY database on EVERY SQL Server instance that has access to that drive before deleting.

In SQL Server 2012 the Windowing Functionality in SQL Server was VASTLY improved, including the addition of LAG and LEAD. This makes for a VERY simple and clean solution...

BI_NewBie (12/4/2014)

---

Thanks Kevin for sharing your thought.

As i said i am very confused about partitioning thing that's the reason this question came up. Yes i have done my home...

I agree Pieter - sounds like a pretty simple Windowing Function query would do the trick. I agree with your analysis of the situation too.

BI_NewBie (12/3/2014)

---

Hey Jeff!!

Thanks for your reply.

In my database, there is a transaction table whose row count is 117690142. There are index created on column but still when we fire any...

mario17 (12/2/2014)

---

Hi, all

After making myself very confy around log and diff backups, I now looking how to make it easier: how to bring that increments but only from...

So the big fat table sucked hind-tit on the table scan, eh?? Not surprising. 🙂

How many reads was it?

Iterative (logical IO) hits on the same 8K page...

So it's a seek. And actual ~= estimated rows.

Therefore my guess is that the table is so fat that the cost of scanning is less than the...

1) I don't see attached query plans

2) what are the estimated and actual rows throughout the query?

3) is the 8M-read seek from a nested loop join or some other form...

You need to filter out the records first. The optimizer can do a LOT with your query (even sometimes things you think it shouldn't do). Try something like...

This is news to me. I haven't read anywhere that reading from 'read only copies' would make changes to the primary. I assumed they would work similar to 'read only'...