March 1, 2007 at 5:17 pm
Good to know! Thanks for the article
February 1, 2008 at 9:51 am
Hi
For all you guys looking to find available bytes in a table row for SQL Server 2005 (haven't tested it against previous version but should work) here is a scalar function that accepts a table name and returns teh remaining bytes left for expansion
Hope it helps !!
-- ================================================
-- Template generated from Template Explorer using:
-- Create Scalar Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Ruchir T
-- Create date: 01/02/2008
-- Description:returns the number of bytes left to use for creating new columns
-- =============================================
CREATE FUNCTION available_tablerowsize
(
-- Add the parameters for the function here
@tablename char(50)
)
RETURNS int
AS
BEGIN
-- variables to track fixed and variable column sizes
DECLARE @num_columns int
DECLARE @result int
DECLARE @num_fixed_columns int
DECLARE @fixed_data_size int
DECLARE @var_data_size int
DECLARE @num_var_columns int
DECLARE @max_var_size int
DECLARE @null_bitmap_size int
DECLARE @row_size int
-- Find the total number of columns
select @num_columns = count(*)
from syscolumns,systypes
where syscolumns.id=object_id(@tablename)
and syscolumns.xtype=systypes.xtype
-- Find the size occupied by fixed length columns (Note: not possible to exist outside the 8060 bytes limit)
select @num_fixed_columns = count(*)
from syscolumns,systypes
where syscolumns.id=object_id(@tablename)
and syscolumns.xtype=systypes.xtype and systypes.variable=0
select @fixed_data_size = sum(syscolumns.length)
from syscolumns,systypes
where syscolumns.id=object_id(@tablename)
and syscolumns.xtype=systypes.xtype and systypes.variable=0
-- Find the size occupied by variable length columns within the 8060 page size limit
-- number of variable length columns
select @num_var_columns=count(*)
from syscolumns, systypes
where syscolumns.id=object_id(@tablename)
and syscolumns.xtype=systypes.xtype and systypes.variable=1
-- max size of all variable length columns
select @max_var_size =max(syscolumns.length)
from syscolumns,systypes
where syscolumns.id=object_id(@tablename)
and syscolumns.xtype=systypes.xtype and systypes.variable=1
-- calculate variable length storage
begin
if @num_var_columns>0
set @var_data_size=2+(@num_var_columns*2)+@max_var_size
--set @var_data_size = @num_var_columns*24
else
set @var_data_size=0
end
-- If there are fixed-length columns in the table, a portion of the row, known as the null bitmap, is reserved to manage column nullability.
select @null_bitmap_size = 2 + ((@num_columns+7)/8)
-- Calculate total rowsize
select @row_size = @fixed_data_size + @var_data_size + @null_bitmap_size + 4
-- Return the available bytes in the row available for expansion
select @result = 8060 - @row_size
RETURN @result
END
GO
February 4, 2008 at 6:43 am
I've bookmarked that to take a look at it more detail when I have time! You should also post in the scripts area, more likely to be found there.
February 8, 2008 at 2:56 am
thanks, I am new to SQL server central so did not know that. I have added it there.
February 12, 2008 at 7:33 am
I wasn't aware of this behavior. Good write-up.
(I think I'll still stick with as narrow a table as I can manage in most cases, but it is good to know.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 28, 2008 at 2:46 pm
Thanks for the write up Andy, you inspired me to test the full ability of a row......I wanted to know how many large columns you could cram in until the page filled with pointers. I wrote it up in my blog here: http://www.consortioservices.com/Blog/2008/02/28/MaximumRowSizeInSQLServer2005ToTheLimit.aspx
Eric Johnson - Host of CS Techcast, the podcast for IT pros
February 28, 2008 at 3:32 pm
You've got too much time on your hands! It is interesting though, and nice to have the answer out there. Must be some really niche application for it.
March 4, 2008 at 12:40 pm
Andy,
Thanks for the article. You solved a problem I was having.
I need to rewrite a web app where the users "need" to be able to put thousands of bytes in one or two fields but depending on what type bid it is they will use different fields.
Knowing that "users will be users" I didn't want to risk allowing them to put more than 8k in a row, but neither did I want to screw with text/clob fields.
So now not only can they hang themselves if they so desire (without a lot of code maint headaches on my part) but now I have to use SQL Server 2005. 😀
Eric S. Johnson (2/28/2008)... Andy, you inspired me to test the full ability of a row... I wrote it up here: http://www.consortioservices.com/Blog/2008/02/28/MaximumRowSizeInSQLServer2005ToTheLimit.aspx ...
Eric - love the PS at the end 🙂
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply