SQL Server 2008 from getting started to mastering 20180627

The theory of database paradigm

The paradigm theory is to set up the rules of the database which is redundant and the smaller structure is reasonable. Relationships in a relational database must meet different paradigms. Currently there are six paradigms for relational databases: first paradigm (1NF), second Normal (2NF), third Normal (3NF), BCNF, Fourth Normal (4NF), and V (5NF)

First Paradigm

Any property of an object cannot be split, each property has and has only one value, that is, there are no duplicate rows, and there are no duplicate columns.

Second Paradigm

On the basis of the first paradigm, all non-primary properties are required to be fully related to the primary attribute. Assuming that property 1 and property 2 are the primary property, property 3 is a non-primary property, and if property 1 or Property 2 can uniquely determine property 3, it does not conform to 2NF, only (Attribute 1+ property 2) can uniquely determine attribute 3 (the implementation that facilitates database-based operations) conforms to 2NF

Third Paradigm

On the basis of the second paradigm, it is required that other fields other than the primary key are not relevant and there is no dependency. For example, a table with a non-main property of 1 = Property 2-Property 3, the table does not meet the third normal form. (Do not store data that can be easily calculated in the database)

BCNF

On the basis of the third paradigm, all fields in the table, including the primary key, are not correlated and there is no dependency. That is, the primary property is not dependent on the primary attribute.

The IV Paradigm

There are no many-to-many relationships in the table. If A and B are 1:n relationships, A and C are 1:n relationships, and B and C are independent of each other, then the Quaternary paradigm is not satisfied.

Model Five

On the basis of the fourth paradigm, it can be decomposed into smaller tables. Re-establish the original structure from the final structure.

Transact-SQL row constructors

Example: inserting multiple rows of data at once with an INSERT statement

CREATE TABLE a(    Column1 NVARCHAR(max),    Column2 NVARCHAR(max));GoINSERT INTO a VALUES(‘1’,’1’),(‘2’,’2’),(‘3’,’3’);

Create a new login and user name with stored procedures

Create the login name huyan1, password 111111, default database test, switch to the test database, create the login huyan1 in the test database user Hy1

EXECUTE sp_addlogin ‘huyan1’,’111111’,’test’USE testEXEC sp_adduser ‘huyan1‘,‘hy1‘

Or use sp_grantdbaccess to create a database user name that is the same as the login name.

EXEC sp_addlogin ‘hy2‘;GOUSE testEXEC sp_grantdbaccess ‘hy2‘

Note: Only the login name is created and the database user name is not created, and the login does not log on to the database properly. You can use the SQL statement to create a database user name, or right-click the login-Properties-user mapping to check the database. The Create and delete database user name statements must be executed under the database. User name hy1 default permissions public.

To delete a newly created logon name:

EXECUTE sp_droplogin ‘huyan1’

To delete a newly created user name:

USE test;EXECUTE sp_dropuser ‘hy1’

User Rights main statement

USE test；GRANT SELECT,UPDATE,DELETEON CustomersTO huyan1REVOKE SELECT,UPDATE,DELETEON CustomersTO huyan1

NOTE: The GRANT statement must be executed under the target database.

Prerequisites for granting user rights

(1) Create login name huyan1, password 111111, default database Test

EXCUTE sp_addlogin ‘huyan1’,’111111’,’test’

(2) Create user Hy under the target database test of the Huyan1 login name

USE test；CREATE USER hy FOR LOGIN huyan1 WITH DEFAULT_SCHEMA=test；

or right-click on the huyan1 login, tick the test database in the properties-user map, the system will automatically add the Huyan1 user under the test database

granting permissions

USE test；GRANT SELECT,UPDATE,DELETEON CustomersTO hy

Note: Hy here refers to the database user name rather than the login name.

Revoke permissions

Revoke user hy's Select,update,delete permissions in the Customers table

USE test;REVOKE SELECT,UPDATE,DELETEON CustomersTO hy

Deny permission

Deny user hy Delete permission in the Customers table

USE test;DENY DELETEON CustomersTO hy

Note: The difference between revoke and deny is that when a user right is denied, the permission cannot be inherited by its group or role membership, and the permission is revoke and can be inherited and granted.

Create a role and assign it to a user

Under the test database, create a role p_test with the stored procedure, with the owner of the role Hy. Grant all permissions to the Customers table for the p_test role. Assign the member user Hy to the role p_test with the stored procedure.

USE testEXEC sp_addrole ‘p_test’,’hy’;GRANT ALL ON Customers TO p_test;EXEC sp_addrolemember ‘p_test’,’hy’;

Note: Role owner Hy is not a role member. Using the exec sp_addrole ' p_test ', ' hy ' statement to create a role p_test and set the owner to Hy does not mean that Hy is a member of the P_test and has the privileges of the p_test role.

Remove members for role p_test HY

