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

Management of http://xiaorenwutest.blog.51cto.com database and table

Last time, we mainly introduced the construction of sql server database and the connection of database through SSMS tool. This time, we mainly introduced the table management of database. At work, DBA usually creates and deletes the database, modifies the contents of the table, expands and shrinks, separates and appends.

The table is the basic data construction to store, find and update the data. The operations on the data are carried out on the basis of the table, and how to maintain the table. Note: in fact, the table is a logical existence, in fact, the data is stored on the hard disk.

In addition, two basic database files, .MDF and transaction log files, are formed when creating the database. Ldf

How sql server 2008R2 stores data

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

The database is created on one or more files on the physical media (disk), and it pre-allocates the physical storage space to be applied by the data and transaction log. The file in which data is stored is called a data file, which contains data and objects, such as tables and indexes. The file that stores the transaction log is called the transaction log file (also known as the log file).

The sql server 2008 R2 database has the following types of files

1. Master data file

two。 Auxiliary data file

3. Transaction log file

4. File stream

Give examples to illustrate

-- create a database

Create database schoolDB

Go

Use schoolDB

Go

The database created in this way has only one data file and one transaction log file. For viewing, right-click "Properties"-"File" on the created schoolDB.

You can click "add" in the interface to add a new data file, schooldb2, and later create a new table in the schooldb database, and the data in the table will be stored in the schooldb and schoolDB2 data files.

Data files are marked when they are stored in the database, and we can use sp_helpdb schoolDB

Data file structure

The structure of data file 1 and data file 3 if shown:

The data file consists of 64kb-sized extents (extend), each consisting of 8 consecutive pages of 8KB.

The smallest storage unit that sqlserver can recognize is called a page, and the size of a page is 8KB, which is the unit in which sqlserver stores data.

In the database, the size of a page is 8KB, and in a computer, 1KB is 1024, so a page can store 1024818192 bytes.

1.1. How to estimate the disk space occupied according to the number of records in the table

1. First calculate how many bytes there are in a row in a table, such as 6 bytes in the student ID field, 8 bytes in the name field, and how many bytes in the date of birth field

two。 Calculate how many lines a page can save.

3. How many rows of records are added to the table in the database every day, you can calculate how much hard disk space you need to add every day, so that you can estimate how much space is planned based on the amount of data.

Create a Tstudent table in the database

Create TABLE TStudent (

StudentID varchar (10) NOT NULL

Sname varchar (10) DEFAULT NULL

Sex char (2) DEFAULT NULL

CardID varchar (20) DEFAULT NULL

Birthday datetime DEFAULT NULL

Email varchar (40) DEFAULT NULL

Class varchar (20) DEFAULT NULL

EnterTime datetime DEFAULT NULL

)

Go

10 bytes + 10 bytes + 2 bytes + 20 bytes + 8 bytes + 40 bytes + 20 bytes + 8 bytes = 118 bytes

A row of the Tstudent table has 118 bytes

If you add 10000 records per day, how many pages will 10000 records account for?

A page can store 10248192 bytes, throw out the header 96 bytes, the real number of bytes stored is 8192-968096 bytes.

One page can store 8096 bytes, and one page can store 8096 Universe 118 records.

How many pages are there in 10000 lines? 100001,68,148 (page)

One page is 8KB, 148KB, 8KB, 1184KB

Storing 10000 records in the Tstudent table requires more disk space for 1MB. In this way, the disk space can be reasonably planned according to the number of records added each day.

No records have been inserted into the Tstudent table yet, so it takes up 0 pages of data

Provides commands to view the number of pages in a data file

Select OBJECT_NAME (i.object_id) as table name, number of data_pages as data pages

From sys.indexes as i

Join sys.partitions as p ON p.object_id=i.object_id and p.index_id=i.index_id

Join sys.allocation_units as an ON a.container_id=p.partition_id

Where i.object_id=object_id ('dbo.TStudent')

Execute stored procedures in the teaching environment and add 10000 records

