2 out of 2 rated this helpful - Rate this topic

Tables

SQL Server 2012

SQL Server 2005

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 to 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 home telephone number.

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 to 1,024 columns. The number of rows in the table is limited only by the storage capacity of the server.
You can assign properties to the table and to each column in the table to control the data that is allowed and other properties. For example, you can create constraints on a column to 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.
The data in the table can be compressed either by row or by page. Data compression can allow more rows to be stored on a page. For more information, see Data Compression.

Types of Tables

Besides the standard role of basic user-defined tables, SQL Server provides the following types of tables that serve special purposes in a database.

Partitioned Tables

Partitioned tables are tables whose data is horizontally divided into 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 default, SQL Server 2012 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, and 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, and 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 any user after they are created, and they are deleted when all users referencing the table disconnect from the instance of SQL Server.

System Tables

SQL Server stores the data that defines the configuration of the server and all its tables in a special set 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 increase the total of columns that a table can have to 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 to retrieve nonnull values. A wide table has defined a column set, which is an untyped XML representation that combines all the sparse columns of a table into a structured output. The number of indexes and statistics is also increased to 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 maximum number of nonsparse columns plus computed columns in a wide table remains 1,024.

Wide tables have the following performance implications.

Wide tables can increase the cost to maintain indexes on the table. We recommend that the number of indexes on a wide table be limited to the indexes that are required by the business logic. As the number of indexes increases, so does the DML compile-time and memory requirement. Nonclustered indexes should be filtered indexes that are applied to data subsets. For more information, see Create Filtered Indexes.
Applications can dynamically add and remove columns from wide tables. When columns are added or removed, compiled query plans are also invalidated. We recommend that you design an application to 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 to 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 to compile and execute.
Switch partition operations on wide tables can be slow and might require large amounts of memory to process. The performance and memory requirements are proportional to the total number of columns in both the source and target partitions.
Update cursors that update specific columns in a wide table should list the columns explicitly in the FOR UPDATE clause. This will help optimize performance when you use cursors.

Common Table Tasks

The following table provides links to common tasks associated with creating or modifying a table.

Table Tasks	Topic
Describes how to create a table.	Create Tables (Database Engine)
Describes how to delete a table.	Delete Tables (Database Engine)
Describes how to create a new table that contains some or all of the columns in an existing table.	Duplicate Tables
Describes how to rename a table.	Rename Tables (Database Engine)
Describes how to view the properties of the table.	View the Table Definition
Describes how to determine whether other objects such as a view or stored procedure depend on a table.	View the Dependencies of a Table

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

Column Tasks	Topic
Describes how to add columns to an existing table.	Add Columns to a Table (Database Engine)
Describes how to 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 to copy columns from one table to another, copying either just the column definition, or the definition and data.	Copy Columns from One Table to Another (Database Engine)
Describes how to modify a column definition, by changing the data type or other property.	Modify Columns (Database Engine)
Describes how to change the order in which the columns appear.	Change Column Order 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. This value is used if another value is not supplied.	Specify Default Values for Columns

Concepts

Primary and Foreign Key Constraints

Unique Constraints and Check Constraints

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD