SQL Server Database

Table
Database (Database engine) Table creation table (Database engine) Delete table (Database engine) Copy table rename table (Database engine) View Table Definition View table dependencies add columns to a table (database engine) Remove columns from a table rename columns (Database engine) Copy columns from one table to another (database engine) repair Change the column order in the table (database engine) to specify the default values for a computed column in the specified table using a sparse column using a column set using a table-valued parameter (Database engine) primary KEY and FOREIGN KEY constraint unique constraints and CHECK constraint table column properties (SQL Server Management Studio) memory Mid-OLTP (in-memory optimized) index (database engine) partitioned tables and partitioned indexed view stored procedures Search user-defined Function statistics plan guide cursor sequence number DDL trigger DML trigger synonym (Database engine) XML data spatial data Binary large object (Blob Data hierarchy data-tier application transaction log (SQL Server) database checkpoint SQL Server database backup and restore bulk import and export data data compression Transact-SQL OLE Automation object event notification monitoring and tuning performance T OC collapsed Directory expand directory
The document has been archived and the information here may have been archived and the information here may be outdated
Table
SQL Server 2014 Other versions of SQL Server SQL Server R2 SQL Server 2005
A table is a database object that contains all the data in the database. The logical organization of data in a table is similar to that in a spreadsheet, organized by row and column format. Each row represents a unique record, and each column represents a field in the record. For example, in a table that contains company employee data, each row represents an employee, and each column represents the employee's information, such as employee number, name, address, position, and home phone number.

• The number of tables in the database is limited only by the number of objects allowed in the database (2,147,483,647). A standard user-defined table can have up to 1024 columns. The number of rows in a table is limited only by the storage capacity of the server.

• You can assign attributes to each column in a table and table to control the data and other properties that are allowed. For example, you can create a constraint on a column to suppress null values or provide a default value when no value is specified, or you could specify a key constraint on a table that enforces uniqueness or defines a relationship between tables.

• Data in a table can be compressed by row or page. With data compression, more rows can be stored on the page. For more information, see Data compression.

Types of tables
--------------------------------------------------------------------------------

In addition to the standard roles for basic user-defined tables, SQL Server provides the following types of tables, which play a special role in the database.

Partitioned tables
A partitioned table is a table that divides data horizontally into multiple cells that can be distributed across multiple filegroups in a database. Partitioning enables you to quickly and efficiently access or manage subsets of data while maintaining the integrity of the entire collection, making large tables or indexes easier to manage. By default, SQL Server 2014 supports up to 15,000 partitions. For more information, see Partitioned Tables and partitioned indexes.

Temp table
Temporary tables are stored in tempdb. There are two types of temporal tables: local tables and global tables. They differ in name, visibility, and usability. The name of the local temporary table begins with a single number sign (#), which is visible only to the current user connection, and is removed when the user disconnects from the SQL Server instance. The name of the global temporary table begins with a two number sign (# #) and is visible to any user after it is created, and is deleted when all users referencing the table are disconnected from the instance of SQL Server.

system tables
SQL Server stores the data that defines the server configuration and all of its tables in a special set of tables called system tables. Users cannot directly query or update system tables. You can view the information in a system table from a system view. For more information, see System View (Transact-SQL).

Wide table
A wide table uses sparse columns to increase the total number of columns that a table can contain to 30,000 columns. A sparse column is a normal column that uses an optimized storage method for Null values. Sparse columns reduce the space requirement for null values, but at the expense of increasing the cost of retrieving non-null values. A wide table has defined a column set, which is an untyped XML representation that merges all the sparse columns of a table into a structured output. The number of indexes and statistics is also increased by 1,000 and 30,000 respectively. The maximum size of a wide table row is 8,019 bytes. Therefore, most of the data in any particular row should be NULL. The sum of columns for non-sparse columns and computed columns in a wide table must still not exceed 1,024.

A wide table has the following performance implications.

• A wide table will increase the cost of maintaining the index of the table. We recommend that you limit the number of indexes for a wide table to the number of indexes required by the business logic. The number of indexes increases, and the DML compile-time and memory requirements increase. A nonclustered index should be a filtered index applied to a subset of data. For more information, see Create a filtered index.

• Applications can dynamically add and remove columns in a wide table. When you add or remove a column, the compiled query plan is also invalidated. We recommend that you design an application that matches the scheduled workload so that the schema changes are minimized.

• Performance is affected when data is added and deleted in a wide table. The application must be designed for a predetermined workload so that changes to the table data are minimized.

• Restrict the DML statements that are performed on the wide table to update multiple rows of the clustered key. Compiling and executing these statements will require a lot of memory resources.

• Switching partition operations on wide tables can be slow and may require a lot of memory to be processed. Performance and memory requirements are proportional to the number of columns in the source and destination partitions.

• Update cursors for updating specific columns in a wide table should explicitly list these columns in the FOR UPDATE clause. This will help optimize performance when using cursors.

Common Table Tasks
--------------------------------------------------------------------------------

The following table provides links to common tasks related to creating or modifying tables.

Table Task Topics
Describes how to create a table. CREATE TABLE (Database engine)
Describes how to delete a table. Delete table (Database engine)
Describes how to create a table that contains some or all of the columns in an existing table. Copying tables
Describes how to rename a table. Renaming tables (Database engine)
Describes how to view the properties of a table. View Table Definition
Describes how to determine whether other objects, such as views or stored procedures, depend on a table. To view a table's dependencies

The following table provides links to common tasks related to creating or modifying columns in a table.

Column Task topics
Describes how to add columns to an existing table. To add a column to a table (Database engine)
Describes how to remove columns from a table. Remove a column from a table
Describes how to change the name of a column. Renaming columns (Database engine)
Describes how to copy columns from one table to another by copying only the column definitions or by copying the definitions and data. Copy columns from one table to another table (Database engine)
Describes how to modify a column definition by changing the data type or other properties. modifying columns (Database engine)
Describes how to change the order in which columns are displayed. Change the order of columns in a table
Describes how to create a computed column in a table. Specify computed columns in a table
Describes how to specify a default value for a column. If no other value is provided, this value is used. Specify default values for columns

SQL Server Database