There is a gap between the number of pages and what we have just calculated, which is normal. 68 records may not be inserted on each page.

1.2. Log of things

A thing is a collection of one or more T-SQL statements, and it has one property: either execution succeeds or fails. Every sql server database has a transaction log, a SQL statement for recording everything. In the event of a data disaster, the database can be restored through the T-SQL statement of transaction logging.

If the system fails, sql server will use the transaction log to redo (roll forward) all confirmed things and undo (roll back) all unfinished things.

1.3. Create a database (expand / shrink)

Use the graphical interface to create the database, in object Explorer, right-click the "Database" node, select the "New Database" command from the pop-up shortcut menu, open the "New Database" window, set the database name to "class", and determine the owner of the database. The user who created the database by default will become the owner of the database. The initial value of the master data file is 3mb, and the automatic growth value is 1mb. When the data file or log file space is used up, the capacity of the file will be increased according to the set automatic growth value.

You can set the save path for data files and log files in the path column. After clicking OK, a new database is successfully established, and two files are generated, one is the .MDF containing the data file, and the other is the .ldf file containing the log information.

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.

If you want to set up the database at the database level

Select options in the database properties, and you can modify database-related properties on the right.

1.3.1. Extended database

When the data files and log files in the database are full, more space needs to be allocated for data files and log files. 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 data file, or by allocating space on another new data 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 files are allowed to grow, which prevents the unlimited growth of files, resulting in the exhaustion of entire disk space.

Examples are as follows:

Set the class data file size of the class database to 100MB, do not grow automatically, and then add a new data file named "class1". The initial file size is set to "50MB", and the maximum file size is set to "500MB".

In object Explorer, right-click the class database, select the Properties command from the pop-up shortcut menu, open the Database Properties-class window, select the File option in the selection Page of the Database Properties-class window, and open the File selection page.

Configured as shown in the following figure: finally click OK

1.3.2. Shrink the database

After using the database for a period of time, the increase of free space in the database is often caused by data deletion, so it is necessary to reduce the disk space allocated to database files and transaction log files to avoid wasting disk space. When there is no data in the database, you can modify the properties of the database file to directly change its occupied space, but when there is data in the database, this will destroy the data in the database, so it is necessary to use contraction to reduce the database space.

Each file in the database can be reduced by deleting unused space, and SQL server allows both data files and log files to shrink by shrinking the database and freeing up unused space. You can shrink the database manually and automatically.

The method of manually shrinking the database

You can see from the figure that you can choose either to shrink the database or to shrink a data file separately.

Shrink the database:

When you shrink a database, you cannot shrink the entire database to a smaller size than the initial size, and if the database was created as 10mb and then grew to 10mb, the database can only shrink to a minimum of 10mb, even if all data has been deleted. But when you shrink a file, you can shrink the database file to a smaller size than its original size

Shrink data file

Select-- "Task"-- "shrink"-- "File" command to open the shrink file dialog box.

Select the data option in the Files of Type drop-down list box, or you can choose to shrink the log file here. The currently allocated space option and the available space option show the space occupied, the space used, and the percentage of shrinkage of the file.

Free unused space: frees up all unused space in the file and shrinks the file to the last allocated size, which reduces the file size but does not move any data.

Reorganize the page before releasing unused space: all unused space in the file is released and an attempt is made 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: migrate all data in the specified file to another file in the same filegroup, and then delete the empty file.

Determine the options as needed, and then click OK to perform the shrink operation.

Automatically shrink the database

You can achieve auto-shrink by setting the Auto-shrink option parameter in the options selection page in the Database Properties-- class window. When the autoshrink option is set to true, the database of available space is automatically shrunk.

1.3.3. Detach and attach databases

If you want to change the database to a different sql server instance on the same computer or if you want to move the database, you can use the ability to detach and attach the database. You can detach the data files and log files of the database and reattach them to the same other SQL Server instance.

Detach database

Detaching a database means removing the database from the SQL server instance, but ensuring that the data files and log files in the database are intact. These separate data files and log files can be attached to other database instances.

