143 lines
9.6 KiB
SQL
143 lines
9.6 KiB
SQL
-- 1. Drop Schema
|
|
create database db_to_be_dropped;
|
|
create table db_to_be_dropped.t0(id int primary key, c int, name char(20));
|
|
create table db_to_be_dropped.t1(id int primary key, c int, name char(20)) PARTITION BY RANGE(id) ( PARTITION p0 VALUES LESS THAN (0), PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN MAXVALUE );
|
|
|
|
create index k1 on db_to_be_dropped.t0 (name);
|
|
create index k2 on db_to_be_dropped.t0(c);
|
|
create index k1 on db_to_be_dropped.t1(name);
|
|
create index k2 on db_to_be_dropped.t1(c);
|
|
create index k3 on db_to_be_dropped.t1 (id, c);
|
|
|
|
insert into db_to_be_dropped.t0 values (1, 2, "123"), (2, 3, "123");
|
|
insert into db_to_be_dropped.t1 values (1, 2, "123"), (2, 3, "123");
|
|
-- 2. Drop/Truncate Table
|
|
create database table_to_be_dropped_or_truncated;
|
|
create table table_to_be_dropped_or_truncated.t0_dropped(id int primary key, c int, name char(20));
|
|
create table table_to_be_dropped_or_truncated.t1_dropped(id int primary key, c int, name char(20)) PARTITION BY RANGE(id) ( PARTITION p0 VALUES LESS THAN (0), PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN MAXVALUE );
|
|
create table table_to_be_dropped_or_truncated.t0_truncated(id int primary key, c int, name char(20));
|
|
create table table_to_be_dropped_or_truncated.t1_truncated(id int primary key, c int, name char(20)) PARTITION BY RANGE(id) ( PARTITION p0 VALUES LESS THAN (0), PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN MAXVALUE );
|
|
|
|
create index k1 on table_to_be_dropped_or_truncated.t0_dropped (name);
|
|
create index k2 on table_to_be_dropped_or_truncated.t0_dropped (c);
|
|
create index k1 on table_to_be_dropped_or_truncated.t1_dropped (name);
|
|
create index k2 on table_to_be_dropped_or_truncated.t1_dropped (c);
|
|
create index k3 on table_to_be_dropped_or_truncated.t1_dropped (id, c);
|
|
|
|
create index k1 on table_to_be_dropped_or_truncated.t0_truncated (name);
|
|
create index k2 on table_to_be_dropped_or_truncated.t0_truncated (c);
|
|
create index k1 on table_to_be_dropped_or_truncated.t1_truncated (name);
|
|
create index k2 on table_to_be_dropped_or_truncated.t1_truncated (c);
|
|
create index k3 on table_to_be_dropped_or_truncated.t1_truncated (id, c);
|
|
|
|
insert into table_to_be_dropped_or_truncated.t0_dropped values (1, 2, "123"), (2, 3, "123");
|
|
insert into table_to_be_dropped_or_truncated.t1_dropped values (1, 2, "123"), (2, 3, "123");
|
|
|
|
insert into table_to_be_dropped_or_truncated.t0_truncated values (1, 2, "123"), (2, 3, "123");
|
|
insert into table_to_be_dropped_or_truncated.t1_truncated values (1, 2, "123"), (2, 3, "123");
|
|
|
|
-- 3.1. Drop/Truncate Table Partition
|
|
create database partition_to_be_dropped_or_truncated;
|
|
create table partition_to_be_dropped_or_truncated.t0_dropped(id int primary key, c int, name char(20));
|
|
create table partition_to_be_dropped_or_truncated.t1_dropped(id int primary key, c int, name char(20)) PARTITION BY RANGE(id) ( PARTITION p0 VALUES LESS THAN (0), PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN MAXVALUE );
|
|
create table partition_to_be_dropped_or_truncated.t0_truncated(id int primary key, c int, name char(20));
|
|
create table partition_to_be_dropped_or_truncated.t1_truncated(id int primary key, c int, name char(20)) PARTITION BY RANGE(id) ( PARTITION p0 VALUES LESS THAN (0), PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN MAXVALUE );
|
|
|
|
create index k1 on partition_to_be_dropped_or_truncated.t0_dropped (name);
|
|
create index k2 on partition_to_be_dropped_or_truncated.t0_dropped (c);
|
|
create index k1 on partition_to_be_dropped_or_truncated.t1_dropped (name);
|
|
create index k2 on partition_to_be_dropped_or_truncated.t1_dropped (c);
|
|
create index k3 on partition_to_be_dropped_or_truncated.t1_dropped (id, c);
|
|
|
|
create index k1 on partition_to_be_dropped_or_truncated.t0_truncated (name);
|
|
create index k2 on partition_to_be_dropped_or_truncated.t0_truncated (c);
|
|
create index k1 on partition_to_be_dropped_or_truncated.t1_truncated (name);
|
|
create index k2 on partition_to_be_dropped_or_truncated.t1_truncated (c);
|
|
create index k3 on partition_to_be_dropped_or_truncated.t1_truncated (id, c);
|
|
|
|
insert into partition_to_be_dropped_or_truncated.t0_dropped values (1, 2, "123"), (2, 3, "123");
|
|
insert into partition_to_be_dropped_or_truncated.t1_dropped values (1, 2, "123"), (2, 3, "123");
|
|
|
|
insert into partition_to_be_dropped_or_truncated.t0_truncated values (1, 2, "123"), (2, 3, "123");
|
|
insert into partition_to_be_dropped_or_truncated.t1_truncated values (1, 2, "123"), (2, 3, "123");
|
|
|
|
-- 3.2. Remove/Alter Table Partitioning
|
|
create database partition_to_be_removed_or_altered;
|
|
create table partition_to_be_removed_or_altered.t_removed(id int primary key, c int, name char(20)) PARTITION BY RANGE(id) ( PARTITION p0 VALUES LESS THAN (0), PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN MAXVALUE );
|
|
create table partition_to_be_removed_or_altered.t_altered(id int primary key, c int, name char(20)) PARTITION BY RANGE(id) ( PARTITION p0 VALUES LESS THAN (0), PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN MAXVALUE );
|
|
|
|
create index k1 on partition_to_be_removed_or_altered.t_removed (name);
|
|
create index k2 on partition_to_be_removed_or_altered.t_removed (c);
|
|
create index k3 on partition_to_be_removed_or_altered.t_removed (id, c);
|
|
|
|
create index k1 on partition_to_be_removed_or_altered.t_altered (name);
|
|
create index k2 on partition_to_be_removed_or_altered.t_altered (c);
|
|
create index k3 on partition_to_be_removed_or_altered.t_altered (id, c);
|
|
|
|
insert into partition_to_be_removed_or_altered.t_removed values (1, 2, "123"), (2, 3, "123");
|
|
|
|
insert into partition_to_be_removed_or_altered.t_altered values (1, 2, "123"), (2, 3, "123");
|
|
|
|
-- 4. Drop Table Index/PrimaryKey
|
|
create database index_or_primarykey_to_be_dropped;
|
|
create table index_or_primarykey_to_be_dropped.t0(id int primary key nonclustered, c int, name char(20));
|
|
create table index_or_primarykey_to_be_dropped.t1(id int primary key nonclustered, c int, name char(20)) PARTITION BY RANGE(id) ( PARTITION p0 VALUES LESS THAN (0), PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN MAXVALUE );
|
|
|
|
create index k1 on index_or_primarykey_to_be_dropped.t0 (name);
|
|
create index k2 on index_or_primarykey_to_be_dropped.t0 (c);
|
|
create index k1 on index_or_primarykey_to_be_dropped.t1 (name);
|
|
create index k2 on index_or_primarykey_to_be_dropped.t1 (c);
|
|
create index k3 on index_or_primarykey_to_be_dropped.t1 (id, c);
|
|
|
|
insert into index_or_primarykey_to_be_dropped.t0 values (1, 2, "123"), (2, 3, "123");
|
|
insert into index_or_primarykey_to_be_dropped.t1 values (1, 2, "123"), (2, 3, "123");
|
|
-- 5. Drop Table INDEXES
|
|
create database indexes_to_be_dropped;
|
|
create table indexes_to_be_dropped.t0(id int primary key nonclustered, c int, name char(20));
|
|
create table indexes_to_be_dropped.t1(id int primary key nonclustered, c int, name char(20)) PARTITION BY RANGE(id) ( PARTITION p0 VALUES LESS THAN (0), PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN MAXVALUE );
|
|
|
|
create index k1 on indexes_to_be_dropped.t0 (name);
|
|
create index k2 on indexes_to_be_dropped.t0 (c);
|
|
create index k1 on indexes_to_be_dropped.t1 (name);
|
|
create index k2 on indexes_to_be_dropped.t1 (c);
|
|
create index k3 on indexes_to_be_dropped.t1 (id, c);
|
|
|
|
insert into indexes_to_be_dropped.t0 values (1, 2, "123"), (2, 3, "123");
|
|
insert into indexes_to_be_dropped.t1 values (1, 2, "123"), (2, 3, "123");
|
|
-- 6. Drop Table Column/Columns
|
|
create database column_s_to_be_dropped;
|
|
create table column_s_to_be_dropped.t0_column(id int primary key nonclustered, c int, name char(20));
|
|
create table column_s_to_be_dropped.t1_column(id int primary key nonclustered, c int, name char(20)) PARTITION BY RANGE(id) ( PARTITION p0 VALUES LESS THAN (0), PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN MAXVALUE );
|
|
create table column_s_to_be_dropped.t0_columns(id int primary key nonclustered, c int, name char(20));
|
|
create table column_s_to_be_dropped.t1_columns(id int primary key nonclustered, c int, name char(20)) PARTITION BY RANGE(id) ( PARTITION p0 VALUES LESS THAN (0), PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN MAXVALUE );
|
|
|
|
create index k1 on column_s_to_be_dropped.t0_column (name);
|
|
create index k2 on column_s_to_be_dropped.t0_column (c);
|
|
create index k1 on column_s_to_be_dropped.t1_column (name);
|
|
create index k2 on column_s_to_be_dropped.t1_column (c);
|
|
create index k3 on column_s_to_be_dropped.t1_column (id, c);
|
|
|
|
create index k1 on column_s_to_be_dropped.t0_columns (name);
|
|
create index k2 on column_s_to_be_dropped.t0_columns (c);
|
|
create index k1 on column_s_to_be_dropped.t1_columns (name);
|
|
create index k2 on column_s_to_be_dropped.t1_columns (c);
|
|
-- create index k3 on column_s_to_be_dropped.t1_columns (id, c);
|
|
|
|
insert into column_s_to_be_dropped.t0_column values (1, 2, "123"), (2, 3, "123");
|
|
insert into column_s_to_be_dropped.t1_column values (1, 2, "123"), (2, 3, "123");
|
|
insert into column_s_to_be_dropped.t0_columns values (1, 2, "123"), (2, 3, "123");
|
|
insert into column_s_to_be_dropped.t1_columns values (1, 2, "123"), (2, 3, "123");
|
|
-- 7. Modify Table Column
|
|
create database column_to_be_modified;
|
|
create table column_to_be_modified.t0(id int primary key nonclustered, c int, name char(20));
|
|
create table column_to_be_modified.t1(id int primary key nonclustered, c int, name char(20)) PARTITION BY RANGE(id) ( PARTITION p0 VALUES LESS THAN (0), PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN MAXVALUE );
|
|
|
|
create index k1 on column_to_be_modified.t0 (name);
|
|
create index k2 on column_to_be_modified.t0 (c);
|
|
create index k1 on column_to_be_modified.t1 (name);
|
|
create index k2 on column_to_be_modified.t1 (c);
|
|
create index k3 on column_to_be_modified.t1 (id, c);
|
|
|
|
insert into column_to_be_modified.t0 values (1, 2, "123"), (2, 3, "123");
|
|
insert into column_to_be_modified.t1 values (1, 2, "123"), (2, 3, "123");
|