In Service Pack 5, SQL Azure gave the support for executing the stored procedure, "sp_tableoption". This stored procedure can be used to specify the behavior of column values of type varchar(max),nvarchar(max),text,ntext,image etc and also to change lock behavior type on bulk load.
There are 3 types of supported options for sp_tableoption
1. table lock on bulk load :
When it is disabled, it will take row-level lock. When it is enabled, it will take table level lock while doing bulk-load options.
2. text in row: When it is OFF, text,ntext and image column values will be seperately stored in blob and in row pointer to blob will be stored. When it is ON, instead of pointer in row, it will stored the text of those columns.
3. large value types out of row: When it is 1, varchar(max),nvarchar(max) and xml columns in the table are stored outside of the row, with the pointer stored in the row. When it is 0, these columns will be stored inside row itself.
Exec sp_tableoption 'TableName','text in row','OFF'
It returns 0 for success, error number in case of failure.
Read More
Have you written a blog post or article related to this feature and want to get them added here?
Read More...