{"id":2134,"date":"2014-02-26T11:00:02","date_gmt":"2014-02-26T16:00:02","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2134"},"modified":"2014-11-13T13:41:57","modified_gmt":"2014-11-13T18:41:57","slug":"row-offset-array","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2134\/row-offset-array\/","title":{"rendered":"Data Pages and the Row Offset Array"},"content":{"rendered":"<div>\n<h3>Introduction<\/h3>\n<p>\nTwo weeks ago, I started the Storage Wednesday series. In this series, always on a Wednesday, I am going to write about the way SQL Server stores and accesses data. The list of posts can be found at the end of last week's post, <a href=\"http:\/\/sqlity.net\/en\/2033\/the-page\/\">The Page<\/a>.\n<\/p>\n<p>\nIn <a href=\"http:\/\/sqlity.net\/en\/2033\/the-page\/\">How SQL Server stores Data<\/a> I mentioned that everything in SQL Server is stored in pages. That includes metadata about the database itself as well as catalog data helping SQL Server to find the pages that belong to a specific object or to locate an empty page. However, most importantly it includes the actual table row data. Dependent of the type of data stored on a page, SQL Server assigns a numeric page type to it. Today I would like to invite you to take a look at Page Type 1: The Data Page. We are also going to look at the Row Offset Array, which is an integral part of any data page.\n<\/p>\n<h3>The Data Page<\/h3>\n<p>\nIndependent of the table having a clustered index defined, or it being a heap, the actual row data is always stored in pages of type 1, called data pages. As all other pages too, a data page has a 96 byte page header.<br \/>\nIn the header, information about the page is stored. You can for example find a reference to the table (and index) that the page belongs to in here. In addition, on a data page, one of the fields contains the number or rows stored on that page. For additional information on the page header check out last week's post about <a href=\"http:\/\/sqlity.net\/en\/2088\/page-header-explained\/\">the page header<\/a>.\n<\/p>\n<p>\nAll the way at the end of the page, you can find the row offset array or slot array. The slot array is a list of 2 byte pointers to the beginning of each row. The data belonging to a row together with its row header is called a slot. The slot array allows SQL Server to quickly find the beginning of each row. It also allows for the rows in a clustered index to be stored out of order within a page; the slot array determines the logical order (the index order) even if it is different from the physical order of the row on the page.\n<\/p>\n<h3>Row Offset Array Example<\/h3>\n<p>\nLet us look at that slot array in a little more detail:\n<\/p>\n<p>\n<a href=\"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/The_row_offset_array_or_slot_array.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/The_row_offset_array_or_slot_array.jpg\" alt=\"The Row Offset Array or Slot Array\" width=\"794\" height=\"656\" class=\"aligncenter size-full wp-image-2136\" srcset=\"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/The_row_offset_array_or_slot_array.jpg 794w, https:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/The_row_offset_array_or_slot_array-300x247.jpg 300w, https:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/The_row_offset_array_or_slot_array-150x123.jpg 150w\" sizes=\"auto, (max-width: 794px) 100vw, 794px\" \/><\/a>\n<\/p>\n<p>\nIn this image I have highlighted the three slot array entries as well as the first bytes of the slots they are pointing to. I did cut out a few lines of the output to make it fit on the screen. I also highlighted the slot count value in the header. Below the memory dump <span class=\"tt\">DBCC PAGE<\/span> printed a decoded form on the slot array.\n<\/p>\n<p>\nThe first thing to note is that the address on the left is the memory address that the page got loaded into. Only the last four digits of this value are relevant to us here. Also, every value on a page is stored on the page in revers byte order. So a <span class=\"tt\">0300<\/span> actually represents the hex value <span class=\"tt\">0x0003<\/span>. In addition, the slot array is also stored in reverse order. That means that the last two bytes of the page always point to the beginning of the first row on the page.\n<\/p>\n<p>\nYou might have also noticed that each of the three rows starts with the same byte pattern. That is the row header. We will talk about the row header next week.\n<\/p>\n<h3>Summary<\/h3>\n<p>\nIn SQL Server, Page Type 1 is used to store the actual data. Each data page is organized in slots, each containing the data of a single row. The starting position of each slot is noted in the row offset array at the end of the page.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>SQL Server stores actual table data in pages of type 1, data pages. Read on to see how to navigate a data page and how to find each row using the row offset array.<\/p>\n<p> <a href=\"https:\/\/sqlity.net\/en\/2134\/row-offset-array\/\">[more&#8230;]<\/a><\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[5,27,14,105],"tags":[111,107,114,15,109],"class_list":["post-2134","post","type-post","status-publish","format-standard","hentry","category-general","category-series","category-sql-server-internals","category-storage-wednesday","tag-dbcc-page","tag-page","tag-page-header","tag-sql-server","tag-storage-engine"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Data Pages and the Row Offset Array - sqlity.net<\/title>\n<meta name=\"description\" content=\"SQL Server stores actual table data in pages of type 1, data pages. See how to navigate a data page and how to find each row using the row offset array.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sqlity.net\/en\/2134\/row-offset-array\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Data Pages and the Row Offset Array - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"SQL Server stores actual table data in pages of type 1, data pages. See how to navigate a data page and how to find each row using the row offset array.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2134\/row-offset-array\/\" \/>\n<meta property=\"og:site_name\" content=\"sqlity.net\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/sqlity.net\" \/>\n<meta property=\"article:published_time\" content=\"2014-02-26T16:00:02+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:41:57+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/The_row_offset_array_or_slot_array.jpg\" \/>\n<meta name=\"author\" content=\"Sebastian Meine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@sqlity\" \/>\n<meta name=\"twitter:site\" content=\"@sqlity\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sebastian Meine\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2134\\\/row-offset-array\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2134\\\/row-offset-array\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"Data Pages and the Row Offset Array\",\"datePublished\":\"2014-02-26T16:00:02+00:00\",\"dateModified\":\"2014-11-13T18:41:57+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2134\\\/row-offset-array\\\/\"},\"wordCount\":638,\"commentCount\":2,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2134\\\/row-offset-array\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/The_row_offset_array_or_slot_array.jpg\",\"keywords\":[\"DBCC PAGE\",\"page\",\"Page Header\",\"SQL Server\",\"storage engine\"],\"articleSection\":[\"General\",\"Series\",\"SQL Server Internals\",\"Storage Wednesday\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2134\\\/row-offset-array\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2134\\\/row-offset-array\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2134\\\/row-offset-array\\\/\",\"name\":\"Data Pages and the Row Offset Array - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2134\\\/row-offset-array\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2134\\\/row-offset-array\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/The_row_offset_array_or_slot_array.jpg\",\"datePublished\":\"2014-02-26T16:00:02+00:00\",\"dateModified\":\"2014-11-13T18:41:57+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"SQL Server stores actual table data in pages of type 1, data pages. See how to navigate a data page and how to find each row using the row offset array.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2134\\\/row-offset-array\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2134\\\/row-offset-array\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2134\\\/row-offset-array\\\/#primaryimage\",\"url\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/The_row_offset_array_or_slot_array.jpg\",\"contentUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/02\\\/The_row_offset_array_or_slot_array.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2134\\\/row-offset-array\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Data Pages and the Row Offset Array\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\",\"name\":\"sqlity.net\",\"description\":\"Quality for SQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sqlity.net\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\",\"name\":\"Sebastian Meine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"caption\":\"Sebastian Meine\"},\"sameAs\":[\"http:\\\/\\\/sqlity.net\",\"https:\\\/\\\/x.com\\\/sqlity\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Data Pages and the Row Offset Array - sqlity.net","description":"SQL Server stores actual table data in pages of type 1, data pages. See how to navigate a data page and how to find each row using the row offset array.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/sqlity.net\/en\/2134\/row-offset-array\/","og_locale":"en_US","og_type":"article","og_title":"Data Pages and the Row Offset Array - sqlity.net","og_description":"SQL Server stores actual table data in pages of type 1, data pages. See how to navigate a data page and how to find each row using the row offset array.","og_url":"https:\/\/sqlity.net\/en\/2134\/row-offset-array\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-02-26T16:00:02+00:00","article_modified_time":"2014-11-13T18:41:57+00:00","og_image":[{"url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/The_row_offset_array_or_slot_array.jpg","type":"","width":"","height":""}],"author":"Sebastian Meine","twitter_card":"summary_large_image","twitter_creator":"@sqlity","twitter_site":"@sqlity","twitter_misc":{"Written by":"Sebastian Meine","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/2134\/row-offset-array\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2134\/row-offset-array\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"Data Pages and the Row Offset Array","datePublished":"2014-02-26T16:00:02+00:00","dateModified":"2014-11-13T18:41:57+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2134\/row-offset-array\/"},"wordCount":638,"commentCount":2,"image":{"@id":"https:\/\/sqlity.net\/en\/2134\/row-offset-array\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/The_row_offset_array_or_slot_array.jpg","keywords":["DBCC PAGE","page","Page Header","SQL Server","storage engine"],"articleSection":["General","Series","SQL Server Internals","Storage Wednesday"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/2134\/row-offset-array\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2134\/row-offset-array\/","url":"https:\/\/sqlity.net\/en\/2134\/row-offset-array\/","name":"Data Pages and the Row Offset Array - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/2134\/row-offset-array\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/2134\/row-offset-array\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/The_row_offset_array_or_slot_array.jpg","datePublished":"2014-02-26T16:00:02+00:00","dateModified":"2014-11-13T18:41:57+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"SQL Server stores actual table data in pages of type 1, data pages. See how to navigate a data page and how to find each row using the row offset array.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2134\/row-offset-array\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2134\/row-offset-array\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/2134\/row-offset-array\/#primaryimage","url":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/The_row_offset_array_or_slot_array.jpg","contentUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2014\/02\/The_row_offset_array_or_slot_array.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2134\/row-offset-array\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"Data Pages and the Row Offset Array"}]},{"@type":"WebSite","@id":"https:\/\/sqlity.net\/en\/#website","url":"https:\/\/sqlity.net\/en\/","name":"sqlity.net","description":"Quality for SQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sqlity.net\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c","name":"Sebastian Meine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","caption":"Sebastian Meine"},"sameAs":["http:\/\/sqlity.net","https:\/\/x.com\/sqlity"]}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-yq","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2134","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/comments?post=2134"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2134\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2134"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2134"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2134"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}