SQL SERER 2008性能调优.doc

第 PAGE 86 页 共 NUMPAGES 86 页 性能调优 百胜表规范 USE ERP_DW GO /* 功能说明： 创建【区域】维度表 修改说明： Create by LY on 2011-09-07 */ IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE id = OBJECT_ID(Dim_Area) AND type = U) BEGIN DROP TABLE Dim_Area END GO CREATE TABLE [dbo].[Dim_Area] ( AreaCode VARCHAR(20) NOT NULL, AreaName VARCHAR(50) NULL, CONSTRAINT PK_DIM_AREA PRIMARY key (AreaCode) ); GO /* 功能说明： 获取区域表有效的信息 修改说明： Create by LY on 2011-09-07 */ IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE id = OBJECT_ID(VW_Dim_Area) AND type = V) BEGIN DROP VIEW VW_Dim_Area END GO CREATE VIEW VW_Dim_Area AS SELECT AreaCode FROM Dim_Area LEFT JOIN SYSOBJECTS ON 1=1 LEFT JOIN SYSCOLUMNS ON 1=1 WHERE 1=1 GROUP BY AreaCode GO /* 功能说明：抽取业务库的数据到数据仓库 修改说明： Create by LY on 2011-09-07 Modify by LY on 2011-09-07 增加变量的注释 */ IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE id = OBJECT_ID(P_GetData_Load_Dim_Area) AND OBJECTPROPERTY(ID, NIsProcedure) = 1) BEGIN DROP PROCEDURE [dbo].[P_GetData_Load_Dim_Area] END GO SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[P_GetData_Load_Dim_Area] @P_sourceDB_name NVARCHAR(50) 源数据库名称，数据从哪抽取 AS BEGIN BEGIN TRAN 开始事务 DECLARE @TrunSql VARCHAR(50); 清空数据，不记录日志 DECLARE @InsertSql VARCHAR(MAX); 插入数据 SET @TrunSql= TRUNCATE Table Dim_Area ; EXEC (@TrunSql) IF @@error0 BEGIN ROLLBACK TRAN RETURN -1 END SET @InsertSql= INSERT INTO Dim_Area SELECT QUYU.QYDM as AreaCode, QUYU.QYMC as AreaName FROM ERP_Business..QUYU ; EXEC(@InsertSql) IF @@ERROR0 BEGIN ROLLBACK TRAN RETURN -1 END COMMIT TRAN END; GO /* 功能说明：抽取业务库的数据到数据仓库 修改说明： Create by