Set Up the mymsgs Example Table

An example that creates the mymsgs table from the pubs2 database, specifying text rather than varchar for the description column in preparation for migrating the column’s contents from off-row to in-row storage.

1> use pubs2
2> go
1> exec sp_drop_object mymsgs, 'table'
2> go
1> create table mymsgs (

          error                int               not null
        , severity             smallint          not null
        , dlevel               smallint          not null
        , description          text
        , langid               smallint          null
        , sqlstate             varchar (5)       null

) lock datarows
2> go
1> insert mymsgs select * from master..sysmessages
2> go
(9564 rows affected)
1> exec sp_spaceusage display, 'table', mymsgs
2> go
All the page counts in the result set are in the unit 'KB'.
 OwnerName TableName IndId  NumRows UsedPages RsvdPages ExtentUtil ExpRsvdPages  
PctBloatUsedPages  PctBloatRsvdPages
 --------- --------- ----- ------- --------- --------- ---------- ------------ ---------------------- ------------------
 dbo       mymsgs       0    9564.0    372.0     384.0      96.87        320.0
           16.25             20.00
 dbo       mymsgs     255    NULL    19132.0   19140.0      99.95      19136.0
           00                 0.02
1> use pubs2
1> 
2> dump tran pubs2 with no_log
1> 
2> /* Drop the spaceusage stats table before each run */
3> exec sp_drop_object spaceusage_object, 'table'
Dropping table spaceusage_object
(return status = 0)
1> 
2> exec sp_spaceusage archive, 'table', mymsgs
Data was successfully archived into table 'pubs2.dbo.spaceusage_object'.
(return status = 0)