欢迎来到天天文库
浏览记录
ID:22517320
大小:4.24 MB
页数:86页
时间:2018-10-29
《sqlserver2008性能调优》由会员上传分享,免费在线阅读,更多相关内容在工程资料-天天文库。
1、WORD文档下载可编辑性能调优1百胜表规范USEERP_DWGO/*功能说明:创建【区域】维度表修改说明:CreatebyLYon2011-09-07*/IFEXISTS(SELECT1FROMSYSOBJECTSWHEREid=OBJECT_ID('Dim_Area')ANDtype='U')BEGINDROPTABLEDim_AreaENDGOCREATETABLE[dbo].[Dim_Area](AreaCodeVARCHAR(20)NOTNULL,AreaNameVARCHAR(50)NULL,CONSTRAINTPK_DIM_ARE
2、APRIMARYkey(AreaCode));GO/*功能说明:获取区域表有效的信息修改说明:CreatebyLYon2011-09-07*/IFEXISTS(SELECT1FROMSYSOBJECTSWHEREid=OBJECT_ID('VW_Dim_Area')ANDtype='V')BEGINDROPVIEWVW_Dim_AreaENDGOCREATEVIEWVW_Dim_AreaASSELECTAreaCodeFROMDim_AreaLEFTJOINSYSOBJECTSON1=1LEFTJOINSYSCOLUMNSON1=1WHERE
3、1=1GROUPBYAreaCodeGO专业技术资料整理WORD文档下载可编辑/*功能说明:抽取业务库的数据到数据仓库修改说明:CreatebyLYon2011-09-07ModifybyLYon2011-09-07增加变量的注释*/IFEXISTS(SELECT1FROMSYSOBJECTSWHEREid=OBJECT_ID('P_GetData_Load_Dim_Area')ANDOBJECTPROPERTY(ID,N'IsProcedure')=1)BEGINDROPPROCEDURE[dbo].[P_GetData_Load_Dim_
4、Area]ENDGOSETANSI_NULLSONSETQUOTED_IDENTIFIERONGOCREATEPROCEDURE[dbo].[P_GetData_Load_Dim_Area]@P_sourceDB_nameNVARCHAR(50)----源数据库名称,数据从哪抽取ASBEGINBEGINTRAN---开始事务DECLARE@TrunSqlVARCHAR(50);----清空数据,不记录日志DECLARE@InsertSqlVARCHAR(MAX);----插入数据SET@TrunSql='TRUNCATETableDim_Ar
5、ea';EXEC(@TrunSql)IF@@error<>0BEGINROLLBACKTRANRETURN-1ENDSET@InsertSql='INSERTINTODim_AreaSELECTQUYU.QYDMasAreaCode,QUYU.QYMCasAreaNameFROMERP_Business..QUYU';EXEC(@InsertSql)IF@@ERROR<>0BEGINROLLBACKTRANRETURN-1ENDCOMMITTRANEND;专业技术资料整理WORD文档下载可编辑GO/*功能说明:抽取业务库的数据到数据仓库修改说
6、明:CreatebyLYon2011-09-07ModifybyLYon2011-09-07增加变量的注释*/IFOBJECT_ID('[dbo].[P_GetData_Load_Dim_Area]','P')ISNOTNULLDROPPROC[dbo].[P_GetData_Load_Dim_Area]GOSETANSI_NULLSONSETQUOTED_IDENTIFIERONGOCREATEPROCEDURE[dbo].[P_GetData_Load_Dim_Area]@P_sourceDB_nameNVARCHAR(50)----源数
7、据库名称,数据从哪抽取ASBEGINBEGINTRAN---开始事务DECLARE@TrunSqlVARCHAR(50);----清空数据,不记录日志DECLARE@InsertSqlVARCHAR(MAX);----插入数据SET@TrunSql='TRUNCATETableDim_Area';EXEC(@TrunSql)IF@@error<>0BEGINROLLBACKTRANRETURN-1ENDSET@InsertSql='INSERTINTODim_AreaSELECTQUYU.QYDMasAreaCode,QUYU.QYMCasA
8、reaNameFROMERP_Business..QUYU';EXEC(@InsertSql)IF@@ERROR<>0BEGINROLLBACKTRANRETURN
此文档下载收益归作者所有