Maximum Row Size in SQL Server 2005
-
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/awarren/2862.asp
-
Good writeup!
-
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
-
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?
-
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.
-
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
-
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,
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.
-
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!
-
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.
-
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,
Nice article and very well written!
Regards,Yelena Varsha
-
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.
-
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.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply