When adding new column to table, where is that data stored ?

  • When a row exceeds 8060 bytes, a ROW_OVERFLOW_DATA page and pointer are created, to fit the 8KB page size limit.

    What happens when a new column is added to an existing table :

    Is the data stored at the 'end' of the table, and pointers are created after each existing row in a page to point to this new data, similar to ROW_OVERFLOW_DATA (even if adding new column does not exceed row size over 8060 bytes) ?

    Or is the entire table reorganized so the new column 'fits' in the original page ?

  • The new column has to fit on the original page.
    If it's also varchar/nvarchar/varbinary, then  it  too can have portions of its data  sent out to row_overflow pages

    And your first sentence isn't exactly true. What happens is that the variable columns can have some/all of their contents put on overflow pages. Those pages are not the same structure as the data page, they're closer to how the MAX data types are stored.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • >The new column has to fit on the original page.
    Does that mean the entre table/leaf node pages have to be re-organised ? That way there is no 'row chaining' like in oracle which causes performance issues due to reading from addition pointer pages, correct?

  • There's no such thing as 'row chaining'. A row must fit onto a single page, the only exceptions being that data in varchar, nvarchar & varbinary columns can be partially or completely stored off-page (not good for performance though)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply