1建表和索引
create table index_test (id int identity(1,1),name sysname);
create clustered index nonidex_id on index_test(id);
2 插入测试数据
declare @count int=1;
while @count<=100000
begin
insert into index_test(name)
select replace(left(cast(NEWID() as varchar(50)),10) + right(cast(NEWID() as varchar(50)),10),'-','')
set @count=@count+1
end
3 观察更改前的表空间大小 data是 5920 KB
go
sp_spaceused index_test
name rows reserved data index_size unused
index_test 100000 5920 KB 5648 KB 48 KB 224 KB
4 更改列属性
alter table index_test
alter column name varchar(50);
5 观察更改后 表空间大小 data 是 11480 KB 说明列偏移量并不是在原来基础上面增加减少的。而是从最后面开始增加。
sp_spaceused index_test
name rows reserved data index_size unused
index_test 100000 11480 KB 11280 KB 64 KB 136 KB
6 重建索引
alter index nonidex_id on index_test rebuild with(online=on);
7 重建索引后表空间大小 data 为 3800KB
sp_spaceused index_test
name rows reserved data index_size unused
index_test 100000 3800 KB 3760 KB 16 KB 24 KB
8 线上库严禁频繁的字段属性更改