Open In App
Related Articles

SQL Snapshots

Like Article
Save Article
Report issue

Snapshot is a recent copy of the table from the database or a subset of rows/columns of a table. The SQL statement that creates and subsequently maintains a snapshot normally reads data from the database residing server. A snapshot is created on the destination system with the create snapshot SQL command. The remote table is immediately defined and populated from the master table. These are used to dynamically replicate data between distributed databases. Two types of snapshots are available.

  1. Simple snapshots
  2. Complex snapshots
Simple snapshot : In simple snapshot, each row is based on a single row in a single remote table. This consists of either a single table or a simple SELECT of rows from a single table. Example –
as select * from emp;
Complex snapshot : In complex snapshot, a row may be based on more than one row in a remote table via GROUP BY operation or result of Multi-Table Join. This consists of joined tables, views, or grouped and complex SELECT statement queries. Example –
AS SELECT student.rollno, 
FROM student
SELECT new_student.rollno, 
FROM new_student;
Advantages :
  • Response time is improved when local read-only copy of table exists.
  • Once snapshot is built on remote database, if node containing data from which the snapshot is built is not available. Snapshot can be used without need to access the unavailable database.
  • Ease network loads.
  • Data subsetting.
  • Disconnected computing.
  • Mass deployment.
Disadvantages :
  • Snapshots are not reachable when primary database goes offline.
  • It does not support full text indexing.
  • Snapshot runs out of disk if data changes frequently faster.
  • As no.of snapshots increases, disk space becomes problematic.
Applications :
  • Protects data.
  • Maintains history of data.
  • Used in testing application software.
  • Used in data mining.
  • Recovers data when information is lost because of human error or corruption of data.

Last Updated : 23 Oct, 2020
Like Article
Save Article
Share your thoughts in the comments
Similar Reads