資源描述:
《sqlserver2008性能調(diào)優(yōu)》由會(huì)員上傳分享,免費(fèi)在線閱讀,更多相關(guān)內(nèi)容在工程資料-天天文庫。
1、專業(yè)資料分享性能調(diào)優(yōu)1百勝表規(guī)范USEERP_DWGO/*功能說明:創(chuàng)建【區(qū)域】維度表修改說明:CreatebyLYon2011-09-07*/IFEXISTS(SELECT1FROMSYSOBJECTSWHEREid=OBJECT_ID('Dim_Area')ANDtype='U')BEGINDROPTABLEDim_AreaENDGOCREATETABLE[dbo].[Dim_Area](AreaCodeVARCHAR(20)NOTNULL,AreaNameVARCHAR(50)NULL,CONSTRAI
2、NTPK_DIM_AREAPRIMARYkey(AreaCode));GO/*功能說明:獲取區(qū)域表有效的信息修改說明:CreatebyLYon2011-09-07*/IFEXISTS(SELECT1FROMSYSOBJECTSWHEREid=OBJECT_ID('VW_Dim_Area')ANDtype='V')BEGINDROPVIEWVW_Dim_AreaENDGOCREATEVIEWVW_Dim_AreaASSELECTAreaCodeFROMDim_AreaLEFTJOINSYSOBJECTSON1=
3、1LEFTJOINSYSCOLUMNSON1=1WHERE1=1GROUPBYAreaCodeGO完美DOC格式整理專業(yè)資料分享/*功能說明:抽取業(yè)務(wù)庫的數(shù)據(jù)到數(shù)據(jù)倉庫修改說明:CreatebyLYon2011-09-07ModifybyLYon2011-09-07增加變量的注釋*/IFEXISTS(SELECT1FROMSYSOBJECTSWHEREid=OBJECT_ID('P_GetData_Load_Dim_Area')ANDOBJECTPROPERTY(ID,N'IsProcedure')=1)BE
4、GINDROPPROCEDURE[dbo].[P_GetData_Load_Dim_Area]ENDGOSETANSI_NULLSONSETQUOTED_IDENTIFIERONGOCREATEPROCEDURE[dbo].[P_GetData_Load_Dim_Area]@P_sourceDB_nameNVARCHAR(50)----源數(shù)據(jù)庫名稱,數(shù)據(jù)從哪抽取ASBEGINBEGINTRAN---開始事務(wù)DECLARE@TrunSqlVARCHAR(50);----清空數(shù)據(jù),不記錄日志DECLARE@Ins
5、ertSqlVARCHAR(MAX);----插入數(shù)據(jù)SET@TrunSql='TRUNCATETableDim_Area';EXEC(@TrunSql)IF@@error<>0BEGINROLLBACKTRANRETURN-1ENDSET@InsertSql='INSERTINTODim_AreaSELECTQUYU.QYDMasAreaCode,QUYU.QYMCasAreaNameFROMERP_Business..QUYU';EXEC(@InsertSql)IF@@ERROR<>0BEGINROLLB
6、ACKTRANRETURN-1ENDCOMMITTRANEND;完美DOC格式整理專業(yè)資料分享GO/*功能說明:抽取業(yè)務(wù)庫的數(shù)據(jù)到數(shù)據(jù)倉庫修改說明:CreatebyLYon2011-09-07ModifybyLYon2011-09-07增加變量的注釋*/IFOBJECT_ID('[dbo].[P_GetData_Load_Dim_Area]','P')ISNOTNULLDROPPROC[dbo].[P_GetData_Load_Dim_Area]GOSETANSI_NULLSONSETQUOTED_IDENT
7、IFIERONGOCREATEPROCEDURE[dbo].[P_GetData_Load_Dim_Area]@P_sourceDB_nameNVARCHAR(50)----源數(shù)據(jù)庫名稱,數(shù)據(jù)從哪抽取ASBEGINBEGINTRAN---開始事務(wù)DECLARE@TrunSqlVARCHAR(50);----清空數(shù)據(jù),不記錄日志DECLARE@InsertSqlVARCHAR(MAX);----插入數(shù)據(jù)SET@TrunSql='TRUNCATETableDim_Area';EXEC(@TrunSql)IF@@e
8、rror<>0BEGINROLLBACKTRANRETURN-1ENDSET@InsertSql='INSERTINTODim_AreaSELECTQUYU.QYDMasAreaCode,QUYU.QYMCasAreaNameFROMERP_Business..QUYU';EXEC(@InsertSql)IF@@ERROR<>0BEGINROLLBACKTRANRETURN-1ENDCOMMITTR