Jon Gurgul's Blog

Tag: Internals

  • SQL Server Page Types

    —

    by

    Jon Gurgul
    in

    There are lots of different page types and Paul Randall list them in his article. However as I tend to have a rather poor memory for remembering them first off here are the ones that can be anywhere within the database. m_type 1  DATA_PAGE 2  INDEX_PAGE 3 TEXT_MIX_PAGE 4 TEXT_TREE_PAGE 7 SORT_PAGE 10 IAM_PAGE Now…

  • SQL Server Row Internals

    —

    by

    Jon Gurgul
    in

    Row Storage A Row is stored on a Page which is 8192 Bytes, and this is broken down in two key areas when considering physical storage. Firstly on all pages there is a 96 Byte Header. In addition to this we also need to store the Row structure as well as its location in the…

  • SQL Server Max Row Size 8060 Part 2

    —

    by

    Jon Gurgul
    in

    In my previous post on row maximum length I showed that the largest row size that could be stored on page was 8053 byte. So to recap a table with 2 fixed length columns CHAR(8000) & CHAR(53) the internal storage would look as below, with an unused space of 34 bytes. Page Header 96 bytes…

  • SQL Servers Virtual Columns and Row Cracking

    —

    by

    Jon Gurgul
    in

    In order to access row structures there are several functions within SQL Server that are beneficial. I have listed these below with the descriptions taken from the actual functions. sys.fn_GetRowsetIdFromrowdump — Cracks the output of %%rowdump%% virtual column and returns the rowset id sys.fn_rowdumpCracker — Cracks the output of %%rowdump%% virtual column sys.fn_physlocFormatter — Formats the…

  • SQL Server Max Row Size 8060

    —

    by

    Jon Gurgul
    in

    When asked the size of the largest row that could be placed on to a single SQL Server data page the answer that most give is 8060 bytes. If you try to create a fixed length table with columns that total this length however it throws an error. Please create a database to run this test and…