Shulou(Shulou.com)06/01 Report--

I. the data storage structure of SQL Server

SQL Server is a database management system that needs to store high-capacity data in an efficient way. To better understand how SQL Server handles data, you need to understand the storage structure of the data.

1. File type

The database is stored on disk in units of files and consists of data files and transaction log files. A database should contain at least one data file and one transaction log file.

Master data file: the master data file contains the startup information of the database and points to other files in the database. Each database has one master data file (one and only one). The recommended file extension is .mdf.

Secondary (secondary) data files: all data files except the primary data file are secondary data files, some databases may not contain any secondary data files, while others contain multiple secondary data files, the recommended file extension for secondary data files is .ndf

Transaction log files: transaction log files contain information to recover all transactions in the database. There is at least one transaction log file in each database, and of course there can be multiple. The recommended file extension for transaction log files is .idf

File stream data files: enable SQL-based applications to store unstructured data in the file system, such as documents, pictures, audio, video, etc. 2. Data files

From a more microscopic point of view, the data file consists of several 64KB-sized extents, each consisting of 8 consecutive pages of 8KB, as shown in the following figure:

The smallest unit of storage that SQL Server can recognize is called a page. The size of a page is 8KB, which is the unit in which SQL Server actually stores data. The disk Icano operation is then performed at the page level. An area consists of eight physically contiguous pages for

Manage pages effectively. All pages are stored in the area. When the table is created in SQL Server, the object is assigned to the extent. Smaller tables can be in the same area as other database objects.

3. Transaction log

A transaction is a collection of one or more T-SQL statements, equivalent to an "atomic" task, either executed successfully or not at all. Each SQL Server database has a transaction log, which is used to record SQL statements for all transactions. In the event of a data disaster, the database can be restored through the T-SQL statement recorded by the transaction log.

Transaction log files are stored not in pages, but in strips of log records of varying sizes.

II. Database management

SSMS tools can be used to design and build databases and store enterprise data to meet the needs of enterprises.

1. Create a database

Before you create a database, you must determine the name of the database, the owner (the user who created the database), the size, and the location where the data file and transaction log file are stored. So before you create a database, you need to consider the following:

The right to create a database is granted by default to members of the sysadmin and dbcreator server roles, which are used to grant users server-wide security privileges

The user who created the database will become the owner of the database

Consider the location of data files and log files

Reasonably estimate the size and growth of the database: 1) start SSMS and log in to the database using sa

2) right-click the "Database" node and select the "New Database" command from the pop-up shortcut menu.

3) Open the "New Database" window, set the database name to "class", specify the owner of the database, and the user who creates the database will become the owner of the database by default. 4) set the save directory of the file in the "path" column, click the button in this column, and open the "locate folder" dialog box, where you can choose the directory to save the file.

5) set the initial value in the "initial size" column and the automatic growth value in the "automatic growth / maximum size" column.

6) when you have set the data name and other options, click the "OK" button and a new database will be built. It generates two files, one is a .MDF file containing data, and the other is a .ldf file containing log information.

7) as a database administrator, you may deal with some special requirements for the database, all of which need to set database-level options for the database instead of the default values at the time of creation. Changes to these options can be made in the options interface of the Database Properties-class window

2. Expand the database

When the space of the data file or log file in the database is full, you need to allocate more space for the data file and log file. SQL Server can automatically extend the database based on the growth parameters defined when you create the database, or you can manually extend the database by allocating more file space on an existing database file, or by allocating space on another new file.

When you expand a database, you must increase the capacity of the database by at least 1MB, and you can specify the maximum to which the file is allowed to grow, which prevents the file from growing indefinitely, resulting in the exhaustion of the entire disk space. According to their own needs, the growth value should be set appropriately, too large or too small is not appropriate.

1) in SSMS object Explorer, right-click the "class" database, select the "Properties" command from the pop-up shortcut menu, and open the "Database Properties-class" window.

2) in the "Database Properties-class" window, select the "File" option in the "selection Page" pane, and open the "File" selection page.

3) Select the class data file, set the initial size to 100MB, and click in the autogrow / maximum size column. Button, open the change class Auto-growth Settings dialog box, deselect the enable Auto-growth check box, and then click OK

