How do you get to limits of 8060 bytes per row and 8000 per (varchar, nvarchar) value?

by

Inside the Storage Engine: Anatomy of a record

This is for SQL Server 2005

  • record header
    • 4 bytes long
    • two bytes of record metadata (record type)
    • two bytes pointing forward in the record to the NULL bitmap
  • fixed length portion of the record, containing the columns storing data types that have fixed lengths (e.g. bigint, char(10), datetime)
  • NULL bitmap
    • two bytes for count of columns in the record
    • variable number of bytes to store one bit per column in the record, regardless of whether the column is nullable or not (this is different and simpler than SQL Server 2000 which had one bit per nullable column only)
    • this allows an optimization when reading columns that are NULL
  • variable-length column offset array
    • two bytes for the count of variable-length columns
    • two bytes per variable length column, giving the offset to the end of the column value
      versioning tag
  • this is in SQL Server 2005 only and is a 14-byte structure that contains a timestamp plus a pointer into the version store in tempdb

So, for one char(8000)

  • 4 bytes (record header)
  • 8000 fixed length
  • 3 null bitmap
  • 2 bytes to count variable-length
  • 14 timestamp

However, if you had 40 varchar(200) columns

  • 4 bytes (record header)
  • 0 fixed length
  • 6 null bitmap
  • 2 bytes to count variable-length
  • 202 x 40 = 8080
  • 14 timestamp

Total = 8080 + 4 + 6 + 2 + 14 = 8106. WTF? You get a warning when you created this table

I would not get too hung up on it: this information has no practical day to day value

More Related Contents:

  1. Updating image in VB and SQL Server not working [closed]
  2. Where clause not working in sql server
  3. Compare the values of column in SQL Server
  4. T-SQL stored procedure that accepts multiple Id values
  5. How do I escape a percentage sign in T-SQL?
  6. Using RegEx in SQL Server
  7. Get list of databases from SQL Server
  8. SqlServer: Login failed for user
  9. SQL Server 2008 Windows Auth Login Error: The login is from an untrusted domain
  10. SQL Update with row_number()
  11. Calendar table for Data Warehouse
  12. SQL Server default character encoding
  13. SQL Server error on update command – “A severe error occurred on the current command”
  14. Recursive sum in tree structure
  15. SQL Server SP – Pass parameter for “IN” array list?
  16. Why the SQL Server ignore the empty space at the end automatically?
  17. Add default value of datetime field in SQL Server to a timestamp
  18. SQL server schema and default schema
  19. SQL to find first non-numeric character in a string
  20. What is the PostgreSQL equivalent for ISNULL()
  21. SQL Server: What is the difference between CROSS JOIN and FULL OUTER JOIN?
  22. How to decrypt a password from SQL server?
  23. How to get the 1st value before delimiter in sql server
  24. What are the advantages of VistaDB
  25. comparing a column to a list of values in t-sql
  26. How to pivot unknown number of columns & no aggregate in SQL Server?
  27. How to get a list of all the MS SQL Server instances on the local machine?
  28. mysqldump equivalent for SQL Server
  29. Forgot SQL Server Password
  30. Populate Dataset With Table Names From Stored Procedure

Leave a Comment