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 Wednesday, February 7, 2007 8:35 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 6:56 AM
Points: 6,804, Visits: 1,934
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
Post #343316
Posted Tuesday, February 27, 2007 2:03 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 3:55 AM
Points: 92, Visits: 223
Good writeup!
Post #347739
Posted Tuesday, February 27, 2007 2:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 10, 2007 2:33 AM
Points: 4, 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

Post #347741
Posted Tuesday, February 27, 2007 7:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 2, 2012 12:56 PM
Points: 8, 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?
Post #347787
Posted Tuesday, February 27, 2007 7:46 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 6:56 AM
Points: 6,804, Visits: 1,934

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
Post #347793
Posted Tuesday, February 27, 2007 11:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 2, 2012 12:56 PM
Points: 8, 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

Post #347900
Posted Tuesday, February 27, 2007 11:24 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 6:56 AM
Points: 6,804, Visits: 1,934
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
Post #347904
Posted Tuesday, February 27, 2007 11:25 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Saturday, September 6, 2014 11:27 PM
Points: 126, Visits: 505

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.




Post #347905
Posted Tuesday, February 27, 2007 11:33 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 6:56 AM
Points: 6,804, Visits: 1,934
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
Post #347906
Posted Tuesday, February 27, 2007 12:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 2, 2012 12:56 PM
Points: 8, 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.

Post #347913
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse