SQL Server表结构概述

Microsoft SQL Server is a relational database management systems (RDBMS) that, at its fundamental level, stores the data in tables. The tables are the database objects that behave as containers for the data, in which the data will be logically organized in rows and columns format. Each row is considered as an entity that is described by the columns that hold the attributes of the entity. For example, the customers table contains one row for each customer, and each customer is described by the table columns that hold the customer information, such as the CustomerName and CustomerAddress. The table rows have no predefined order, so that, to display the data in a specific order, you would need to specify the order that the rows will be returned in. Tables can be also used as a security boundary/mechanism, where database users can be granted permissions at the table level.

Microsoft SQL Server是一个关系数据库管理系统（RDBMS），它在其基本级别上将数据存储在表中。 表是充当数据容器的数据库对象，其中数据将以行和列格式逻辑组织。 每行都被视为一个实体，由保存实体属性的列所描述。 例如，customers表为每个顾客包含一行，并且每个顾客由保存顾客信息（例如CustomerName和CustomerAddress）的表列描述。 表行没有预定义的顺序，因此，要以特定顺序显示数据，您需要指定返回行的顺序。表还可以用作安全边界/机制，数据库用户可以在其中使用可以在表级别被授予权限。

表基础 (Table basics)

SQL Server tables are contained within database object containers that are called Schemas. The schema also works as a security boundary, where you can limit database user permissions to be on a specific schema level only. You can imagine the schema as a folder that contains a list of files. You can create up to 2,147,483,647 tables in a database, with up to 1024 columns in each table. When you design a database table, the properties that are assigned to the table and the columns within the table will control the allowed data types and data ranges that the table accepts. Proper table design, will make it easier and faster to store data into and retrieve data from the table.

SQL Server表包含在称为架构的数据库对象容器中。 该模式还可以用作安全边界，您可以在其中将数据库用户权限限制为仅在特定模式级别。 您可以将架构想象为包含文件列表的文件夹。 一个数据库中最多可以创建2,147,483,647个表，每个表中最多可以有1024列。 设计数据库表时，分配给该表的属性以及该表中的列将控制该表接受的允许的数据类型和数据范围。 正确的表设计将使它更容易，更快地将数据存储到表中或从表中检索数据。

特殊表类型 (Special table types)

In addition to the basic user defined table, SQL Server provides us with the ability to work with other special types of tables. The first type is the Temporary Table that is stored in the tempdb system database. There are two types of temporary tables: a local temporary table that has the single number sign prefix (#) and can be accessed by the current connection only, and the Global temporary table that has two number signs prefix (##) and can be accessed by any connection once created.

除了基本的用户定义表之外，SQL Server还使我们能够使用其他特殊类型的表。 第一种类型是存储在tempdb系统数据库中的临时表 。 临时表有两种类型：具有单个数字符号前缀（＃）且只能由当前连接访问的本地临时表，以及具有两个数字符号前缀（##）且可以为-的全局临时表。创建后可以通过任何连接访问。

A Wide Table is a table the uses the Sparse Column to for optimized storage for the NULL values, reducing the space consumed by the table and increasing the number of columns allowed in that table to 30K columns.

宽表是使用稀疏列来优化存储NULL值的表，它减少了表占用的空间并将该表中允许的列数增加到30K列。

System Tables are a special type of table in which the SQL Server Engine stores information about the SQL Server instance configurations and objects information, that can be queried using the system views.

系统表是表的一种特殊类型，SQL Server Engine在其中存储有关SQL Server实例配置和对象信息的信息，可以使用系统视图来查询这些信息。

Partitioned Tables are tables in which the data will be divided horizontally into separate unites in the same filegroup or different filegroups, based on a specific key, to enhance the data retrieval performance.

分区表是指其中的数据将基于特定键在水平方向上分成相同文件组或不同文件组中的单独单元，以增强数据检索性能的表。

物理实施 (Physical implementation)

Physically, SQL Server tables are stored in ta database as a set of 8 KB pages. Table pages are stored by default in a single partition that resides in the PRIMARY default filegroup. A table can be also stored in multiple partitions, in which each group of rows will be stored in a specific p