数据如何存储在 SQL Server 中？

How is data stored in SQL server?

提问by Anoop

How is data stored in SQL server?

数据如何存储在 SQL Server 中？

回答by Erich Mirabal

This Wikipedia articledescribes it rather well.

这篇维基百科文章对它进行了很好的描述。

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

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

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.

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.

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 支持不同的数据类型，包括整数、浮点数、小数、Char（包括字符串）、Varchar（可变长度字符串）、二进制（用于非结构化的 blob 数据）、Text（用于文本数据）等主要类型. 它还允许定义和使用用户定义的复合类型 (UDT)。SQL Server 还将服务器统计信息作为虚拟表和视图（称为动态管理视图或 DMV）提供。除了表之外，数据库还可以包含其他对象，包括视图、存储过程、索引和约束以及事务日志。一个 SQL Server 数据库最多可以包含 231 个对象，并且可以跨越多个操作系统级别的文件，最大文件大小为 220 TB。数据库中的数据存储在扩展名为 .mdf 的主数据文件中。使用 .ndf 扩展名标识的辅助数据文件用于存储可选的元数据。日志文件以 .ldf 扩展名标识。

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

对于表的物理存储，其行被划分为一系列分区（编号为 1 到 n）。分区大小由用户定义；默认情况下，所有行都在一个分区中。一个表被分成多个分区，以便在一个集群上分布一个数据库。每个分区中的行存储在 B 树或堆结构中。如果表具有关联索引以允许快速检索行，则行将根据其索引值按顺序存储，并由 B 树提供索引。数据在叶子的叶子节点中，其他节点存储从各个节点可达的叶子数据的索引值。如果索引是非聚集的，则不会根据索引键对行进行排序。索引视图与索引表具有相同的存储结构。没有索引的表存储在无序堆结构中。堆和 B 树都可以跨越多个分配单元。

回答by Garrett

SQL Server data is stored in data files that, by default, have an .MDF extension. The log (.LDF) files are sequential files used by SQL Server to log transactions executed against the SQL Server instance (more on instances in a moment). The log files (.LDF files) are truncated automatically when using the SIMPLE recovery model, but not when using BULK LOGGED or FULL recovery.

SQL Server 数据存储在默认情况下具有 .MDF 扩展名的数据文件中。日志 (.LDF) 文件是 SQL Server 用来记录针对 SQL Server 实例执行的事务的顺序文件（稍后将详细介绍实例）。使用 SIMPLE 恢复模式时会自动截断日志文件（.LDF 文件），但使用 BULK LOGGED 或 FULL 恢复时不会自动截断。

Instances allow for more than one installation of SQL Server on a single machine. If the instance is nameless, it is the default instance. Named instances are possible as well. For eg:

实例允许在一台机器上安装多个 SQL Server。如果实例是无名的，则它是默认实例。命名实例也是可能的。例如：

MACHINENAME <-- the default instance is just the machine name MACHINENAME\Test <-- this is the "Test" instance on this machine

MACHINENAME <-- 默认实例只是机器名 MACHINENAME\Test <-- 这是这台机器上的“Test”实例

You can use tools like SQL Server Management Studio (as of SQL Server 2005) or Enterprise Manager (SQL Server 2000 and before) to interact with the instance & the databases under the instance.

您可以使用 SQL Server Management Studio（自 SQL Server 2005 起）或企业管理器（SQL Server 2000 及更早版本）等工具与实例和实例下的数据库进行交互。

All instances (as of SQL Server 2005) will have a hidden resource database, as well as a master, model, msdb, and temp database. These databases are "system" databases.

所有实例（从 SQL Server 2005 开始）都将有一个隐藏的资源数据库，以及一个 master、model、msdb 和 temp 数据库。这些数据库是“系统”数据库。

Not sure what else you're looking for. Hope that helps.

不知道你还在找什么。希望有帮助。

