Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about what the data storage structure of SQLServer looks like. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

SQLServer is a database management system that needs to store high-capacity data in an efficient way. To better understand how SQLServer 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 file: enables SQL-based applications to store unstructured data in the file system, such as documents, pictures, audio, video, etc.

2. Data file

From a more microscopic point of view, the data file consists of several 64KB-sized extents, each consisting of 8 consecutive pages of 8KB

The smallest unit of storage that SQLServer can recognize is called a page. The size of a page is 8KB, which is the unit in which SQLServer 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 SQLServer, 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 SQLServer 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.

After reading the above, do you have any further understanding of the data storage structure of SQLServer? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.