1:  -- clean up any test artifacts
   2:  if object_id('test1') is not null drop table test1
   3:  if object_id('test2') is not null drop table test2
   4:  if object_id('test') is not null drop view test
   5:  GO
   6:   
   7:  -- the partition column must have a CHECK 
   8:  -- and be part of the PRIMARY KEY
   9:  create table test1 (col1 int 
  10:      primary key check (col1 = 1), col2 varchar(50))
  11:  GO
  12:   
  13:  -- the partition column must have a CHECK 
  14:  -- and be part of the PRIMARY KEY
  15:  create table test2 (col1 int 
  16:      primary key check (col1 = 2), col2 varchar(50))
  17:  GO
  18:   
  19:  -- the partition view must use UNION ALL
  20:  create view test as 
  21:      select * from test1 union all
  22:      select * from test2
  23:  GO
  24:   
  25:  -- add a record to each, let the view 
  26:  -- determine the correct table
  27:  insert into test select 1, 'one'
  28:  insert into test select 2, 'two'
  29:  GO
  30:   
  31:  -- take a look, it's all partitioned correctly
  32:  select * from test1
  33:  select * from test2
  34:  GO
  35:   
  36:  -- clear the table for another test
  37:  delete from test
  38:  -- insert into the first table,, like we did above
  39:  insert into test select 1, 'one'
  40:  -- now let the view move it to the 
  41:  -- other table with an update
  42:  update test set col1 = 2 where col1  = 1
  43:  GO
  44:   
  45:  -- take a look, it's all moved like we want; magic
  46:  select * from test1
  47:  select * from test2