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
- Temporary tables
- System tables
- Wide 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:
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.
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.
For more information about temporary tables, see Creating and Modifying Table Basics.
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 2008 Database Engine system tables have been implemented as read-only views for backward compatibility purposes in SQL Server 2008. You cannot directly work with the data in these system tables. We recommend that you access SQL Server metadata by using catalog views. |
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.
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.
No comments:
Post a Comment