Examples are as follows:

Separate the class database

Right-click the class database in object Explorer, select Task-detach command from the pop-up shortcut menu, and open the detach Database window.

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. You must select the remove connection check box to disconnect all active connections.

By default, the detach operation retains out-of-date optimization statistics when detaching the database, and to update existing optimization statistics, you can select the Update Statistics check box.

Additional 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.

Click the add button and select the class.mdf data file

After clicking OK, the attached file was successful.

If the transaction log file of the database is lost and the data file remains intact, it can also be attached successfully, and the database will automatically create a new transaction log file when it is attached.

1.3.4. Delete database

Users can delete the database when they no longer need their own database, but they cannot delete the system database. After deleting the database, the file and its data are deleted from the disk on the server, and once the database is deleted, it will be permanently deleted.

Confirm the database to delete

1.4. Basic management of tables

Data integrity:

Entity integrity: ensure that each row has a unique identity column:

Domain integrity: specifies the data values that the column can accept (domain integrity)

Referential integrity: column references within and between tables

User-defined integrity: column-level and table-level constraints, stored procedures and triggers

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

If the primary key consists of multiple columns, one of the columns will be allowed to have duplicate values, but the worthy combinations of all columns in the primary key must be unique.

Defining a primary key forces the uniqueness of a value entered in a specified column that does not allow null values. If you define a primary key for a table in the database, you can associate that table with other tables, reducing the need for redundant data.

Each database in sql server can store up to 2 billion tables, each table can have 1024 columns, the number and size of rows of the table is only limited by the available storage space, each row can store up to 8060B, the table name must be specified when creating the table, the column name is the data type, and so on.

1.5. Data type of sql server

1. Numeric type represents digits int tinyint smallint bigint decimal money smallmoney decimal floating point numbers and real

two。 Date datetime can be accurate to 0.333 millisecond small

3. Character types include char and nchar as well as variable length character types varchar and nvarchar

4. Fixed length character char (20)

5. Variable length character varchar (20)

6. Char is suitable for storing one character in English, occupying 1 byte.

7. Nchar is suitable for storing one character in Chinese, occupying 2 bytes.

8. The binary Binary and varbinary,bit represent the value of 0 or 1 of one bit, which represents the only 8-bit binary in the database.

1.5.1. Default value

If no value is specified for the column when you insert the row, the column uses the default value, which can be any value that evaluates to a constant, an expression, a built-in function, or a mathematical expression.

For each column of the table, if no default value is assigned and left blank: then

1. If the option to allow null values is set, NULL will be inserted like this column

two。 If the allowed null value is not set, the column remains blank. However, until users provide a value for the column, they will not be able to save the row.

1.5.2. Identifier column

For each table, you can create a broad implied system-generated sequence number worth identifying the paid column, and the serial number value uniquely identifies each row in the table. When you insert a row in a table, the identifier column automatically generates a unique label for the application.

The identifier column has the following three characteristics

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

two。 During the insert operation, the value of the column is systematically generated according to a certain rule, and null values are not allowed.

3. Column values are not duplicated and have the effect of identifying each row in the table. Each table can have only one identity column

Create an identity column, usually specifying three contents

Type: decimal, int,numeric,smallint,bigint,tinyint. Where decimal and numeric, the decimal place must be zero

Seed: the value assigned to the first row of the table: the default is "1"

Ground increment: the increment between two adjacent identification values. The default is 1.

1.5.3. Check constraint

The check constraint enforces the integrity of the domain by limiting the values that the column can receive. Such constraints are similar to foreign key constraints in that you can control the values that are placed in the column. But they are different in the way they determine the effective value. Foreign key constraints obtain a list of valid values from other tables, while check constraints determine valid values through logical expressions that are not based on data in other columns.

You can create an check constraint from any logical expression that returns TRUE or FALSE based on a logical operator.

Examples are as follows:

You can limit the worthwhile range of data in the age column to 0-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.