4) Click the "add" button on the "File" selection page, add a database file, specify the new file name as "db_class" in the "logical name" column, select "Row data" in the "Files of Type" column, set the "initial size" to 50MB, and click in the "automatic growth / maximum size" column. Button and select the enable automatic growth check box in the dialog box that opens. Set the maximum file size to 500MB, select the path C:\ db_class, and then click OK

3. Shrink the database

Each file in the database can be reduced by deleting unused space, SQL Server allows unused space to be released by shrinking the database, and both data files and log files can be reduced (shrunk). You can shrink the database manually or automatically.

1) manually shrink the database

As shown in the following figure, select the tasks-> shrink-> Database command to open the shrink Database-class window. The currently allocated space option shows the amount of space occupied by the database, the free space option shows the space and percentage that the database can shrink, and then click OK to perform the shrink database operation.

When you shrink a database, you cannot shrink the entire database to smaller than its original size. Therefore, if the database is created as 10MB and then grows to 100MB, the database can only shrink to a minimum of 10MB, even if all data is deleted. However, when you shrink files, you can shrink individual database files smaller than their original size.

2) shrink files manually

As shown in figure 1 of manually shrinking the database, select tasks-> shrink-> File command to open the shrink File-class window

Select the data option in the Files of Type drop-down list, or you can select the shrink log file here. The currently allocated space option and the available space option show the occupied space, remaining space, and percentage of shrinking of the file.

There are three options in the shrink operation option group, which defaults to "Free unused space", which serves the following purposes:

Free unused space: all unused space in the file is freed and the file is shrunk to the last allocated size. This reduces the size of the file, but does not move any data; reorganizes the page before releasing unused space: all unused space in the file is released and attempts to relocate to unallocated space, here you need to specify the value of the shrink file to option Empty the file by migrating the data to another file in the same filegroup: move all the data in the specified file to another file in the same filegroup, and then delete the empty file; 3) automatically shrink the database

You can achieve auto-shrink by setting options in the Database Properties window to select Auto-shrink on the page. When the autoshrink selection is set to True, the database with free space will be automatically shrunk, as shown in the following figure, this activity takes place in the background and does not affect user activities within the data

4. Detach and attach databases

Detaching and attaching data is useful if you need to change the database to different SQL Server instances on the same or different computers, or if you want to move the location where the database files are stored.

1) detach database

① right-click the "class" database in SSMS object Explorer, select "Task"-> "detach" command from the pop-up shortcut menu, and open the "detach Database" window.

② checks the database to be detached, and when it is correct, click OK to complete the detach operation.

When there are one or more active connections in the database, the message column displays the number of active connections, such as one active connection. The Delete connection check box must be selected to disconnect all active connections; by default, the detach operation will retain out-of-date optimization statistics when detaching the database; to update existing optimization statistics, you can select the Update Statistics check box; 2) attach the database

When you attach a database, all data files (primary and secondary) must be available. If the path to any data file is different from that when the database was first created or the last time the database was attached, you must specify the current path to the file.

① moves all files of the class database to another location, such as the D:\ class_log folder

② connects to the SQL Server database instance in SSMS object Explorer

③ right-click "Database" and select the "attach" command in the pop-up shortcut menu to open the "attach Database" window.

④ click the add button, then select the disk drive where the database to be attached is located in the location data File dialog box that opens, expand the directory tree to find and select the .MDF file for the class database, and then click OK

⑤ if you want to change the name of the attached database, you can enter the name in the "append as" column of the "attach database" window, or change the owner of the database in the "owner" column. After confirmation, click the "OK" button to complete the additional operation.

If the transaction log file of the database is lost and the data file is well preserved, it can also be attached successfully. When you attach, the database automatically creates a new transaction log file.

5. Delete the database

When users no longer need their own database, or have moved it to another database or server, they can delete the database, but they cannot delete the system database. After you delete the database, the file and its data are deleted from the disk on the server. Once the database is deleted, it is permanently deleted.

1) right-click the "class" database, select "Delete" from the pop-up shortcut menu, and open the "Delete object" window.

2) confirm the database to be deleted. If a user is connected to the database, select the "close existing connection" check box, and then click the "OK" button to complete the deletion of the database.

Third, the basic concept of table