EXEC sp_droprolemember ‘p_test’,’hy’;

Server role

Bulkadmin: Allows the BULK INSERT statement to be run to insert data into the database from a large number of text
DBCreator: Allows creation, modification, deletion and restoration of any database for assistant DBAs and developers
Diskadmin: Allows you to manage disk files, such as mirrored databases and add backup devices, for assistant DBAs
Processadmin: Allows multitasking management, multiple processes can be multi-tasking, or the process can be deleted
Securityadmin: Security administrator, managing logins and their properties
ServerAdmin: Service administrator, changing server configuration options and shutting down the server
Setupadmin: Managing Linked Servers, controlling the startup of stored procedures
SysAdmin: Permission to perform any task, only for database administrators

Database role Standard Role

Allows a user to create a role with a single permission. If you create a role called user, allow users to insert,select,update the specified tables in the database, and do not allow other tasks.

Application roles

Allow users to create password protection for specific applications

Pre-defined database roles

These roles are built-in and cannot be changed permissions
(1) DB_Owner: You can do all the things other roles can do, and you can do some administrative operations
(2) Db_accessadmin: You can specify who can access the database by adding or removing users
(3) Db_securityadmin: You can modify role memberships and administrative permissions
(4) Db_dlladmin: You can run all DLL commands in the database, create modifications and delete database objects without having to browse their data
(5) Db_backupoperator: Backing Up the database
(6) Db_datareader: Read all data from all users ' tables
(7) Db_datawriter: You can add changes to delete data from all tables in all users
(8) Db_denydatareader: Cannot read data from any user's table
(9) Db_denydatawriter: Cannot add change delete operation to any user's data in any table
Public: Each database user is in the public role and the user is not granted the right to inherit public permissions. The role cannot be deleted.

System Database master Database

Stores the login name and user ID of the role, system configuration, database name and initialization information, is the most important database.

Model database

Some of the predefined criteria for creating a database, such as the initial size of the database, a specific set of information, and so on, are the basis for the tempdb database.

tempdb database

A temporary database that was created when SQL Server2008 was started and lost when SQL Server2008 was closed. The primary store is the temporary tables and temporary stored procedures established by the user.

msdb database

Provide SQL server2008 with the necessary information to run the job.

Database files and log master data files

A database corresponds to a master data file with an. mdf extension that contains startup information for the database and other files, and user data and objects can be stored in the master data file or in a secondary data file.

Secondary data files

Optionally, a user-defined file that stores user data with a. ndf extension that, when the database exceeds the maximum limit for a single Windows file, can use a secondary data file to spread the data across multiple disks, allowing the database to continue to grow.

Transaction log files

The log information that is used to recover the database is saved, and each database must have at least 1 log files. The extension is. ldf.

Database snapshot (db snapshot)

A database snapshot is a static, read-only view of the source database that is on the same server instance as the source database, and that the database snapshot is updated as the source database is transactionally consistent with the source database. Multiple database snapshots can exist for a database.

Advantages

(1) for reporting purposes. A client can query a database snapshot to write a report with the data when the snapshot is created.
(2) used to save historical data to generate reports. A database snapshot retains historical data at a point in time, allowing the user to generate reports on historical data at that point in time.
(3) Use a database snapshot with a mirrored database to access the mirror database and release resources on the principal database.
(4) The impact of data protection against management errors
(5) If a user error occurs on the source database, you can restore the database to the state when the database snapshot was created.
(6) Manage the test database. Create a database snapshot of the test database before the first round of testing begins. The database snapshot can be used to restore the database to its pre-test state at the end of the test to restart the test.

Note: Database snapshots are related to the source database and cannot be recovered from offline and corrupted databases, and cannot be substituted for backup and restore. All Recovery models support database snapshots.

Limitations of database Snapshots

(1) During the existence of a database snapshot, the source database cannot be detached, restored, and deleted, but can be backed up.
(2) The copy-on-write operation is performed on the snapshot each time the source database is updated, and the I/O performance of the source database is affected.
(3) You cannot delete files from the source database or from any snapshot.
(4) must be created and saved on the same server instance as the source database.
(5) relies on the source database, but not on redundant storage, to prevent disk errors or other types of corruption.
(6) When the snapshot update runs out of disk space or encounters other errors, it becomes a snapshot and must be removed.
(7) The snapshot is read-only and cannot be upgraded, so it is not available after upgrade.
(8) Cannot create a snapshot of the model database, the tempdb database, the master database.
(9) cannot attach, detach, backup, restore Database snapshot, cannot delete files in database snapshot.
The only way to create a database snapshot is to use Transact-SQL statements. and Microsoft SQL Server Management Studio does not support database snapshots.

To create a database snapshot

CREATE DATABASE test_snapshot_201806271505 ON(    NAME=test,    FILENAME=‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test_snapshot_201806271505.ss‘)AS SNAPSHOT OF test;GO

