SQL Clone
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
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: Moderators
Points: 25029 Visits: 2746
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/awarren/2862.asp

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
CraigIW
CraigIW
SSC-Addicted
SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)

Group: General Forum Members
Points: 417 Visits: 235
Good writeup!
Leonaldo Brum
Leonaldo Brum
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 1

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


Kathleen-402235
Kathleen-402235
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 71
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?
Andy Warren
Andy Warren
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: Moderators
Points: 25029 Visits: 2746

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.



Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Kathleen-402235
Kathleen-402235
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 71

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


Andy Warren
Andy Warren
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: Moderators
Points: 25029 Visits: 2746
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.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
MudLuck
MudLuck
Mr or Mrs. 500
Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)

Group: General Forum Members
Points: 535 Visits: 519

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.





Andy Warren
Andy Warren
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: Moderators
Points: 25029 Visits: 2746
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!

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Kathleen-402235
Kathleen-402235
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 71

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.


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