This just another possible way :

Select lt.*

FROM

(Select avatarID, episode From #clients where [status]='add') as lt

Left join

(Select avatarID, episode From #clients where [status]='discharge') as rt

ONlt.avatarID = rt.avatarID

and lt.episode=rt.episode

WHERE rt.avatarID...

This is one thought that I have >

Assuming there is a realiable PK field in the table:

Bring in the 70 million rows to a staging area locally on the destination...

I would have to say resist the temptation to think about space first and the simple layout of a denormalized design second. You are thinking like a designer of a...

I am wondering about the three part naming convention and its substitution with a synonym. I envision a scenario where the code reads like

/* block 1*/

SELECT ...

FROM ProductionDB_test.dbo.tableA

I instead...

I do find the comment that a previous post made on the lack of transparency of synonyms within reporting tools (only show tables and views) a bit unsettling. I also...

aj2red (6/28/2016)

---

Control-H?

Two things:

1) Promoting objects - things to be checked every time something gets moved.

2) copying production to test or dev.

let's say you have 200 objects in production.

You...

aj2red (6/24/2016)

---

If you live in a universe where you control database and scheming naming conventions, then I imagine you don't need to synonyms.

However, I don't.

For example:

We have written multiple interfaces...

Thanks for the article. Very creative approach to make use of this hidden and under utilised tool. Documentation is among the last thing people think of when creating their objects...

To add to the post above ... somewhere I've read that Oracle databases apply triggers on a row by row basis, or at least can. So I was thinking if...

Sean Lange (3/8/2016)

---

MMartin1 (3/8/2016)

---

SELECT @ORDER_NUMBER = i.ORDER_NUMBER, @ID = i.ST_ID, @Name = i.FULL_NAME, @Timestamp = i.ORDER_DATE

from inserted i

This above will fail whenever the application inserted more than one row...

Sergiy (3/8/2016)

---

Mvs2k11 (3/8/2016)

---

Thanks ..I was figuring out the data which has millions of records however I have the data which have mentioned..

What is the solution.. when I put max recursion...

drew.allen (3/8/2016)

---

MMartin1 (3/8/2016)

---

Going deeper with the CROSS APPLY suggestion :

Consider something like this >

SELECT SUM(someNumberColumn) / (SELECT SUM(someNumberColumn) FROM Table) AS westCoastSalesAsA%OfTotal

FROM Table

WHERE someFilterColumn= 'west coast'

Could be...

This is a case where you can run into a infinite loop if you had something like

PARENT CHILD

-------- -------

1 ...

Going deeper with the CROSS APPLY suggestion :

Consider something like this >

SELECT SUM(someNumberColumn) / (SELECT SUM(someNumberColumn) FROM Table) AS westCoastSalesAsA%OfTotal

FROM Table

WHERE someFilterColumn= 'west coast'

Could be done with...

From here what you should do is is create columns in the report (just to audit) that list the return values of these functions to see what actually got returned...