Rows in Pages

• ajay_dekavadiya

  Hmm,
  joeydj, I have some doubts about your MS tutorial. First y, I did not know that there is a specific type for texts and images. I supposed that texts and images are required more than one pages generally since their size generally involves this. So would you please explain this to me
  
  Thanks
  

  
  
  

• Lug Vélez Schmitz

  If you could please reformulate your question a bit more specifically, it would be easier to answer ;)

  Anyhow, as a first note let me say that in SQL pages are the phisical units of storage, while rows can be considered as logical units. Say, when you perform a query and process results in application, you think of rows, while when you are TUNING the query, you think about disk accesses, that is, page read/writes.

  
  
  

• EZ1976

  Pages and Extents

  The fundamental unit of data storage in MicrosoftR SQL Server is the page. In SQL Server 2000, the page size is 8 KB. This means SQL Server 2000 databases have 128 pages per megabyte.

  The start of each page is a 96-byte header used to store system information, such as the type of page, the amount of free space on the page, and the object ID of the object owning the page.

  The table shows eight types of pages in the data files of a SQL Server 2000 database.

  Page type	Contents
  Data	Data rows with all data except text, ntext, and image data.
  Index	Index entries.
  Text/Image	Text, ntext, and image data.
  Global Allocation Map, Secondary Global Allocation Map	Information about allocated extents.
  Page Free Space	Information about free space available on pages.
  Index Allocation Map	Information about extents used by a table or index.
  Bulk Changed Map	Information about extents modified by bulk operations since the last BACKUP LOG statement.
  Differential Changed Map	Information about extents that have changed since the last BACKUP DATABASE statement.

  
  

  Log files do not contain pages; they contain a series of log records.

  Data pages contain all the data in data rows except text, ntext, and image data, which is stored in separate pages. Data rows are placed serially on the page starting immediately after the header. A row offset table starts at the end of the page. The row offset table contains one entry for each row on the page and each entry records how far the first byte of the row is from the start of the page. The entries in the row offset table are in reverse sequence from the sequence of the rows on the page.

   

  Rows cannot span pages in SQL Server. In SQL Server 2000, the maximum amount of data contained in a single row is 8060 bytes, not including text, ntext, and image data.

  Extents are the basic unit in which space is allocated to tables and indexes. An extent is 8 contiguous pages, or 64 KB. This means SQL Server 2000 databases have 16 extents per megabyte.

  To make its space allocation efficient, SQL Server 2000 does not allocate entire extents to tables with small amounts of data. SQL Server 2000 has two types of extents:

  • Uniform extents are owned by a single object; all eight pages in the extent can only be used by the owning object.
    
    
  • Mixed extents are shared by up to eight objects.

  A new table or index is usually allocated pages from mixed extents. When the table or index grows to the point that it has eight pages, it is switched to uniform extents. If you create an index on an existing table that has enough rows to generate eight pages in the index, all allocations to the index are in uniform extents.

  
  
  
• Rows in Pages