Say I have the following setup:
use tempdb go set nocount on go create table MyTest ( Column1 varchar(100), Column2 text ) go insert mytest (Column1, Column2) select REPLICATE('a', 100), REPLICATE('a', 100) from sys.syscolumns a, sys.syscolumns b
I'd like to convert each of the columns to varchar(max) like this:
-- processes every page: alter table mytest alter column Column1 varchar(max) -- processes only metadata: alter table mytest alter column Column2 varchar(max)
How can I demonstrate that the first command processes the whole table while the second command only processes metadata. I was thinking of using SET STATISTICS IO which reports thousands of logical reads for the first command and nothing for the other. I was also thinking of using DBCC LOG or fn_dblog. But I wasn't sure how to interpret or tie the results to the queries I issued.