EDIT: Oh yeah, physically, data in the "data files" (.MDF files, by default) is structured in what are known as "pages" in SQL Server. Data in the log files (.LDF files) is stored sequentially. In the enterprise, the data and log files are sometimes split on different physical hard drives for better disk I/O. Or hardware RAID is used for this purpose.

编辑：哦，是的，在物理上，“数据文件”（.MDF 文件，默认情况下）中的数据在 SQL Server 中被称为“页面”。日志文件（.LDF 文件）中的数据按顺序存储。在企业中，有时将数据和日志文件拆分在不同的物理硬盘驱动器上以实现更好的磁盘 I/O。或者硬件 RAID 用于此目的。

EDIT2: Forgot to mention file groups. Using file groups, you can design your logical database schema such that elements of that schema are physically separated, typically to disburse the physical database across different hard drives. For example, you could have a data file group, an indexes file group, and an images file group (for binary images).

EDIT2：忘了提及文件组。使用文件组，您可以设计逻辑数据库架构，以便该架构的元素在物理上是分开的，通常是在不同的硬盘驱动器之间分配物理数据库。例如，您可以有一个数据文件组、一个索引文件组和一个图像文件组（用于二进制图像）。

回答by onedaywhen

I recommend the book 'Microsoft SQL Server 2008 Internals' -- in fact anything by Kalen Delaney on internals is good, IMO.

我推荐“Microsoft SQL Server 2008 Internals”一书——事实上，Kalen Delaney 关于内部结构的任何东西都很好，IMO。

回答by Andrew Hare

SQL Server is a Relational Database Management System:

SQL Server 是一个关系数据库管理系统：

A Relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd. Most popular commercial and open source databases currently in use are based on the relational model.

A short definition of an RDBMS may be a DBMS in which data is stored in the form of tables and the relationship among the data is also stored in the form of tables.

关系数据库管理系统 (RDBMS) 是基于 EF Codd 引入的关系模型的数据库管理系统 (DBMS)。目前使用的最流行的商业和开源数据库都是基于关系模型的。

RDBMS 的一个简短定义可以是数据以表格的形式存储并且数据之间的关系也以表格的形式存储的DBMS。

回答by mwigdahl

You can take this just about as deep as you want to go, but for SQL Server 2008 Files and Filegroups Architecture - MSDNis a good overview of basic database architecture.

您可以随心所欲地进行深入研究，但对于 SQL Server 2008文件和文件组体系结构 - MSDN是基本数据库体系结构的一个很好的概述。

The MSDN site will be a valuable resource if you need even more detailed specifics on how SQL Server 2008 stores data.

如果您需要关于 SQL Server 2008 如何存储数据的更详细的细节，MSDN 站点将是一个宝贵的资源。

回答by Serapth

What is RDBMS?

什么是关系型数据库？

RDBMS stands for Relational Database Management System. RDBMS data is structured in database tables, fields and records. Each RDBMS table consists of database table rows. Each database table row consists of one or more database table fields.

RDBMS 代表关系数据库管理系统。RDBMS 数据由数据库表、字段和记录构成。每个 RDBMS 表由数据库表行组成。每个数据库表行由一个或多个数据库表字段组成。

RDBMS store the data into collection of tables, which might be related by common fields (database table columns). RDBMS also provide relational operators to manipulate the data stored into the database tables. Most RDBMS use SQL as database query language.

RDBMS 将数据存储到表的集合中，这些表可能通过公共字段（数据库表列）相关联。RDBMS 还提供关系运算符来操作存储到数据库表中的数据。大多数 RDBMS 使用 SQL 作为数据库查询语言。

Edgar Codd introduced the relational database model. Many modern DBMS do not conform to the Codd's definition of a RDBMS, but nonetheless they are still considered to be RDBMS.

Edgar Codd 介绍了关系数据库模型。许多现代 DBMS 不符合 Codd 对 RDBMS 的定义，但它们仍然被认为是 RDBMS。

The most popular RDBMS are MS SQL Server, DB2, Oracle and MySQL. Source

最流行的 RDBMS 是 MS SQL Server、DB2、Oracle 和 MySQL。 来源