A table is a database object that contains all the data in the database. Data is organized in a table similar to that in a spreadsheet, both in row and column format. Each row represents a unique record, and each row represents a field in the record. As shown below:

The tables in SQL Server include the following main components:

Columns: each column represents a property of the object modeled by the table. For example, the employee table has numbered columns, last name columns, job columns, and so on.

Rows: each row represents a separate instance of the object modeled by the table. For example, each employee of the company has a row in the table; 1, data integrity 1) physical integrity

Entity integrity defines a row as the only entity of a particular table. Entity integrity enforces the integrity of the identity column or primary key of a table through UNIQUE (unique) indexes, UNIQUE constraints, or PRIMARY KEY (primary key) constraints

2) Domain integrity

Domain integrity refers to the validity of items in a particular column. You can enforce domain integrity restriction types (by using data types), restrict formatting (by using CHECK constraints and rules), or limit the range of possible values (by using FOREIGN KEY constraints, CHECK constraints, DEFAULT definitions, NOT NULL definitions and rules)

3) referential integrity

Referential integrity preserves the defined relationship between tables when you enter or delete rows. When enforcing referential integrity, SQL Server prevents users from doing the following:

Add or change rows in related tables without associated rows in the main table; change values in the main table (which can result in orphaned rows in the related table); delete rows in the main table if there are matching related rows; 4) user-defined integrity rules

User-defined integrity can define specific business rules that do not belong to any other integrity category. All integrity categories support user-defined integrity, including all column-and table-level constraints, stored procedures, and triggers in CREATE TABLE

2. Primary key

The primary key uniquely identifies the row data in the table, with a primary key value corresponding to a row of data. A primary key consists of one or more fields whose values are unique, and null values (NULL) are not allowed, and a table can have only one primary key.

Use SSMS to manipulate data sheet 1 and data types

A data type is an attribute of data that specifies the type of data that an object can hold, such as integer data, character data, currency data, date and time data, strings, and so on.

The data types in SQL Server can be summarized into the following categories:

1) accurate figures

2) approximate number

3) date and time

4) string

5) Unicode string

6) binary string

2. Default value

If no value is specified for the column when the row is inserted, the column uses the default value. The default value can be any value that evaluates to a constant, such as a constant, a built-in function, or a mathematical expression.

For each column in the table, you can specify the default value that will be entered in the column when the user leaves the column blank. If you do not assign a default value and leave the column blank, then:

If the option to allow blank values is set, NULL; will be inserted into the column. If you do not set the option to allow blank values, the column will remain blank, but they will not be able to hold rows until the user provides a value for the column. 3. Identify the column

For each table, you can create an identification column that contains a system-generated ordinal value that uniquely identifies each row in the table.

You can use identity columns to create auto-incrementing identification numbers in tables, so identity columns are conventionally called self-incrementing columns, and only one identity column can be created for each table.

The identity column has the following three characteristics:

The data type of the column is a numeric type without decimal

During the Insert operation, the value of the column is generated by the system according to a certain rule, and null values are not allowed.

The column values are not duplicated and can identify each row in the table. Each table can only have one identification column.

To create an identity column, you typically specify the following three things:

Type (Type): in SQL Server 2008, identify column types when necessary, such as decimal, int, numeric, smalint, bigint, tinyint. Note that when selecting decimal and numeric, the number of decimal places must be zero. Also pay attention to the range of values represented by each data type

Seed (Seed): the value assigned to the first row of the table. Default is 1.

Recursive increment (Increment): the increment between two adjacent identification values. The default is 1 position 4, check constraint.

CHECK termination enforces the integrity of the domain by limiting the values acceptable to the column. Such constraints are similar to FOREIGN KEY constraints in that you can control the values that are placed in the column. However, they differ in the way they determine valid values; the FOREIGN KEY constraint obtains a list of valid values from other tables, while the CHECK constraint determines valid values through logical expressions that are not based on data in other columns.

You can create CHECK constraints from any logical (Boolean) expression that returns True or Flase based on logical operators. For example, you can limit the range of values in the age column to data between 0,200 and 200 by creating a CHECK constraint to prevent the entered age value from exceeding the normal age range. The logical expression is:

Age > = 0 AND age

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.