SQL Snapshots

SQL snapshots are a recent read-only 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 source database 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 in data replication, reporting, maintaining historical data, and dynamically replicating data between distributed databases.

Create Snapshot in SQL

The CREATE SNAPSHOT syntax is used to create a snapshot in SQL.

To create a simple snapshot based on a single table or a straightforward SELECT query from a single table, you can use the following syntax:

CREATE SNAPSHOT snapshot_name AS
SELECT * FROM your_source_table;

Create Database Snapshot in SQL

To create a database snapshot in SQL Server, can use the following Transact-SQL syntax:

CREATE DATABASE database_snapshot_name
ON (NAME = logical_file_name, FILENAME = 'os_file_name')
AS SNAPSHOT OF source_database_name;

Types of SQL Snapshots

There are two types of snapshots available in SQL

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 of Simple Snapshot:

CREATE SNAPSHOT emp_snap 
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 of Complex snapshot:

CREATE SNAPSHOT sampleSnps1 
AS SELECT student.rollno, student.name 
FROM student
UNION ALL
SELECT new_student.rollno, new_student.name 
FROM new_student;

Advantages of Snapshots in SQL

• 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 of Snapshots in SQL

• 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 number of snapshots increases, disk space becomes problematic.

Applications of Snapshots

• 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.

Conclusion

Snapshots in SQL Server offer a consistent, read-only view of the database at a specific point in time. They are useful in reporting and analytics without impacting the live database. They are space-efficient due to their copy-on-write mechanism.

We have discussed about SQL snapshots with examples and explained how to create simple and complex snapshots in SQL. We also learnt about their applications, advantages and disadvantages.