CODEWITHSUNDEEP
sql-serversql-server-2008-r2
Darshan
Darshan

Reputation: 147

How does SQL Server 2008 R2 handle large data with the small page size?

The actual data of SQL Server is stored in pages and each page has a fixed size of 8192 bytes from which 96 bytes has bean used by a header (for pageId, FileID etc.) of that page and renaming 8096 bytes will be used for your actual data storage.

Now what I what to know is if suppose I have created table Employee like this:

CREATE TABLE [dbo].[Employee]
(
    [FristName] [nvarchar](4000) NOT NULL,
    [LastName] [nvarchar](4000) NOT NULL,
    [EmpID] [int] IDENTITY(1,1) NOT NULL
)

and then I inserted a row into it. Then calculate size of that record 

FirstName nvarchar 4000 * 2 bytes(nvarchar size) =  8000 bytes
LastName nvarchar 4000 * 2 bytes(nvarchar size)  =  8000 bytes
EmpID int                                        =     2 bytes

                                         Total   = 16002 bytes

it means each row has size 16002 bytes which is more than the page size of 8096 bytes.

Now my questions are:

  1. How the SQL Server pages are created for this inserted row?
  2. How SQL Server handles large data internally?
  3. And if I create clustered index on EMPID then how it will be handled in B-Tree. i.e. at navigation level (other than leaf level)?

Thanks in advance.

Upvotes: 0

Views: 1294

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294387

See Table and Index Organization ROW_OVERFLOW_DATA Allocation Unit:

For every partition used by a table (heap or clustered table), index, or indexed view, there is one ROW_OVERFLOW_DATA allocation unit. This allocation unit contains zero (0) pages until a data row with variable length columns (varchar, nvarchar, varbinary, or sql_variant) in the IN_ROW_DATA allocation unit exceeds the 8 KB row size limit. When the size limitation is reached, SQL Server moves the column with the largest width from that row to a page in the ROW_OVERFLOW_DATA allocation unit. A 24-byte pointer to this off-row data is maintained on the original page.

Upvotes: 2

Related Questions