Database snapshots, user-defined functions, and computing Columns

Database snapshots, user-defined functions, and computing Columns
1. Database Snapshot
Database snapshots are the State at which a database is saved at the moment of the snapshot. snapshot and backup are different in principle, but the functions are the same, that is, the time when data can be restored to backup. the Snapshot principle is to create a database pointer. When the original database does not change, the snapshot does not occupy space, and the database changes, before the change, the modified data page is copied to the snapshot file, and then the original data page is modified. obviously, the advantage of doing so is that it takes less space than the backup database. snapshots are read-only. You can use them as databases in SQL statements:
Use snap1; -- use a snapshot
Select * From Table1
Of course, you can also implement the data restoration function. When importing data, you can select the data source as a snapshot.
Snapshot creation does not seem to be possible in managementstudio by clicking the mouse, only through SQL statements:
Create Database snap2
On
(Name = db1,
Filename = 'C:/ABC. MDF ')
As snapshot of db1
Here snap2 is of course the snapshot name, while name = db1 here db1 refers to the logical name of the data file of the database to be snapshot.
You can right-click a database and choose Properties from the logical name of the database.
Filename = '...' refers to the location where the snapshot file is stored. The last db1 refers to the database to be snapshot.
Note: A database cannot be deleted after it is taken a snapshot. to delete a database, you must first Delete All snapshots of the database.
2. User-Defined Functions
You can define your own functions in database> programmable> function> scalar function.
Right-click the "new scalar value function" and call the template of the new function. Press Ctrl + Shift + m to assign values to parameters in the template. Then, write your own SQL statement in the middle of begin end.
For example, if we have a function getage () that calculates the age by birthday, the code written is as follows:
Create Function [DBO]. [getage]
(
@ Birthday datetime
)
Returns int
As
Begin
Declare @ result int
Declare @ now datetime
Set @ now = getdate ();
Select @ result = datediff (YY, @ birthday, @ now)
Return @ result
End
After writing the code, press F5 to save the compiled function to the database. it is also very easy to call a user-defined function. Just like the general function usage, it must be followed by the architecture name, that is, it must be written as DBO. getage, instead of getage. let's create a new query to test it:
Select DBO. getage ('2014/1/10 ')
22 is returned. Haha, the function is successfully written.
3. Calculate Columns
When designing a database table, a special column does not fill in any design type. You cannot change the value of this column. This is the calculation column.
The calculated column value is calculated by using a certain function formula and so on, taking the value of another or multiple columns as the input value.
When opening a table or creating a new table, there is a "calculated column specification" item under the column attribute. Fill in the required formula in the "formula" to complete the design of the calculated column.
For example, if we have a table with the field birthday (datetime type), we will create a new column named age and fill in the formula (isnull ([DBO]. [getage] ([birthday]), (0) and then save it. when you open the data in the table, you can see that all the age fields are displayed.
You cannot directly write complex logic in a computing column. Generally, You need to combine custom functions and computing columns to complete various complex logics.