Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Maximum Row Size in SQL Server 2005


Maximum Row Size in SQL Server 2005

Author
Message
Andy Warren
Andy Warren
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: Moderators
Points: 7187 Visits: 2679

I said inserted instead of insteadof:-) Sorry about that. You're right, the standard text columns are not available in insert, update, or delete triggers unless they are set up as instead of rather than after triggers.

Its's a bit off topic, but the thread is light so I'll ask - what size would the columns be if not text types? And I like triggers for auditing myself, but have you considered a profiler or log based option instead?



Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Yelena Varshal
Yelena Varshal
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3480 Visits: 593

Andy,

Nice article and very well written!




Regards,
Yelena Varshal

shashi kant
shashi kant
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 539
 

Andy,

Excellent Article by u ,

But how to overcome or increase the maximum size of row ??

Regards,
shashi kant


vefa
vefa
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 102

Hi Andy,

You are saying this: "you could update a SQL 2000 server, set SQL2K compatibility, and still use the new behavior"

We are using SQL 2000 (sp4). We have 60,65,70,80 compatibility level, so we cannot update compatibility level to use the new behaviour.

How can we do this?

Thanks in advance.

 


Andy Warren
Andy Warren
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: Moderators
Points: 7187 Visits: 2679
Im sorry if I didnt say it very well. If you upgrade your server to SQL 2005 you could leave your existing databases in their current compatability level and take advantage of the change that allows extended rows.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Ian Yates
Ian Yates
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1056 Visits: 445
Good to know!  Thanks for the article



ruchir-628038
ruchir-628038
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 5
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
Andy Warren
Andy Warren
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: Moderators
Points: 7187 Visits: 2679
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.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
ruchir-628038
ruchir-628038
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 5
thanks, I am new to SQL server central so did not know that. I have added it there.
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14369 Visits: 9729
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search