sql table replicated or triggers

pmtolk1 used Ask the Experts™
We have 2 main tables on two different servers
Table A is the primary Table on primary Server
Table B is a replication of the primary Table on Secondary Server
Table C [1 day of Table B's Data]  (On same server and database-secondary server)

I want to know how/what is the best method
for creating a third table which contains only 1 day worth of Data
I will write a script which deletes all data from table C for old data
that runs every night.

How do I get the data into Table C?

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009
Why do you need the data on the replicated server ?

SELECT * from MasteTable
WHERE DateField >=CONVERT(varchar,  GetDate()-1, 112)

--to delete the master table ,use this if you dont have any relations



we need the data on the replicated server for running reports

I wrote a trigger which does an insert  currently and then a delete at night via a scheduled task
I'm not even sure why you need a table.

Why not create a view for the current day?  Unless you need to change the data and want it as a separate set of data not to impact the real data.

Think of using a view.

Otherwise, you can schedule a task to truncate the table and re-insert new data for "table c"
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.


hmmm a view ... maybe ...

When I run certain commands they take forever because the I cant use the indexes of the table and
the queries need to do a table scan to get the data I need

The table has several million rows so I thought if I kept only 1 day worth of data then it will make
those reports faster.

If I make a view it will need to query the main table and filer based on a table scan for dates, yes?

I need the most optimized solution possible because of the big data set

Thanks for the ideas guys
Maybe you should optimize a table.

I have a table that has over 500 million rows.....

You need to avoid Table Scans.  They are VERY VERY bad.

You can also index a view.


2 questions then

1 in order to make the view aren't I doing a table scan on the date range, and isn't this occurring
every time the view gets called

2 how can you avoid table scans if you need to filter on an item that is not an index
like a phone number which has multiple entries per day?

Doesn't putting the data into tables that have the data for 1 day 7 days 40 days and all days solve these problems?


fixed the problem by adding more indexes to the table
AneeshDatabase Consultant
Top Expert 2009

I would rather suggest partition the table instead of a view

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial