The sp_chgattribute tabname system procedure supports features introduced in Adaptive Server 15.7 SP100.
A new configuration value, 2, has been introduced for the dealloc_first_txtpg parameter. Setting the parameter to this value allows you to not deallocate the first text page after NULL update, as a result, the text pointer will not be set to NULL even after null update.
Whether the first text page will be deallocated after NULL update depends on the combination of this table parameter and the database option deallocate first text page.
DB setting (deallocate first text page) | 0 1 2 --------------------------------------------------------- dealloc_first_txtp - true | Y Y N dealloc_first_txtp - false | N N N
The output from sp_help indicates whether first text page will be deallocated, as seen below.
> sp_chgattribute mytab, "dealloc_first_txtpg", 1 'dealloc_first_txtpg' attribute of object 'mytab' changed to 1. (return status = 0) 1> 2> sp_help mytab Name Owner Object_type Object_status Create_date ------- ------- ------------- -------------------------- -------------------- mytab dbo user table deallocate first text page Jan 22 2013 9:45PM > sp_chgattribute mytab, "dealloc_first_txtpg", 2 'dealloc_first_txtpg' attribute of object 'mytab' changed to 2. (return status = 0) 1> 2> sp_help mytab Name Owner Object_type Object_status Create_date ------- ------- ------------- ---------------------- -------------------- mytab dbo user table keep first text page Jan 22 2013 9:45PM
Y: deallocate first text page after null update
N: not deallocate first text page after null update
To set the parameter, use:
sp_chgattribute tabname, "dealloc_first_txtpg", 0 | 1 | 2