数据如何存储在 SQL Server 中? [英] How is data stored in SQL server?

问题描述

数据如何存储在 SQL Server 中?

How is data stored in SQL server?

推荐答案

这篇维基百科文章描述还不错.

This Wikipedia article describes it rather well.

这是它的一个子集，与数据存储有关:

Here is a subset of it, relating to Data Storage:

数据存储的主要单位数据存储是一个数据库，它是一个带类型表的集合列.SQL Server 支持不同的数据类型，包括主要类型例如整数、浮点数、小数、字符(包括字符串), Varchar(变长字符串)，二进制(对于非结构化的 blob数据)、文本(用于文本数据)之间其他.它还允许用户定义要定义的复合类型 (UDT)并使用.SQL Server 也使服务器可用作虚拟表的统计信息和视图(称为动态管理视图或 DMV).数据库也可以包含其他对象，包括视图，存储过程、索引和约束，除了表，连同事务日志.一条 SQL服务器数据库最多可以包含231 个对象，并且可以跨越多个具有最大文件的操作系统级文件大小为 220 TB.中的数据数据库存储在原始数据中扩展名为 .mdf 的文件.二级数据文件，标识为.ndf 扩展名，用于存储可选的元数据.日志文件是用 .ldf 标识扩展名.

Data storage The main unit of data storage is a database, which is a collection of tables with typed columns. SQL Server supports different data types, including primary types such as Integer, Float, Decimal, Char (including character strings), Varchar (variable length character strings), binary (for unstructured blobs of data), Text (for textual data) among others. It also allows user-defined composite types (UDTs) to be defined and used. SQL Server also makes server statistics available as virtual tables and views (called Dynamic Management Views or DMVs). A database can also contain other objects including views, stored procedures, indexes and constraints, in addition to tables, along with a transaction log. A SQL Server database can contain a maximum of 231 objects, and can span multiple OS-level files with a maximum file size of 220 TB. The data in the database are stored in primary data files with an extension .mdf. Secondary data files, identified with an .ndf extension, are used to store optional metadata. Log files are identified with the .ldf extension.

分配给数据库的存储空间分为按顺序编号页，每页大小为 8 KB.一个页面是SQL Server 的 I/O 基本单元操作.一个页面标有存储元数据的 96 字节标头关于页面包括页面数量、页面类型、可用空间页面和对象的 ID拥有它.页面类型定义数据包含在页面中 - 数据存储在数据库、索引、分配图其中包含有关如何页被分配给表和索引，更改保存的映射有关所做更改的信息自上次备份以来的其他页面或日志记录，或包含大数据类型例如图像或文本.虽然页面是I/O 操作的基本单元，空间实际上是按照以下方式管理的由 8 页组成的盘区.一种数据库对象可以跨越所有 8 个范围内的页面(统一范围")或与多达 7 个以上的人共享一个范围对象(混合范围").一排数据库表的跨度不能超过一页，因此限制为 8 KB尺寸.但是，如果数据超过 8KB 并且该行包含 Varchar 或Varbinary 数据，其中的数据列移动到新页面(或可能是一系列页面，称为一个分配单元)并替换为指向数据的指针.

Storage space allocated to a database is divided into sequentially numbered pages, each 8 KB in size. A page is the basic unit of I/O for SQL Server operations. A page is marked with a 96-byte header which stores metadata about the page including the page number, page type, free space on the page and the ID of the object that owns it. Page type defines the data contained in the page - data stored in the database, index, allocation map which holds information about how pages are allocated to tables and indexes, change map which holds information about the changes made to other pages since last backup or logging, or contain large data types such as image or text. While page is the basic unit of an I/O operation, space is actually managed in terms of an extent which consists of 8 pages. A database object can either span all 8 pages in an extent ("uniform extent") or share an extent with up to 7 more objects ("mixed extent"). A row in a database table cannot span more than one page, so is limited to 8 KB in size. However, if the data exceeds 8 KB and the row contains Varchar or Varbinary data, the data in those columns are moved to a new page (or possibly a sequence of pages, called an Allocation unit) and replaced with a pointer to the data.

对于表的物理存储，它的行被分成一系列分区(编号为 1 到 n).这分区大小由用户定义；经过默认所有行都在一个划分.一个表被拆分成多个分区以便传播集群上的数据库.行在每个分区都存储在B 树或堆结构.如果表有一个关联的索引以允许快速检索行，存储行根据他们的索引排序值，B 树提供指数.数据在叶子节点叶子和其他节点存储叶数据的索引值可以从各自的节点访问.如果索引是非聚集的，则行没有按照索引键.索引视图具有与索引相同的存储结构桌子.没有索引的表是存储在一个无序的堆结构中.堆和 B 树都可以跨越多个分配单元.

For physical storage of a table, its rows are divided into a series of partitions (numbered 1 to n). The partition size is user defined; by default all rows are in a single partition. A table is split into multiple partitions in order to spread a database over a cluster. Rows in each partition are stored in either B-tree or heap structure. If the table has an associated index to allow fast retrieval of rows, the rows are stored in-order according to their index values, with a B-tree providing the index. The data is in the leaf node of the leaves, and other nodes storing the index values for the leaf data reachable from the respective nodes. If the index is non-clustered, the rows are not sorted according to the index keys. An indexed view has the same storage structure as an indexed table. A table without an index is stored in an unordered heap structure. Both heaps and B-trees can span multiple allocation units.

这篇关于数据如何存储在 SQL Server 中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！