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