In this article

Applies to:

*
Squốc lộ Server năm 2016 (13.x) & later
*
Azure Squốc lộ Database
*
Azure SQL Managed Instance
*
Azure Synapse Analytics
*
Analytics Platkhung System (PDW)

Tables are database objects that contain all the data in a database. In tables, data is logically organized in a row-and-column format similar lớn a spreadsheet. Each row represents a unique record, and each column represents a field in the record. For example, a table that contains employee data for a company might contain a row for each employee and columns representing employee information such as employee number, name, address, job title, and trang chính telephone number.

Bạn đang xem: Di chuyển ô trong excel

The number of tables in a database is limited only by the number of objects allowed in a database (2,147,483,647). A standard user-defined table can have up khổng lồ 1,024 columns. The number of rows in the table is limited only by the storage capathành phố of the hệ thống.

You can assign properties to lớn the table và lớn each column in the table to control the data that is allowed & other properties. For example, you can create constraints on a column lớn disallow null values or provide a default value if a value is not specified, or you can assign a key constraint on the table that enforces uniqueness or defines a relationship between tables.

Types of Tables

Besides the standard role of basic user-defined tables, Squốc lộ Server provides the following types of tables that serve sầu special purposes in a database.

Partitioned Tables

Partitioned tables are tables whose data is horizontally divided inlớn units which may be spread across more than one filegroup in a database. Partitioning makes large tables or indexes more manageable by letting you access or manage subsets of data quickly and efficiently, while maintaining the integrity of the overall collection. By mặc định, Squốc lộ Server supports up to 15,000 partitions. For more information, see Partitioned Tables and Indexes.

Temporary Tables

Temporary tables are stored in tempdb. There are two types of temporary tables: local and global. They differ from each other in their names, their visibility, và their availability. Local temporary tables have a single number sign (#) as the first character of their names; they are visible only to the current connection for the user, & they are deleted when the user disconnects from the instance of SQL Server. Global temporary tables have two number signs (##) as the first characters of their names; they are visible to lớn any user after they are created, and they are deleted when all users referencing the table disconnect from the instance of Squốc lộ Server.

Reduced recompilations for workloads using temporary tables across multiple scopes

Squốc lộ Server 2019 (15.x) under all database compatibility levels reduces recompilations for workloads using temporary tables across multiple scopes. This feature is also enabled in Azure SQL Database under database compatibility màn chơi 150 for all deployment models. Prior to lớn this feature, when referencing a temporary table with a data manipulation language (DML) statement (SELECT, INSERT, UPDATE, DELETE), if the temporary table was created by an outer scope batch, this would result in a recompile of the DML statement each time it is executed. With this improvement, SQL Server performs additional lightweight checks to lớn avoid unnecessary recompilations:

Check if the outer-scope module used for creating the temporary table at compile time is the same one used for consecutive executions.Keep traông chồng of any data definition language (DDL) changes made at initial compilation và compare them with DDL operations for consecutive sầu executions.

The kết thúc result is a reduction in extraneous recompilations và CPU-overhead.

System Tables

SQL Server stores the data that defines the configuration of the hệ thống và all its tables in a special phối of tables known as system tables. Users cannot directly query or update the system tables. The information in the system tables is made available through the system views. For more information, see System Views (Transact-SQL).

Wide Tables

Wide tables use sparse columns to lớn increase the total of columns that a table can have sầu khổng lồ 30,000. Sparse columns are ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead lớn retrieve nonnull values. A wide table has defined a column phối, which is an untyped XML representation that combines all the sparse columns of a table inkhổng lồ a structured output. The number of indexes & statistics is also increased khổng lồ 1,000 and 30,000, respectively. The maximum kích cỡ of a wide table row is 8,019 bytes. Therefore, most of the data in any particular row should be NULL. The maximum number of nonsparse columns plus computed columns in a wide table remains 1,024.

Wide tables have the following performance implications.

Applications can dynamically add and remove sầu columns from wide tables. When columns are added or removed, compiled query plans are also invalidated. We recommend that you kiến thiết an application khổng lồ match the projected workload so that schema changes are minimized.

When data is added and removed from a wide table, performance can be affected. Applications must be designed for the projected workload so that changes lớn the table data is minimized.

Limit the execution of DML statements on a wide table that update multiple rows of a clustering key. These statements can require significant memory resources khổng lồ compile & exedễ thương.

Switch partition operations on wide tables can be slow and might require large amounts of memory to process. The performance & memory requirements are proportional khổng lồ the total number of columns in both the source & target partitions.

Update cursors that update specific columns in a wide table should menu the columns explicitly in the FOR UPDATE clause. This will help optimize performance when you use cursors.

Comtháng Table Tasks

The following table provides link lớn common tasks associated with creating or modifying a table.

Table TasksTopic
Describes how khổng lồ create a table.Create Tables (Database Engine)
Describes how lớn delete a table.Delete Tables (Database Engine)
Describes how to lớn create a new table that contains some or all of the columns in an existing table.Duplicate Tables
Describes how lớn rename a table.Rename Tables (Database Engine)
Describes how khổng lồ view the properties of the table.View the Table Definition
Describes how khổng lồ determine whether other objects such as a view or stored procedure depover on a table.View the Dependencies of a Table

The following table provides links lớn comtháng tasks associated with creating or modifying columns in a table.

Column TasksTopic
Describes how to lớn add columns to an existing table.Add Columns to lớn a Table (Database Engine)
Describes how lớn delete columns from a table.Delete Columns from a Table
Describes how to change the name of a column.Rename Columns (Database Engine)
Describes how khổng lồ copy columns from one table to another, copying either just the column definition, or the definition và data.Copy Columns from One Table lớn Another (Database Engine)
Describes how khổng lồ modify a column definition, by changing the data type or other property.Modify Columns (Database Engine)
Describes how lớn change the order in which the columns appear.Change Column Order in a Table
Describes how lớn create a computed column in a table.

Xem thêm: Lịch Sử Thích Chân Quang - Tiểu Sử Thượng Tọa Thích Chân Quang

Specify Computed Columns in a Table
Describes how to lớn specify a default value for a column. This value is used if another value is not supplied.Specify Default Values for Columns

See Also

Primary và Foreign Key ConstraintsUnique Constraints & Check Constraints