Special Table Types
Besides the standard role of basic user-defined tables, SQL Server 2005 provides the following types of tables that serve special purposes in a database:
- Partitioned tables
- Temporary tables
- System tables
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).
Important The SQL Server 2005 Database Engine system tables have been implemented as read-only views for backward compatibility purposes in SQL Server 2005. You cannot directly work with the data in these system tables. We recommend that you access SQL Server metadata by using catalog views.