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

In this issue, the editor will bring you about what the SQL Server data storage structure is. The article is rich in content and analyzes and describes it from a professional point of view. I hope you can get something after reading this article.

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.

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 that are used to 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.

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.

The above is what the SQL Server data storage structure shared by the editor is. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.