11. SQL Server view, DB snapshot

SQL Server View

What is a view?

A view is a virtual table in which the content originates from the result set of the query. It is only materialized if the index is established on the view.

Views can filter and manipulate data, rather than accessing the underlying table directly. For example, create a view that shows only a few columns of data from the source table, without giving the user permission to access the underlying table, instead granting the view access rights.

Why use a view?

1, if it is more complex multi-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 base table, sensitive columns can not be retrieved.

3. Add index to view to improve efficiency.

Create a View

1. You can use the SSMs tool to create, view-new view, and select the table selection, enter the name to save.

2. Create with T-SQL

if exists(Select *  fromsysobjectswhereName= 'Newview')--If there is a delete    Drop ViewNewviewGoCreate ViewNewview--Create a View asSelectSchool Number=ID, name=Name, age=Age fromStudentGoSelect *  fromNewview

About ORDER BY

if exists(Select *  fromsysobjectswhereName= 'Newview')--If there is a delete    Drop ViewNewviewGoCreate ViewNewview--Create a View asSelect Top TenSchool Number=ID, name=Name, age=Age fromStudentOrder  byIddesc --If you use the Order keyword, you must specify the topGoSelect *  fromNewview

Modify a View

Alter View Newview  as Select Top  -  from Student Order  by desc -- If you use the Order keyword, you must specify the top Go

Update schema

-- The view you just modified uses the *  query is all columns - - At this point modify the underlying table schema altertableadd  varchar-- The source table has updated the schema, but the view has not changed to call the system stored procedure update EXEC Sp_refreshview Studentview -- The new column will be added in this case.

Update view

Adding, deleting, and changing the view is actually modifying the source table. However, due to a number of constraints (such as a multi-table-isolated view, only the individual fields are retrieved, but when inserting some of the values on the source table are non-null will be problematic), generally do not modify the view.

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.

You must use a database snapshot on the same server instance as the source database, and you cannot delete, detach, or restore the source database.

Working with Database snapshots

-- data in the source table  Use WebDB Go Select *  from Student

if exists(Select *  fromsys.databaseswhereName= 'WEBDB_DBSS')Drop DatabaseWebdb_dbss--exists then deleteGoCreate DatabaseWebdb_dbss--To Create a database snapshot on(Name=webdb, filename='D:\WEBDB_DBSS.SS' --note the suffix. SS)--If you have multiple files that require one by one to indicate asSnapshot ofWebDBGo

--modifying source database data UseWebDBGoUpdateStudentSetName='Great' whereId= Ten --querying source database dataSelect *  fromStudentwhereId= Ten --Querying snapshot Data UseWEBDB_DBSSGoSelect *  fromStudentwhereId= Ten 

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

--Modifying the Student table schemaAlter TableWebDB.dbo.studentAdd [Address] varchar( -)Select *  fromwebDB.dbo.student--the snapshot still has noSelect *  fromwebDB_DBSS.dbo.student--Delete source database student tableDrop TablewebDB.dbo.newTable--Query database Snapshot The table still existsSelect *  fromWebDB_DBSS.dbo.newTable

Recovering data from snapshots to snapshot creation time

Restore Database WebDB  from Database_snapshot='webdb_dbss'goSelect  *from-- The modified value is changed back to select*from- - the deleted table is changed back to

The above: Any changes after the creation of the snapshot will be changed back!

11. SQL Server view, DB snapshot