Works with sql Server 2008测试的(可选)要求之一是在所有表和索引上启用行级压缩.我们有一个现有的数据库,已经创建了很多表和索引.是否有一种简单的方法可以在所有这些表和索引上启用压缩？
这是我最终根据splattne的推荐制作的剧本.
select 'ALTER TABLE [' + name + '] REBUILD WITH (DATA_COMPRESSION = ROW);'
from sysobjects where type = 'U' -- all user tables
UNION
select 'ALTER INDEX [' + k.name + '] ON [' + t.name + '] REBUILD WITH (DATA_COMPRESSION = ROW);'
from sysobjects k
join sysobjects t on k.parent_obj = t.id
where k.type = 'K' -- all keys
AND t.type = 'U' -- all user tables
解决方法
我刚刚使用Works With sql Server工具在使用a_hardin-splattne脚本进行压缩后进行测试.测试失败,因为几个索引未压缩.
“sysobjects”视图包含一些但不是所有索引.我们需要“sysindexes”.感谢aspfaq.com这个索引洞察的匿名海报.我们还想忽略用户定义的函数.
SELECT 'ALTER TABLE [' + name + '] REBUILD WITH (DATA_COMPRESSION = ROW);' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
FROM sysobjects WHERE type = 'U' -- all user tables
UNION
SELECT 'ALTER INDEX [' + i.name + '] ON [' + OBJECT_NAME(i.id) + '] REBUILD WITH (DATA_COMPRESSION = ROW);' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
FROM
sysindexes i
inner join sysobjects o on o.name = OBJECT_NAME(i.id)
WHERE
(i.indid BETWEEN 1 AND 254)
AND (i.Status & 64)=0
AND OBJECTPROPERTY(i.id,'IsMsShipped') = 0
AND NOT o.type in ('TF','FN')
