SQL 2008企业版数据库 转 标准版方法


1 查询和修改压缩表(行压缩 页压缩)功能


SELECT DISTINCT
SCHEMA_NAME(o.schema_id)  + '.' + OBJECT_NAME(o.object_id) AS TableName,
i.name AS IndexName,
p.data_compression_desc AS CompressionType,
i.type_desc AS StorageType
FROM sys.partitions  p  with(nolock)
INNER JOIN sys.objects o with(nolock)
ON p.object_id = o.object_id 
JOIN sys.indexes i 
ON p.object_id = i.object_id
AND i.index_id = p.index_id
WHERE p.data_compression > 0 
AND SCHEMA_NAME(o.schema_id) <> 'SYS' 


如下图所示:
(返回的查询条件中,有显示此表的压缩类型)
查询系统存在的压缩表


修改表压缩的方式

方式一:

在压缩表上点击”右键” —>”管理压缩”–>压缩类型选择”NONE”,点击”立即运行”。

方式二:
ALTER TABLE [表名] REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = NONE)


2 查询和修改表分区


查询使用表分区的表

SELECT t.object_id AS Object_ID, t.name AS TableName, ic.column_id
 as PartitioningColumnID, c.name AS PartitioningColumnName 
  FROM sys.tables AS t
  JOIN sys.indexes AS i
      ON t.object_id = i.object_id
  JOIN sys.columns AS c
      ON t.object_id = c.object_id
  JOIN sys.partition_schemes AS ps
      ON ps.data_space_id = i.data_space_id
  JOIN sys.index_columns AS ic
      ON ic.object_id = i.object_id AND ic.index_id = i.index_id 
      AND ic.partition_ordinal > 0

分区表修正方法

2.1 修改原分区表表名(A)-变更为(B)

EXEC sp_rename ‘A’, ‘B’

2.2 新建一张新表(A)

CREATE TABLE [dbo].[A](
[KeyId] [bigint] IDENTITY(1,1) NOT NULL,
[TmpId] [varchar](5) NOT NULL,
[tmpName] [varchar](60) NOT NULL,
[qty] [int] NOT NULL
)
GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[A] ADD CONSTRAINT [DF_AChange_innRtnQty] DEFAULT ((0)) FOR [qty]
GO

2.3 将数据表(B)中的数据插入至表(A)中
SET IDENTITY_Insert A ON

insert into A(keyId,tmpId,tmpName,qty)
select keyId,tmpId,tmpName,qty from B

SET IDENTITY_Insert A OFF

2.4 删除数据表(B)

truncate table [B]
drop table [B]



注意标识列
默认值是否正常
注意重新建表时 以前的索引名称 默认值约束名称需要变更

插入数据时

SET IDENTITY_Insert [表名] ON
insert into [表名]([列名1],[列名2],[列名3])
select [列名1],[列名2],[列名3] from [源表名]
SET IDENTITY_Insert [表名] OFF



3 查询 并 删除分区函数 分区方案



3.1 查看系统中的分区方案
select * from sys.partition_schemes

删除分区方案名称
3.2 DROP PARTITION SCHEME 分区方案名称;


3.3 查看系统中存在的分区函数
 select * from sys.partition_functions

 


3.4 删除分区函数
DROP PARTITION FUNCTION 分区函数名称;

相关阅读:
mssql 标准版 企业版区别