SQL Server Database Overview

SQL Server is a database management system that runs primarily through windows services. This article mainly introduces how to perform basic operation and management of the database.

Start or stop the database service

After you complete the database installation, you need to manually check whether the installation is successful, running, and configured to start manually or automatically, depending on the situation. You can start or stop the database service in the following three ways:

(1) Using Server Manager

(2) Using SQL Server Configuration Manager

(3) using SSMs

System Database

Databases in SQL Server can be divided into the following two types of applications:

system databases: databases required to manage and maintain SQL Server

User database: user-created database

650) this.width=650; "src=" Https://s5.51cto.com/oss/201711/17/8df039c2701d5210f6397b99fe86a9be.png "title=" Yang Shufan 05.png "alt=" 8df039c2701d5210f6397b99fe86a9be.png "/>

master database: records all system-level information of the SQL Server system, logs all login accounts and system configuration settings, records all other database information (including the location of database files), and logs initialization information for SQL Server

model Database: templates for all databases, modifications to the model database will be applied to all databases created at a later time

msdb database: for SQL Server Agent scheduled alerts and schedules, all task schedules, alarms, operations are stored in the database, and the backup history of all databases is stored

tempdb database: for saving temporary objects or intermediate result sets

SQL Server database File types

The database is stored on disk as a file, with data files and transaction files. A database includes at least one data file and one transaction log file.

Master Data file: includes startup information for the database, pointing to other files in the database. Each database has only one master data file with an. mdf extension.

secondary (secondary) data files: Other data files are secondary data files except the master data file. The database can contain no secondary data files or multiple secondary data files with the extension. ndf

transaction log file: contains information that restores all transaction logs for the database. SQL statements that record all transactions, and when a data disaster occurs, the database can be recovered through a T-SQL statement logged by the transaction log. Each database has at least one transaction log file with an. ldf extension

file Stream (Filestream) data file: enables SQL Server-based applications to store unstructured data in a file system, such as documents, pictures, audio, video, etc.

Database management

(1) Create a database

(2) extending the database

(3) Shrinking database

(4) Separating the database

(5) Additional database

(6) Deleting a database

Manipulating data tables with SSMS

(1) Data type

A data type is a property of data that specifies the type of data to be saved. data types in SQL Server are divided into: precision numbers, approximate numbers, date and time, strings, Unicode strings, binary strings, and other data types. Here's a description of some common data types:

650) this.width=650; "src=" Https://s2.51cto.com/oss/201711/17/641e63b44df78a2505d78601b840f357.png "style=" float: none; "Title=" Yang Shufan 06.png "alt=" 641e63b44df78a2505d78601b840f357.png "/>

650) this.width=650; "src=" Https://s2.51cto.com/oss/201711/17/5bc5c1c9e71dab0b64572c865a0a61a6.png "style=" float: none; "Title=" Yang Shufan 07.png "alt=" 5bc5c1c9e71dab0b64572c865a0a61a6.png "/>

650) this.width=650; "src=" Https://s2.51cto.com/oss/201711/17/ec4a023bae8293d2e439c958b43d250d.png "style=" float: none; "Title=" Yang Shufan 08.png "alt=" Ec4a023bae8293d2e439c958b43d250d.png "/>

(2) Default value

If you insert a row without specifying a value for the column, the column uses the default value. The default value can be any value that evaluates to a constant

(3) Check constraints

Check constraints can enforce domain integrity, similar to foreign key constraints, by restricting the values that are acceptable for the column.

This article is from the "Yang Shufan" blog, make sure to keep this source http://yangshufan.blog.51cto.com/13004230/1983373

SQL Server Database Overview