Special Table Types

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. Under a partitioning scenario, operations such as loading data from an OLTP to an OLAP system, for example, can take seconds instead of minutes or hours in previous releases. Maintenance operations that are performed on subsets of data are also performed more efficiently because they target only the data that is required, instead of the whole table.

Partitioning a table makes sense if the table is very large or is expected to become very large, and either of the following is true:

• The table contains, or is expected to contain, lots of data that is used in different ways.

• Queries or updates against the table are not performing as you expect, or maintenance costs exceed predefined maintenance periods.

Partitioned tables support all the properties and features associated with designing and querying standard tables, including constraints, defaults, identity and timestamp values, triggers, and indexes. Therefore, if you want to implement a partitioned view that is local to one server, you should implement a partitioned table instead. For information to help you understand, design, and implement partitioned tables, see Partitioned Tables and Indexes.

Temporary Tables

There are two types of temporary tables: local and global. Local temporary tables are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced. Local temporary tables are deleted after the user disconnects from the instance of SQL Server. Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.

For more information about temporary tables, see Creating and Modifying Table Basics.

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 except through a dedicated administrator connection (DAC) that should be used only under the direction of Microsoft Customer Services. For more information, see Using a Dedicated Administrator Connection. The system tables typically change in each new version of SQL Server. Applications that directly reference system tables may have to be rewritten before they can be upgraded to a newer version of SQL Server with a different version of the system tables. The information in the system tables is made available through the catalog views. For more information, see System Tables (Transact-SQL).

Wide Tables

A wide table is a table that has defined a column set. Wide tables use sparse columns to increase the total of columns that a table can have to 30,000. 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. To create or change a table into a wide table, you add a column set to the table definition. The maximum number of nonsparse columns plus computed columns in a wide table remains 1,024.

By using wide tables, you can create flexible schemas within an application. You can add or drop columns whenever you want. Keep in mind that using wide tables has unique performance considerations, such as increased run-time and compile-time memory requirements. For more information, see Performance Considerations for Wide Tables.

See Also

Other Resources