Database Reference
In-Depth Information
If you rebuilt the table with the
alter table dbo.AlterDemo rebuild
command and checked the column offsets
again, you would see the results shown in Figure
1-21
.
Figure 1-21.
Table alteration: Column offsets after table rebuild
As you see, the table rebuild reclaims the unused space from the rows.
Finally, table alteration requires SQL Server to obtain a Schema Modification (SCH-M) lock on the table. It makes
the table inaccessible by another session for the duration of the alteration.
■
Note
We will talk about schema locks in detail in Chapter 23, “Schema Locks.”
Summary
SQL Server stores data in databases that consist of one or more transaction log files and one or more data files. Data
files are combined into filegroups. Filegroups abstract the database file structure from database objects, which are
logically stored in the filegroups rather than in database files.
SQL Server always zeros out transaction logs during a database restore and log file auto-growth. By default, it
also zeros out the data files unless instant file initialization is enabled. Instant file initialization significantly decreases
database restore time and makes data file auto-growth instant. However, there is a small security risk associated with
instant file initialization as the uninitialized part of the database may contain data from previously deleted OS files.
Nevertheless, it is recommended that you enable instant file initialization if such a risk is acceptable.
SQL Server stores information on 8K logical data pages combined into extents. There are two types of extents. Mixed
extents store data from different objects. Uniform extents store data that belongs to a single object. SQL Server stores the
first eight object pages in mixed extents. After that, only uniform extents are used during object space allocation.
SQL Server uses special map pages to track allocations in the file. There are several allocation map types. GAM
pages track what extents are allocated. SGAM pages track available mixed extents. IAM pages track extents that are
used by the allocation units on the object (partition) level. PFS stores several page attributes, including free space
available on the page, in heap tables and in row-overflow and LOB pages.
SQL Server stores actual data in data rows. There are two different kinds of data types available. Fixed-length data
types always use the same storage space regardless of the value even when it is NULL. Variable-length data storage
uses the actual data value size.
The fixed-length part of the row and internal overhead must fit into a single data page. Variable-length data can be
stored in separate data pages, such as row-overflow and LOB pages, depending on the actual data size and data type.
SQL Server reads the data pages into a memory cache called the buffer pool. When data is modified, SQL Server
synchronously writes the log record into the transaction log. It saves the modified data pages asynchronously during
the checkpoint process.
SQL Server is a very I/O intensive application, and reducing the number of I/O operations helps to improve the
performance of systems. It is beneficial to reduce the size of data rows by using correct data types. This allows you to
put more rows in the data page and decreases the number of data pages to be processed during scan operations.
You need to be careful when altering tables. This process never decreases the size of rows. The unused space
from the rows can be reclaimed by rebuilding a table or clustered index.
