Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Wednesday, 3 June 2015

How Data Records are Stored Inside into SQL Server?

Index Storage Fundamentals

Pages are used by SQL Server to store everything in the database

When space is allocated to database data files, all of the space is divided into pages. During allocation, each page is created to use 8KB (8192 bytes) of space and they are numbered starting at 0 and incrementing 1 for every page allocated. When SQL Server interacts with the database files, the smallest unit in which an I/O operation can occur is at the page level.

There are three primary components to a page: the page header, records, and offset array, as shown in Figure 2-1.
All pages begin with the page header. The header is 96 bytes and contains meta-information about the page, such
as the page number, the owning object, and type of page. At the end of the page is the offset array. The offset array
is 36 bytes and provides pointers to the byte location of the start of rows on the page. Between these two areas are
8060 bytes where records are stored on the page.

Page Types

  •  File header page
  •  Boot page
  •  Page Free Space (PFS) page
  •  Global Allocation Map (GAM) page

  • Shared Global Allocation
  • Map (SGAM) page
  • Differential Changed Map (DCM) page
  • Bulk Changed Map (BCM) page
  • Index Allocation Map (IAM) page
  • Data page
  • Index page
  • Large object (Text and Image) page

They above Category we can discuss only most important as see below

Data Page
Data pages are likely the most prevalent type of pages in any database. Data pages are used to store the data
from rows in the database’s tables. Except for a few data types, all data for a record is located on data pages. The
exception to this rule is columns that store data in LOB data types.

Index Page
Similar to data pages are index pages. These pages provide information on the structure of indexes and where
data pages are located. For clustered indexes, the index pages are used to build the hierarchy of pages that are
used to navigate the clustered index. With non-clustered indexes, index pages perform the same function but are
also used to store the key values that comprise the index.

SQL Server utilizes a number of different organizational structures for storing data in the database

• Heap
• B-Tree
• Columnar

Heap Structure

The default structure for organizing pages is called a heap.

a heap can be envisioned to be a pile of data pages in no particular order,

B-Tree Structure or Balanced-tree

The second available structure that can be used for indexing is the Balanced-tree, or B-tree, structure

organizing indexes in SQL Server and is used by both clustered and nonclustered
In a B-tree, pages are organized in a hierarchical tree structure,

Column Store Structure

used by the new column store non-clustered index type