Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Maximum Row Size in SQL Server 2005 Expand / Collapse
Author
Message
Posted Tuesday, February 27, 2007 12:17 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 2:35 PM
Points: 6,779, Visits: 1,868

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
Post #347919
Posted Tuesday, February 27, 2007 3:55 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 12:24 PM
Points: 3,475, Visits: 579

Andy,

Nice article and very well written!




Regards,
Yelena Varshal

Post #347979
Posted Tuesday, February 27, 2007 9:22 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 1:39 AM
Points: 162, Visits: 508
 

Andy,

Excellent Article by u ,

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

Regards,
shashi kant

Post #348025
Posted Tuesday, February 27, 2007 10:46 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, September 8, 2013 8:30 PM
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.

 

Post #348036
Posted Wednesday, February 28, 2007 5:27 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 2:35 PM
Points: 6,779, Visits: 1,868
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
Post #348084
Posted Thursday, March 1, 2007 5:17 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444
Good to know!  Thanks for the article


Post #348668
Posted Friday, February 1, 2008 9:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 15, 2008 1:47 PM
Points: 4, 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






Post #450617
Posted Monday, February 4, 2008 6:43 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 2:35 PM
Points: 6,779, Visits: 1,868
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
Post #451127
Posted Friday, February 8, 2008 2:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 15, 2008 1:47 PM
Points: 4, Visits: 5
thanks, I am new to SQL server central so did not know that. I have added it there.
Post #453122
Posted Tuesday, February 12, 2008 7:33 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #454425
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse