Computing the 8060 row-size 1

[harebrain]

Is there a stored procedure that will compute the [maximum] size of a table's rows? It seems like that would be handy to know before altering a table.

 

[SQLDenis]

example for the orders table in the pubs database
use pubs
go

dbcc showcontig('orders') with tableresults
look at the MaximumrecordSize column

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[gmmastros]

Denis,

I'm confused. I came up with this query.

[COLOR=blue]Select[/color] Information_Schema.Columns.Table_Name, 
       Sum([COLOR=#FF00FF]Length[/color]) [COLOR=blue]As[/color] TotalDefinedLength
[COLOR=blue]From[/color]   syscolumns
       [COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] Information_Schema.Columns
          [COLOR=blue]On[/color] [COLOR=#FF00FF]Object_Name[/color](syscolumns.id) = Information_Schema.Columns.Table_Name
          And syscolumns.Name = Information_Schema.Columns.Column_Name
[COLOR=blue]Group[/color] [COLOR=blue]By[/color] Information_Schema.Columns.Table_Name
[COLOR=blue]Order[/color] [COLOR=blue]By[/color] Information_Schema.Columns.Table_Name

The problem is... I'm getting different values. I think my query will tell you the maximum defined size, whereas yours will tell you the maximum used size. What do you think?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

True, here is something to think about

create table testLenTable (col1 decimal(12,4), col2 nvarchar(15),col3 float)


Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[gmmastros]

>> True, here is something to think about

I'm thinking that my query returns the correct number.

When I open the table in enterprise manager, I get...

[tt]
ColumnName Data Type Length
---------- --------- ------
col1 Decimal 9
col2 nvarchar 15
col3 float 8
[/tt]

Since it takes 2 bytes to store nvarchar character, the storage size is 30 for col2. So you end up with...

9 + 30 + 8 which is 47.

When I run my query, I get

[tt]
Table_Name TotalDefinedLength
---------- ------------------
testLenTable 47
[/tt]

Which appears to be correct. Am I missing something here?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

what i am trying to say is that even though the len is 30 characters you can only store 15...so be aware when calculating how much you can fit

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[NoCoolHandle]

Now dont forget that every 8 nullable column's adds 1 bite and varchar fields add around 2 then there is the rowheadder (2 0r 4 bites), and the page headder..32 bites
so.. Add the extra overhad associated with nullable fileds and varchar.. + the row headder and then subtract 32 bites from the 8k page and you get close to gestimating how many rows will fit on a page.

 

[SQLDenis]

and 7 bit columns are only 1 byte not 7

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[harebrain]

Thanks guys!

I was trying to see what my ceiling was. I'm aware of the overhead bytes, but I'm not trying to wring out every last bit; a good ballpark figure is what I was looking for.

George, I thought that info had to be somewhere. I was thinking along those lines, and fortunately we're not (almost never) worried about wide chars.