SQLServerCentral / Discuss Content Posted by Andy Warren / Article Discussions / Article Discussions by Author / Maximum Row Size in SQL Server 2005 / Latest Posts

RE: Maximum Row Size in SQL Server 2005

chris.compton — Tue, 04 Mar 2008 12:40:58 GMT

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) [b]but[/b] now I [i]have[/i] to use SQL Server 2005. :D[quote][b]Eric S. Johnson (2/28/2008)[/b]... 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 ...[/quote]Eric - love the PS at the end :)

RE: Maximum Row Size in SQL Server 2005

Andy Warren — Thu, 28 Feb 2008 15:32:58 GMT

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.

RE: Maximum Row Size in SQL Server 2005

Eric S. Johnson — Thu, 28 Feb 2008 14:46:20 GMT

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.aspxEric Johnson - Host of CS Techcast, the podcast for IT proswww.cstechcast.com

RE: Maximum Row Size in SQL Server 2005

GSquared — Tue, 12 Feb 2008 07:33:58 GMT

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.)

RE: Maximum Row Size in SQL Server 2005

ruchir-628038 — Fri, 08 Feb 2008 02:56:30 GMT

thanks, I am new to SQL server central so did not know that. I have added it there.

RE: Maximum Row Size in SQL Server 2005

Andy Warren — Mon, 04 Feb 2008 06:43:50 GMT

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.

RE: Maximum Row Size in SQL Server 2005

ruchir-628038 — Fri, 01 Feb 2008 09:51:09 GMT

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 expansionHope 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 ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- 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 intASBEGIN -- 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 ENDGO

RE: Maximum Row Size in SQL Server 2005

Ian Yates — Thu, 01 Mar 2007 17:17:00 GMT

Good to know! Thanks for the article

RE: Maximum Row Size in SQL Server 2005

Andy Warren — Wed, 28 Feb 2007 05:27:00 GMT

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.

RE: Maximum Row Size in SQL Server 2005

vefa — Tue, 27 Feb 2007 22:46:00 GMT

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.

RE: Maximum Row Size in SQL Server 2005

shashi kant — Tue, 27 Feb 2007 21:22:00 GMT

Andy,

Excellent Article by u ,

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

Regards,shashi kant

RE: Maximum Row Size in SQL Server 2005

Yelena Varshal — Tue, 27 Feb 2007 15:55:00 GMT

Andy,

Nice article and very well written!

RE: Maximum Row Size in SQL Server 2005

Andy Warren — Tue, 27 Feb 2007 12:17:00 GMT

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?

RE: Maximum Row Size in SQL Server 2005

Kathleen-402235 — Tue, 27 Feb 2007 12:02:00 GMT

Do you mean an "Instead Of" trigger? I've read how to use these in order to be able to access the text column.

I'm not sure how to access the text column in the inserted/deleted tables in an Insert trigger. In any case, we would need to access the data during inserts AND updates since the purpose of our triggers is for auditing and the only way I've seen to do this is using an "Instead Of" trigger, which is more work. I wouldn't mind the additional code involved if our Text fields were really necessary, but in most cases they aren't. The only reason we have them is that people were lazy when creating some of our tables.

RE: Maximum Row Size in SQL Server 2005

Andy Warren — Tue, 27 Feb 2007 11:33:00 GMT

Im not sure about the cost. I would guess pretty similar, but thats just a guess. I'll see if I can find anything that digs into it more. As far as large page sizes, not sure that bigger would yield returns - we already have 64k extents, and I hate to see them make it too easy for people to design rows that are 64k bytes long!

RE: Maximum Row Size in SQL Server 2005

MudLuck — Tue, 27 Feb 2007 11:25:00 GMT

Andy,

What a nice tidbit of information. It's like AWE for data pages! I suppose that the cost in performance to resew the page back togther in memory for a query with allot of these types of rows would be the down side and much slower, how much in terms of execution plan and I/O would be interesting to know.

I also wonder which would be faster a text field with a pointer to an extent or a ROW_OVERFLOW_DATA allocation unit, in theroy both would store the same information though retrival would be slightly diffrent.

Finally I wonder if this is just a hack until they extend the data pages to work with the 64 bit busses that are fast becomming the standard. Like say to 16 kb or even 32 kb pages like Oracle supports. Before the back lash I'm no Oracle DBA just know from past projects.

Enjoy.

RE: Maximum Row Size in SQL Server 2005

Andy Warren — Tue, 27 Feb 2007 11:24:00 GMT

I dont know that I'd agree with that effort entirely. Definitely if you can upgrade to SQL 2005 the use of the new max types makes things a little more flexible, but as a generalization they still store the majority of their data outside of the row - which is good for us as a performance tweak, as it keeps the core row size small for times when we do scans. The other part is even in SQL 2000 you can access text fields as long as you use an inserted trigger. A little more work, but at least its a way to do it.

RE: Maximum Row Size in SQL Server 2005

Kathleen-402235 — Tue, 27 Feb 2007 11:06:00 GMT

Thanks Andy! I've been trying to convince my coworkers for the last 2 years that we should get rid of all of our text fields because they are not accessible in the inserted and deleted tables. Now I have some ammunition

RE: Maximum Row Size in SQL Server 2005

Andy Warren — Tue, 27 Feb 2007 07:46:00 GMT

That is a great question! And to be honest, I didn't think to test it nor do I know the answer! I suspect the update trigger works fine only because while you can't access text/image in a standard trigger, you can access varchar(max) and related types, which makes me think that they've made the behind the scenes paging a bit more transparent to us. Here is the note about the varchar(max) in a trigger from BOL:

ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. Both AFTER and INSTEAD OF triggers support varchar(MAX), nvarchar(MAX), and varbinary(MAX) data in the inserted and deleted tables.

With luck someone will have the answer and will post, if not I'll try to make time in the next week or so to do some testing and report back.

RE: Maximum Row Size in SQL Server 2005

Kathleen-402235 — Tue, 27 Feb 2007 07:31:00 GMT

I'm curious as to how this might affect any update triggers for this table. If the data is treated the same as binary data in that it its stored on a different page and accessed through a pointer, then update triggers would not have access to any of the columns that were moved to a different page. Does anyone know of that's the case? Or are update triggers handled differently in 2005?

RE: Maximum Row Size in SQL Server 2005

Leonaldo Brum — Tue, 27 Feb 2007 02:11:00 GMT

I think that this is a good point - a very good one.

Should you care or not care for permormance, or how the structure of your DB has to be, so should you decide whether you will want to do it or not.

The difference before reading this article and after is that now you know and you can assess the consequences and effects of your decisions, in order both to design a solution that takes them into account and to document the fact properly. Someone will then thank you later for having documented your design option, just as I am now expressing my thanks for this excellent article.

L. Brum

RE: Maximum Row Size in SQL Server 2005

CraigIW — Tue, 27 Feb 2007 02:03:00 GMT

Good writeup!

Maximum Row Size in SQL Server 2005

Andy Warren — Wed, 07 Feb 2007 20:35:00 GMT

Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/awarren/2862.asp