Resolve SQL Server views, database snapshots _mysql

Introduction to Database Snapshots

A database snapshot, as its name shows, is a view of the database at a point in time. Is the feature introduced by SQL Server version after 2005. There are more scenarios for snapshots, but the first goal of snapshot design is to report services. For example, I need a 2011 balance sheet, which requires the data to remain in the state at 0 o'clock December 31, 2011, and this can be achieved with a snapshot. Snapshots can also be combined with mirroring to achieve the purpose of read-write separation. Let's look at the snapshots below.

What is a snapshot

A database snapshot is a read-only static view of a SQL Server database (the source database). In other words, snapshots can be understood as a read-only database. With snapshots, you can provide the following benefits:

Provides a static view to service a report

Database snapshots can be used to recover the database, which is much faster than backup recovery (I'll explain why)

Combined with database mirroring to provide read-write separation

As a backup before the test environment or data changes, such as I want to bulk import or delete data before, or to provide the tester for testing, to do a snapshot, if there is a problem, you can use the snapshot restore to the state of the snapshot established

SQL Server View

What is a view?

A view is a virtual table that originates from the query's result set. It is only materialized when an index is established on the view.

Instead of directly accessing the underlying tables, views can filter and process data. For example, create a view that shows only a few columns of data from the source table without giving the user access to the underlying table, instead granting the view access.

Why use a view?

1, if it is more complex multiple table, you can put these complex statements in the view to complete, and we just need to use a simple statement query view.

2, the protection of the underlying table, sensitive columns can not be retrieved.

3, to add an index to the view, you can improve efficiency.

Create a View

1, you can use the SSMs tool to create, view-> new View-> Select the table column-> enter the name saved.

2. Using T-SQL to create

if exists (select * from sysobjects where name = ' Newview ')--if there is a delete
  drop view Newview go
CREATE view Newview- -Create VIEW
as select number =id, name =name, age =age from student go
select * from Newview

About ORDER BY

if exists (select * from sysobjects where name = ' Newview ')--if there is a delete
  drop view Newview go
CREATE view Newvie W--Create VIEW
as select Top 10 learn number =id, name =name, age =age from student order by
ID DESC--If you use the Order keyword, you must specify
top Go
select * from Newview

Modify View

Alter VIEW Newview
as
Select Student ORDER by
ID DESC--If you use the Order keyword, you must specify the
top go

Update schema

--The view you just modified uses a * query for all columns
--At this point, modify the underlying table schema
ALTER TABLE student add mail varchar-

-when the source table has updated the schema, but the view does not change Need to invoke system stored procedures update
EXEC sp_refreshview Studentview-
-When new columns are added

Update view

The view of the increase, delete, change operation, in fact, is to modify the source table. However, due to a number of restrictions (such as the view detected by multiple tables, only individual fields are retrieved, but when the values on some source tables are inserted, there is a problem), and the view is generally not modified.

So updating the source table updates the view.

Database snapshots

A database snapshot is a read-only static view of the source database at a point in time that can be used to recover a database.

Using a database snapshot must be in the same server instance as the source database, and the source database cannot be deleted, detached, or restored.

Using Database snapshots

--Data in source table
webdb
Go
select * FROM student

if exists (SELECT * from sys.databases where name = ' WEBDB_DBSS ')
drop database WEBDB_DBSS--exists deletes go
create da Tabase WEBDB_DBSS--Create a database
snapshot
  on (name = WebDB,
  filename= ' D:\WEBDB_DBSS.SS '--note suffix. SS
)-- If there are multiple files that require one by one points as
snapshot of WebDB go

--Modify source database data use
webdb go
update student set name= ' great ' where id = ten 

--query source database data
select * FROM Stud ent WHERE id = ten 

--query snapshot data use
WEBDB_DBSS
go

Modify the source database and the snapshot database has not changed.

--Modify the student table Schema
ALTER TABLE webDB.dbo.student add [address] varchar
SELECT * FROM WebDB.dbo.student

--There is still no select * from WebDB_DBSS.dbo.student in the snapshot- 

-delete the source database Student table
drop table webDB.dbo.newTable 

- -Query database snapshots The table still exists
select * from webDB_DBSS.dbo.newTable

Recovering data from snapshots to snapshot creation time

Restore Database WebDB
from database_snapshot= ' WEBDB_DBSS '

go select * FROM WebDB.dbo.student--modified values are changed back to

SELECT * FROM WebDb.dbo.newTable--the deleted table is changed back to

The above is known: Any changes after the creation of snapshots will be changed back to the data!