数据表的物理优化方案(2)

--------------------------------- -- 对指定的表进行物理优化. --------------------------------- IF EXISTS(SELECT [NAME] FROM SYSOBJECTS WHERE [NAME]='dt_optimize_table') BEGIN DROP PROC dt_optimize_table END GO CREATE PROC dt_optimize_table ( @NVR_DBNAME NVARCHAR(64),  --数据库名. @NVR_DBPATH NVARCHAR(256), --数据库所在路径. @NVR_TABLENAME NVARCHAR(256), --表名. @NVR_OLEGROUPNAME NVARCHAR(256), --上一个文件组的名称. @NVR_NEWGOUPNAME NVARCHAR(256) OUTPUT --新的组文件名. )  WITH ENCRYPTION  AS BEGIN DECLARE @NVR_NEWID NVARCHAR(16)  --新的表文件编号. DECLARE @NVR_CMD NVARCHAR(4000)--命令 DECLARE @NVR_TMPTABLENAME NVARCHAR(256) --暂时表名 DECLARE @INT_TRANSACTION INT SET @NVR_NEWID= LEFT(REPLACE(NEWID(),'-',''),16) SET @NVR_TMPTABLENAME=@NVR_TABLENAME + '_' + @NVR_NEWID SET @NVR_NEWGOUPNAME=@NVR_TMPTABLENAME --添加一个文件组. SET @NVR_CMD='ALTER DATABASE [' + @NVR_DBNAME + '] ADD FILEGROUP [' + @NVR_TMPTABLENAME + ']' EXECUTE(@NVR_CMD) --向文件组中添加一个文件 SET @NVR_CMD=' ALTER DATABASE [' + @NVR_DBNAME + '] ADD FILE( NAME = N''' + @NVR_TMPTABLENAME + ''',  FILENAME = N''' + @NVR_DBPATH + '/' + @NVR_TMPTABLENAME + '.NDF'' ,  SIZE = 3,  FILEGROWTH = 10%)  TO FILEGROUP [' +@NVR_TMPTABLENAME + ']' EXECUTE(@NVR_CMD) -------------------------------------------- --事务段 -------------------------------------------- SET @INT_TRANSACTION=1 BEGIN TRANSACTION -------------------------------------------- --在该文件中添加表. **这里要手工修改**. -------------------------------------------- CREATE TABLE [BL_DATA_2003] ( [BIG_DATAAUTOID] [int] NOT NULL , [BIG_AREAAUTOID] [int] NOT NULL , [BIG_EnterTypeAutoID] [int] NOT NULL , [VAR_DATE] [varchar] (9) COLLATE Chinese_PRC_CI_AS NOT NULL , [FLO_VALUE] [float] NULL , CONSTRAINT [PK_DATA_2003] PRIMARY KEY  CLUSTERED  ( [BIG_DATAAUTOID], [BIG_AREAAUTOID], [BIG_EnterTypeAutoID], [VAR_DATE] )  ON [MC_INDUSTRY_2003]  ) ON [MC_INDUSTRY_2003] SET @NVR_CMD=' CREATE TABLE [' + @NVR_TMPTABLENAME + '] ( [BIG_DATAAUTOID] [int] NOT NULL , [BIG_AREAAUTOID] [int] NOT NULL , [BIG_EnterTypeAutoID] [int] NOT NULL , [VAR_DATE] [varchar] (9) COLLATE Chinese_PRC_CI_AS NOT NULL , [FLO_VALUE] [float] NULL , CONSTRAINT [PK_' + @NVR_TMPTABLENAME + '] PRIMARY KEY  ( [BIG_DATAAUTOID], [BIG_AREAAUTOID], [BIG_EnterTypeAutoID], [VAR_DATE] )  ON [' + @NVR_TMPTABLENAME + ']  ) ON [' +@NVR_TMPTABLENAME + ']' EXECUTE(@NVR_CMD) IF @@ERROR <>0 BEGIN SET @INT_TRANSACTION=-1 GOTO RollTRANSACTION END --  --在表上建立一个日期的索引.如果有索引的话.在此添加. --  SET @NVR_CMD='CREATE INDEX IX_' + @NVR_TMPTABLENAME + '_DATE ON ' + @NVR_TMPTABLENAME + ' (BIG_DATE)' --  EXECUTE(@NVR_CMD) --将数据移到新的表. SET @NVR_CMD='INSERT INTO ' + @NVR_TMPTABLENAME + ' SELECT * FROM ' + @NVR_TABLENAME  --在此视你实质情况而定,可加入主键一至的 ORDER BY  EXECUTE(@NVR_CMD) IF @@ERROR <>0 BEGIN SET @INT_TRANSACTION=-1 GOTO RollTRANSACTION END --删除原表. SET @NVR_CMD='DROP TABLE ' + @NVR_TABLENAME EXECUTE(@NVR_CMD) IF @@ERROR <>0 BEGIN SET @INT_TRANSACTION=-1 GOTO RollTRANSACTION END --提交事务. COMMIT TRANSACTION SET @INT_TRANSACTION=0 -------------------------------------------- --事务段 -------------------------------------------- --将表名改为原来表名. SET @NVR_CMD='exec sp_rename ''' + @NVR_TMPTABLENAME + ''', ''' + @NVR_TABLENAME + '''' EXECUTE(@NVR_CMD) --回滚事务. RollTRANSACTION: IF @INT_TRANSACTION=-1 BEGIN ROLLBACK TRANSACTION END ELSE BEGIN --删除原来的表文件. SET @NVR_CMD=' ALTER DATABASE [' + @NVR_DBNAME + ']  REMOVE FILE ' + @NVR_OLEGROUPNAME  EXECUTE(@NVR_CMD) END -------------------------------------------- --清理日志 -------------------------------------------- SET @NVR_CMD='DUMP TRANSACTION ' + @NVR_DBNAME + ' WITH NO_LOG' EXECUTE(@NVR_CMD) SET @NVR_CMD='DBCC SHRINKFILE(2, 0)' EXECUTE(@NVR_CMD) END