In the preceding code, test_snapshot_201806271505 is the snapshot name, and test in Name=test is the logical name of the source database, which can be right-click Database-Properties-File view. FileName fills in the sparse file storage path for the snapshot, where TEST_SNAPSHOT_201806271505.SS is the name of the sparse file, and the last Test is the source database name (not the logical name).

Note: Running the above statement in SQL Server Management Studio actually creates a db snapshot that did not run successfully.
Msg 1844, Level 16, State 1, line 1th
The database Snapshot is not supported by Express Edition with Advanced Services (64-bit).

Recovering a Database snapshot

USE masterRESTORE DATABASE test FROMDATABASE_SNAPSHOT=‘test_snapshot_201806271505‘;GO

In the preceding code, test is the source database name, and test_snapshot_201806271505 is the snapshot name.

Deleting a database snapshot

DROP DATABASE test_snapshot_201806271505;

Note: When recovering a database with a database snapshot, the source database cannot have more than one snapshot, nor can there be any read-only or compressed filegroups, and files that are now offline when the snapshot is created.

Create and modify database creation database

CREATE DATABASE 教务管理系统ON(    NAME=‘教务管理系统_DATA‘,    FILENAME=‘E:\教务管理系统_DATA.mdf‘,    SIZE=5MB,    MAXSIZE=10MB,    FILEGROWTH=5%)LOG ON(    NAME=‘教务管理系统_LOG‘,    FILENAME=‘E:\教务管理系统_LOG.ldf‘,    SIZE=2MB,    MAXSIZE=10MB,    FILEGROWTH=1MB)

Modify Database name

ALTER DATABASE 教务管理系统 MODIFY NAME=database_name;

or use stored procedures

EXEC sp_renamedb ‘database_name‘,‘School_MIS‘;

Modify Database Size

You can increase the database capacity by adding a secondary database file to the database

ALTER DATABASE School_MISADD FILE(    NAME=‘School_MIS1‘,    FILENAME=‘E:\School_MIS1.mdf‘,    SIZE=3MB,    MAXSIZE=10MB,    FILEGROWTH=10%)

The new minor file is named School_mis1 in the code above, the storage path is E:\School_MIS1.mdf, the initial size is 3MB, the maximum growth limit is 10MB, and the autogrow increment is 10%.

View database Status

(1) To view the database status by querying the State_desc column values of the sys.databases view in the master table, the query condition is the database name

SELECT state_desc FROM sys.databasesWHERE name=‘test‘

(2) View status by using the Status property of the DATABASEPROPERTYEX () function

USE testGOSELECT DATABASEPROPERTYEX(‘test‘,‘STATUS‘) AS ‘当前数据库状态‘;

SQL Server 2008 provides several ways to view database information, such as viewing database file information using sys.database_files in the master database, sys.filegroups viewing database group information, Sys.maste_ Files View the database file basic information and status information. In addition to catalog views and functions, you can view the space used and reserved by a stored procedure sp_spaceused.

USE testGOEXEC sp_spaceused;

Using sp_helpdb to view database basic information

EXEC sp_helpdb ‘test‘;

Detaching and attaching a database detach database

Remove the database from the SQL Server instance, but leave the database intact in its data files and transaction log files, which you can then use to attach the database to any instance of SQL Server.
The database cannot be detached in the following situations:
Databases that have been replicated and published cannot be detached, and if the databases have been published, they must be disabled by running sp_replicationdboption to be detached.
If a snapshot exists in the database, all snapshots must be deleted before they can be detached.
The database is mirroring in a database mirroring session.
The database is in a state that can not be detached.
The database is a system database that cannot be detached.

Detach Database command

USE masterEXEC sp_detach_db ‘test‘;

Attaching a database

Attach the database to a different server instance using the detached data file and transaction log file. You can attach replicated and detached databases.

Attach Database command No log attach database command

CREATE DATABASE test ON(    FILENAME=‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test.mdf‘ )FOR ATTACH_REBUILD_LOG;

Multiple. mdf,.ndf,.ldf file Attach database commands

CREATE DATABASE School_MIS ON(FILENAME=‘E:\教务管理系统_DATA.mdf‘),(FILENAME=‘E:\School_MIS1.mdf‘),(FILENAME=‘E:\test_data.ndf‘)LOG ON(FILENAME=‘E:\教务管理系统_LOG.ldf‘)FOR ATTACH;

Stored Procedure Attach Database command

EXEC sp_attach_db @dbname=‘School_MIS‘,@filename1=‘E:\教务管理系统_DATA.mdf‘,@filename2=‘E:\School_MIS1.mdf‘,@filename3=‘E:\test_data.ndf‘,@filename4=‘E:\教务管理系统_LOG.ldf‘;

SQL Server 2008 from getting started to mastering 20180627