Is there a tool to calculate row size and which columns would go in_row_data?

SQL Server Administration
1

I just watched a course about how MS SQL tables are stored on the disk and a big portion of that were the IN_ROW_DATA and etc., explaining how fixed-width columns are always in teh IN_ROW_DATA and if any other overflow they get put in the OUT_ROW_DATA and etc.

The lector showed a visual aid of how you can calculate which columns would end in the IN_ROW_DATA when deisgning the table (assuming you knew what you were doing and the data it's going to store) is there some tool or website that can show similar information before the table is created?

I was thinking of a tool/site where I enter what columns I am thinking of making and it tell me based on that how many would be in the IN_ROW, how many in the OUT_ROW, how many bites a row could take, etc.

2

Welcome to forum,

What would this help you accomplish in real world scenario? What are you trying to solve

1 Like
3

If the columns are (MAX) type, by default they will go in the row if there's room left on the page to store them. It's almost impossible to know in advance how many bytes will be available on any given page as data is being added to the db.

One possible tuning technique is to force all (MAX) columns out of row. Then you know it will only use 16 bytes in the main table. Of course there is extra overhead when reading the off-row data, so if you constantly read the MAX data, you likely don't want to force off page.

2 Likes