One cornerstone of the relational database model is that there is only one structure that actually stores information in a relational database: the table.
A table is defined as a set of
Size Limits for Rows and Columns
As discussed earlier in this chapter, the maximum row size for a table is 8060 bytes. The maximum size for character and binary datatypes is 8000 bytes. Note, however, that the new Unicode datatypes require 2 bytes per character and thus require twice the amount of storage space as non-Unicode datatypes. This limits the size of
columns to 4000
A table without a clustered index is a heap table. There is no imposed ordering of the data rows for a heap table. Additionally, there is no direct linkage between the pages in a heap table.
In pre-7.0 versions of SQL Server, the data pages were doubly linked in a manner similar to that of a clustered table. To scan the table, SQL Server would look up the first page in the
table and then scan from that point forward, following the page pointers until it reached the last page in the table (
m_nextPage = 0
). Table scanning a linked chain of pages is more efficient when they are contiguous so that it reads the pages in file order, without having to hop around within the file to read the pages in linked page order. Unfortunately, the table could become
How, then, does SQL Server find all the data rows when it has to perform a table scan on a heap table? In SQL Server 2000, each heap table has at least one IAM (Index Allocation Map) page. The address of the first IAM page is stored in the
). The IAM page registers which extents are used by the table. SQL Server can then simply scan the allocated extents referenced by the IAM page, in physical order. This
As discussed earlier, each IAM can map a maximum of 63,903 extents for a table. As a table uses extents beyond the range of those 63,903 extents, more IAM pages are created for the heap table as needed. A heap table also has at least one IAM page for each file on which the heap table has extents allocated. Figure 33.10 shows the structure of a heap table and how its contents are traversed using the IAM pages.
Figure 33.10. The structure of a heap table.
is a table that has a clustered index defined on it. When you create a clustered index, the data rows in the table are physically sorted in the order of the columns in the index key. The data pages are chained together in a doubly linked list (each page points to the
Figure 33.11. A clustered table.