表变量上的 SQL SERVER 非聚集索引?

本文介绍了表变量上的 SQL SERVER 非聚集索引?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

如何在表变量上创建非聚集索引?

How can we create non-clustered index on table variable?

创建表@risk(rskid int)

在@risk(rskid) 上创建非聚集索引 r_rskid_nc

它不起作用??

我该如何优化它??

更改程序 [dbo].[SPR_LV004](@TopN INT,@LoggedUserId INT,@Entity VARCHAR(255),@OpModel VARCHAR(255),@RiskCat VARCHAR(255),@RsdlInh VARCHAR(1),@DisplayAction VARCHAR(1),@LastAssDate 日期时间)作为

ALTER PROCEDURE [dbo].[SPR_LV004] ( @TopN INT ,@LoggedUserId INT ,@Entity VARCHAR(255) ,@OpModel VARCHAR(255) ,@RiskCat VARCHAR(255) ,@RsdlInh VARCHAR(1) ,@DisplayAction VARCHAR(1) ,@LastAssDate DATETIME ) AS

SET NOCOUNT ON DECLARE @Thisdate DATETIME SET @ThisDate = GETDATE() DECLARE @MainFilter TABLE( fcaName VARCHAR(100) ,fctName VARCHAR(100) ,rskId INT PRIMARY KEY CLUSTERED ) DECLARE @Tgt TABLE(rasRiskId INT ,rasId INT ,RAMName VARCHAR(100) ,RAMColour INT ,RAMImpDesc VARCHAR(100) ,RAMLikDesc VARCHAR(100) ,RAMImpScore INT ,RAMLikScore INT ,LastScore INT ,AnnualExposure NUMERIC(15, 0) ,Currency VARCHAR(9) ,OverallExp NUMERIC(15,0) ,Frequency INT ,LastAssessmentDate DATETIME) DECLARE @Inh TABLE(rasRiskId INT ,rasId INT ,RAMName VARCHAR(100) ,RAMColour INT ,RAMImpDesc VARCHAR(100) ,RAMLikDesc VARCHAR(100) ,RAMImpScore INT ,RAMLikScore INT ,LastScore INT ,AnnualExposure NUMERIC(15, 0) ,Currency VARCHAR(9) ,OverallExp NUMERIC(15,0) ,Frequency INT ,LastAssessmentDate DATETIME) DECLARE @Res TABLE(rasRiskId INT ,rasId INT ,RAMName VARCHAR(100) ,RAMColour INT ,RAMimpDesc VARCHAR(100) ,RAMlikDesc VARCHAR(100) ,RAMImpScore INT ,RAMLikScore INT ,LastScore INT ,AnnualExposure NUMERIC(15, 0) ,Currency VARCHAR(9) ,OverallExp NUMERIC(15,0) ,Frequency INT ,LastAssessmentDate DATETIME) DECLARE @RiskData TABLE(RAMScore INT ,AnnExp NUMERIC(15, 0) ,rskId INT ,RiskID VARCHAR(20) ,rskDescription VARCHAR(150) ,LongDesc VARCHAR(4000) ,RiskAssessmentDate VARCHAR(100) ,RAMName VARCHAR(100) ,rskRAMId INT ,rskRiskProfile INT ,EntityInfo VARCHAR(100) ,OwnerName VARCHAR(100) ,NomineeName VARCHAR(100) ,ReviewerName VARCHAR(100) ,TgtRasId INT ,TgtRamName VARCHAR(100) ,TgtRamColour INT ,TgtRamImpDesc VARCHAR(100) ,TgtRamLikDesc VARCHAR(100) ,TgtRamImpScore INT ,TgtRamLikScore INT ,TgtLastScore INT ,TgtAnnualExp Numeric(15,0) ,TgtCurrency VARCHAR(9) ,InhRasId INT ,InhRamName VARCHAR(100) ,InhRamColour INT ,InhRamImpDesc VARCHAR(100) ,InhRamLikDesc VARCHAR(100) ,InhRamImpScore INT ,InhRamLikScore INT ,InhLastScore INT ,InhAnnualExp Numeric(15,0) ,InhCurrency VARCHAR(9) ,RsdRasId INT ,RsdRamName VARCHAR(100) ,RsdRamColour INT ,RsdRamImpDesc VARCHAR(100) ,RsdRamLikDesc VARCHAR(100) ,RsdRamImpScore INT ,RsdRamLikScore INT ,RsdLastScore INT ,rsdAnnualExp Numeric(15,0) ,RsdCurrency VARCHAR(9) ,fcaName VARCHAR(100) ,fctName VARCHAR(100) ,CCRId VARCHAR(15) ,crrDescription VARCHAR(150) ,Assessed char(1) ,Attested Char(1) ,DisplayAction VARCHAR(1) ,opModelName VARCHAR(255) ,severity NUMERIC(15,0) ,topriskcat VARCHAR(100) ,TgtOverallExp NUMERIC(15,0) ,TgtFrequency INT ,InhOverallExp NUMERIC(15,0) ,InhFrequency INT ,ResOverallExp NUMERIC(15,0) ,ResFrequency INT) DECLARE @RiskCtrl TABLE( rcnRiskId INT ,rcnId INT ,ControlInfo VARCHAR(1100) ,Performance INT --VARCHAR(60) ,Design INT --VARCHAR(60) ,ControlOwner VARCHAR(100) ,ControlNominee VARCHAR(100) ,ControlReviewer VARCHAR(100)) DECLARE @ACTIONS TABLE( ActionRiskId INT ,ControlID INT ,actTgtCompleteDate VARCHAR(9) ,actTgtODueDate VARCHAR(9) ,RATgtDate VARCHAR(9) ,ActNominee VARCHAR(100) ,ActOwner VARCHAR(100) ,ActCompleteAmt INT ,ActionDetail VARCHAR(MAX) ,AType INT ,ActionState VARCHAR(90) ) DECLARE @TopRiskSort TABLE( RdRecId INT IDENTITY(1,1) NOT NULL ,AnnExp NUMERIC(15, 0) ,rskId INT ,severity NUMERIC(15,0) ) DECLARE @TopRisk TABLE( AnnExp NUMERIC(15, 0) ,rskId INT ,severity NUMERIC(15,0) ) -- New tables for LV004 06-JAN-2010 -- gets the parent Operational Model -- by priyanka DECLARE @OpModels TABLE( OpModelName VARCHAR(255), rskId INT) DECLARE @TopRiskCat TABLE( rskid INT PRIMARY KEY CLUSTERED, topRiskCat VARCHAR(1000)) --LOCAL VARIABLES DECLARE @Query VARCHAR(8000) --Create filter data in a Table datatype INSERT INTO @MainFilter SELECT DISTINCT NULL as fcaName ,NULL as fctName ,R.rskId FROM RiskProfiles RP INNER JOIN RiskProfileFactAnal RPF ON RP.rwkReference = RPF.wfaRiskProfile INNER JOIN FactCatAnal FA ON RPF.wfaFactAnalId = FA.fcaId INNER JOIN UserViewsEntity UE ON RPF.wfaRiskProfile = UE.uveEntId INNER JOIN Risks R ON RP.rwkReference = R.rskRiskProfile INNER JOIN RiskAnalysis RA ON R.rskId = RA.ranRiskId INNER JOIN RiskCategory RC ON RA.ranRiskAnalId = RC.rctId LEFT OUTER JOIN RiskAssessment RASS ON R.rskId = RASS.rasRiskId AND R.rskRelevant = 'Y' WHERE (UE.uveUserId = @LoggedUserId) --AND FA.fcaId in (SELECT FinalID FROM @FinalTree) AND FA.fcaId in (SELECT TreeTableID FROM [dbo].[OpModelListToTable](@OpModel)) AND RP.rwkReference in (SELECT TreeTableID FROM [dbo].[EntityListToTable](@Entity)) AND RC.rctid in (SELECT TreeTableID FROM [dbo].[RiskCategoryListToTable](@RiskCat)) AND R.rskOpendate <= @LastAssDate -- Insertion into new tables INSERT INTO @OpModels SELECT FCA.fcaName AS TopLevelBusinessLine, Rsk.rskid FROM @MainFilter MF INNER JOIN Risks RSK ON RSK.rskid= MF.rskId INNER JOIN RiskProfileFactAnal RPF ON RPF.wfaRISkProfile = RSK.rskRiskProfile INNER JOIN FactCatAnal FCA ON FCA.fcaId = RPF.wfaFactAnalId INNER JOIN FactCategory FC ON FC.fctid = FCA.fcaCategory and FC.fctName='Business Unit' INSERT INTO @TopRiskCat SELECT R.rskid ,dbo.fn_GetTopParentRiskCategoryScalar(RC.rctId) as TopLevelRiskCat FROM @MainFilter MF INNER JOIN RISKS R ON R.rskid= MF.rskId INNER JOIN RiskAnalysis RA ON R.rskId = RA.ranRiskId INNER JOIN RiskCategory as RC ON RA.ranRiskAnalId = RC.rctId -- Target Data INSERT INTO @Tgt SELECT TgtRskID,rasId,ramaltName,ramaltColour,TgtRAMDescr,TgtRAMLikDescr,TgtRAMImpScr,TgtRAMLikScr ,LastScore , AnnualExposure,curCode, TgtOverallExp,TgtFrequency,RiskLastAssessment FROM ( SELECT RA.rasRiskId AS TgtRskID ,RA.rasId ,RM.ramaltName ,RM.ramaltColour ,isnull(RI.ramimpDescription,'') AS TgtRAMDescr ,isnull(RL.ramlikDescription,'') AS TgtRAMLikDescr ,isnull(RA.rasRAMImpactScore,0) AS TgtRAMImpScr ,isnull(RA.rasRAMLikelihoodScore,0) AS TgtRAMLikScr ,isnull(dbo.RiskAssessmentLastScore(RA.rasRiskId, 'I'),0) AS LastScore ,ISNULL(RA.rasExposureHO * (RA.rasRAMLikFreqEntered / 100), 0) / 1000 AS AnnualExposure ,RCU.curCode ,RA.rasExposure as TgtOverallExp ,RA.rasRAMLikFreqEntered as TgtFrequency ,RA.rasAssessmentDate as RiskLastAssessment FROM RAMImpacts RI INNER JOIN RiskAssessment RA ON RI.ramimpScore = RA.rasRAMImpactScore AND RI.ramimpRAMId = RA.rasRAMId INNER JOIN Risks R ON R.rskId = RA.rasRiskId INNER JOIN @MainFilter MF ON MF.rskId = R.rskId INNER JOIN RAMALTs RM ON RA.rasRAMId = RM.ramaltRAMId AND RA.rasRAMALTAmount = RM.ramaltAmount INNER JOIN RAMLikelihoods RL ON RA.rasRAMLikelihoodScore = RL.ramlikScore AND RA.rasRAMId = RL.ramlikRAMId LEFT OUTER JOIN RAM RMS ON RA.rasRAMId = RMS.ramId LEFT OUTER JOIN Currencies RCU ON RMS.ramCurrencyId = RCU.curId WHERE (RA.rasType = 'T') ) A --WHERE A.RiskLastAssessment IN INNER JOIN (SELECT MAX(RAouter.rasAssessmentDate)as rasAssessmentDate,RAouter.rasRiskid FROM RiskAssessment RAouter --WHERE RAouter.rasAssessmentDate <= @LastAssDate Group by RAouter.rasRiskid) B ON A.RiskLastAssessment = B.rasAssessmentDate and A.TgtRskID = B.rasRiskid --Inherent data INSERT INTO @Inh SELECT InhRskID,rasId,ramaltName,ramaltColour,InhRAMDescr,InhRAMLikDescr,InhRAMImpScr,InhRAMLikScr,LastScore,AnnualExposure,curCode, InhOverallExp,InhFrequency,RiskLastAssessment FROM ( SELECT RA.rasRiskId AS InhRskID ,RA.rasId ,RM.ramaltName ,RM.ramaltColour ,isnull(RI.ramimpDescription,'') AS InhRAMDescr ,isnull(RL.ramlikDescription,'') AS InhRAMLikDescr ,isnull(RA.rasRAMImpactScore,0) AS InhRAMImpScr ,isnull(RA.rasRAMLikelihoodScore,0) AS InhRAMLikScr ,isnull(dbo.RiskAssessmentLastScore(RA.rasRiskId, 'I'),0) AS LastScore ,ISNULL(RA.rasExposureHO * (RA.rasRAMLikFreqEntered / 100), 0) / 1000 AS AnnualExposure ,RCU.curCode ,RA.rasExposure as InhOverallExp ,RA.rasRAMLikFreqEntered as InhFrequency ,RA.rasAssessmentDate as RiskLastAssessment FROM RAMImpacts RI INNER JOIN RiskAssessment RA ON RI.ramimpScore = RA.rasRAMImpactScore AND RI.ramimpRAMId = RA.rasRAMId INNER JOIN Risks R ON R.rskId = RA.rasRiskId INNER JOIN @MainFilter MF ON MF.rskId = R.rskId INNER JOIN RAMALTs RM ON RA.rasRAMId = RM.ramaltRAMId AND RA.rasRAMALTAmount = RM.ramaltAmount INNER JOIN RAMLikelihoods RL ON RA.rasRAMLikelihoodScore = RL.ramlikScore AND RA.rasRAMId = RL.ramlikRAMId LEFT OUTER JOIN RAM RMS ON RA.rasRAMId = RMS.ramId LEFT OUTER JOIN Currencies RCU ON RMS.ramCurrencyId = RCU.curId WHERE (RA.rasType = 'I'))-- AND (RA.rasIsLatest = 'Y') A --WHERE A.RiskLastAssessment IN INNER JOIN (SELECT MAX(RAouter.rasAssessmentDate)as rasAssessmentDate,RAouter.rasRiskid FROM RiskAssessment RAouter --WHERE RAouter.rasAssessmentDate <= @LastAssDate Group by RAouter.rasRiskid) B ON A.RiskLastAssessment = B.rasAssessmentDate and A.InhRskID = B.rasRiskid --Residual data INSERT INTO @Res SELECT ResRskID,rasId,ramaltName,ramaltColour,ResRAMDescr,ResRAMLikDescr,ResRAMImpScr,ResRAMLikScr,LastScore,AnnualExposure,curCode, ResOverallExp,ResFrequency,RiskLastAssessment FROM( SELECT RA.rasRiskId AS ResRskID ,RA.rasId ,RM.ramaltName ,RM.ramaltColour ,isnull(RI.ramimpDescription,'') AS ResRAMDescr ,isnull(RL.ramlikDescription,'') AS ResRAMLikDescr ,isnull(RA.rasRAMImpactScore,0) AS ResRAMImpScr ,isnull(RA.rasRAMLikelihoodScore,0) AS ResRAMLikScr ,isnull(dbo.RiskAssessmentLastScore(RA.rasRiskId, 'R'),0) AS LastScore ,ISNULL(RA.rasExposureHO * (RA.rasRAMLikFreqEntered / 100), 0) / 1000 AS AnnualExposure ,RCU.curCode ,RA.rasExposure as ResOverallExp ,RA.rasRAMLikFreqEntered as ResFrequency ,RA.rasAssessmentDate as RiskLastAssessment FROM RAMImpacts RI INNER JOIN RiskAssessment RA ON RI.ramimpScore = RA.rasRAMImpactScore AND RI.ramimpRAMId = RA.rasRAMId INNER JOIN Risks R ON R.rskId = RA.rasRiskId INNER JOIN @MainFilter MF ON MF.rskId = R.rskId INNER JOIN RAMALTs RM ON RA.rasRAMId = RM.ramaltRAMId AND RA.rasRAMALTAmount = RM.ramaltAmount INNER JOIN RAMLikelihoods RL ON RA.rasRAMLikelihoodScore = RL.ramlikScore AND RA.rasRAMId = RL.ramlikRAMId LEFT OUTER JOIN RAM RMS ON RA.rasRAMId = RMS.ramId LEFT OUTER JOIN Currencies RCU ON RMS.ramCurrencyId = RCU.curId WHERE (RA.rasType = 'R'))-- AND (RA.rasIsLatest = 'Y') A --WHERE A.RiskLastAssessment IN INNER JOIN (SELECT MAX(RAouter.rasAssessmentDate)as rasAssessmentDate,RAouter.rasRiskid FROM RiskAssessment RAouter --WHERE RAouter.rasAssessmentDate <= @LastAssDate Group by RAouter.rasRiskid) B ON A.RiskLastAssessment = B.rasAssessmentDate and A.ResRskID = B.rasRiskid -- Risk Data to display on report INSERT INTO @RiskData SELECT RA.rasRAMImpactScore * RA.rasRAMLikelihoodScore AS RAMScore ,ISNULL(RA.rasExposureHO * (RA.rasRAMLikFreqEntered / 100), 0) / 1000 AS AnnExp -- Required for sorting. ,RSK.rskId ,dbo.RiskPrefixId(RSK.rskId) AS RiskID ,isnull(RSK.rskDescription,'') ,isnull(SUBSTRING(RSK.rskLongDesc, 1, 4000),'') AS LongDesc ,CASE WHEN YEAR(RSK.rskAssNext) <= 1900 THEN NULL ELSE CONVERT(VARCHAR(9), RSK.rskAssNext, 6) END as RiskAssessmentDate ,RAM.RAMName ,RSK.rskRAMId ,RSK.rskRiskProfile ,RP.rwkDesc + ': ' + dbo.EntityPrefixId(RSK.rskRiskProfile) AS EntityInfo ,isnull(dbo.FULLNAME(RSK.rskOwner),'...') AS OwnerName ,isnull(dbo.FULLNAME(RSK.rskNomineeId),'...') AS NomineeName ,isnull(dbo.FULLNAME(RSK.rskReviewer),'...') AS ReviewerName ,T.rasId AS TgtRasId ,T.RAMName AS TgtRamName ,T.RAMColour AS TgtRamColour ,T.RAMImpDesc AS TgtRamImpDesc ,T.RAMLikDesc AS TgtRamLikDesc ,isnull(T.RAMImpScore,0) AS TgtRamImpScore ,isnull(T.RAMLikScore,0) AS TgtRamLikScore ,isnull(T.LastScore,0) AS TgtLastScore ,isnull(T.AnnualExposure,0.00) AS TgtAnnualExp ,T.Currency as TgtCurrency ,I.rasId AS InhRasId ,I.RAMName AS InhRamName ,I.RAMColour AS InhRamColour ,I.RAMImpDesc AS InhRamImpDesc ,I.RAMLikDesc AS InhRamLikDesc ,isnull(I.RAMImpScore,0) AS InhRamImpScore ,isnull(I.RAMLikScore,0) AS InhRamLikScore ,isnull(I.LastScore,0) AS InhLastScore ,isnull(I.AnnualExposure,0.00) AS InhAnnualExp ,I.Currency as InhCurrency ,R.rasId AS RsdRasId ,R.RAMName AS RsdRamName ,R.RAMColour AS RsdRamColour ,R.RAMimpDesc AS RsdRamImpDesc ,R.RAMlikDesc AS RsdRamLikDesc ,isnull(R.RAMImpScore,0) AS RsdRamImpScore ,isnull(R.RAMLikScore,0) AS RsdRamLikScore ,isnull(R.LastScore,0) AS RsdLastScore ,isnull(R.AnnualExposure,0.00) AS RsdAnnualExp ,R.Currency as RsdCurrency ,MF.fcaName ,MF.fctName ,dbo.CRRPrefixId(CRR.CrrId) AS CCRId ,isnull(CRR.crrDescription,'') ,CASE WHEN ISNULL(RA.rasid,0) = 0 THEN 'N' Else 'Y' END AS Assessed ,CASE WHEN UPPER((RA.rasstatus)) = 'A' THEN 'Y' ELSE 'N' END AS Attested ,@DisplayAction ,OP.OpModelName as TopLevelOpModel ,RSK.rskSeverity as RiskSeverity ,TRC.topRiskCat as TopLevelRiskCat ,Isnull(T.OverallExp/1000,0) as TgtOverallExp ,Isnull(T.Frequency,0) as TgtFrequency ,IsNUll(I.OverallExp/1000,0) as InhOverallExp ,ISNUll(I.Frequency,0) as InhFrequency ,IsNUll(R.OverallExp/1000,0) as ResOverallExp ,Isnull(R.Frequency,0) as ResFrequency FROM Risks RSK INNER JOIN @MainFilter MF ON MF.rskId = RSK.rskId INNER JOIN RiskProfiles RP ON RSK.rskRiskProfile = RP.rwkReference INNER JOIN RAM ON RP.rwkRAMID = RAM.ramId LEFT OUTER JOIN CentralRiskRegister CRR ON CRR.crrId = RSK.rskCRRId -- Added on 03/Jan/08 LEFT OUTER JOIN RiskAssessment RA ON RSK.rskId = RA.rasRiskId AND RA.rasIsLatest = 'Y' AND RA.rasType = CASE WHEN @RsdlInh = '1' THEN 'R' WHEN @RsdlInh = '2' THEN 'I' ELSE 'T' END LEFT OUTER JOIN @Tgt T ON RSK.rskId = T.rasRiskId LEFT OUTER JOIN @Inh I ON RSK.rskId = I.rasRiskId LEFT OUTER JOIN @Res R ON RSK.rskId = R.rasRiskId LEFT OUTER JOIN @OpModels OP ON RSK.rskId = OP.rskid LEFT OUTER JOIN @TopRiskCat TRC ON RSk.rskid = TRC.rskid WHERE RSK.rskRelevant = 'Y' -- Get Risk data in descending order of Severity INSERT INTO @TopRiskSort select distinct AnnExp,rskId,severity FROM @RiskData Order BY Severity desc; --Order BY AnnExp desc; --If @TopN values is 0 take all the recods IF @TopN = 0 BEGIN INSERT INTO @TopRisk select AnnExp ,rskId ,severity from @TopRiskSort END ELSE BEGIN -- Else INSERT INTO @TopRisk select AnnExp ,rskId ,severity from @TopRiskSort where RdRecId <= @topn; END --Controls for the above Risks INSERT INTO @RiskCtrl SELECT RD.rskId ,RC.rcnId ,dbo.RiskControlPrefixId(RC.rcnId) + ':' + rcnShortDescr AS ControlInfo ,(SELECT efid FROM Effectiveness WHERE efId = CA.rcaEffectiveness) AS Performance --ACCR-1327 ,(SELECT adid FROM Adequacy WHERE adId = CA.rcaAdequacy) AS Design ,dbo.FULLNAME(RC.rcnOwnerId) AS ControlOwner ,dbo.FULLNAME(RC.rcnNomineeId) AS ControlNominee ,dbo.FULLNAME(RC.rcnReviewerId) AS ControlReviewer FROM @TopRisk TR LEFT OUTER JOIN @RiskData RD ON RD.rskId=TR.rskId LEFT OUTER JOIN RiskControls RC ON RC.rcnRiskId = RD.rskId LEFT OUTER JOIN RiskCtrlAss CA ON RC.rcnId = CA.rcaRiskCtrlId WHERE (ISNULL(CA.rcaId, 0) = (SELECT IsNull(MAX(RA.rcaId), 0) FROM RiskCtrlAss RA WHERE RA.rcaRiskCtrlId = RC.rcnId)) --Risk and Risk Controls Actions INSERT INTO @ACTIONS -- Risk Actions fro all the risks in @RiskData table SELECT A.actOwningId AS ActionRiskId ,NULL AS ControlID ,CASE WHEN (YEAR(A.actTargetDate) > 1900) AND ( A.actState = 4 ) THEN CONVERT (VARCHAR(9), A.actTargetDate, 6) ELSE NULL END AS actTgtCompleteDate ,CASE WHEN (YEAR(A.actTargetDate) > 1900) AND (A.actState < 3) AND (A.actTargetDate <= @ThisDate) THEN CONVERT (VARCHAR(9), A.actTargetDate, 6) ELSE NULL END AS actTgtODueDate ,CASE WHEN (YEAR(A.actTargetDate) > 1900) AND (A.actState < 3) AND (A.actTargetDate <= @ThisDate) THEN NULL --'overdue' WHEN YEAR(A.actTargetDate) > 1900 AND ( A.actState = 3 OR A.actState = 4 ) THEN NULL --'complete' WHEN YEAR(A.actTargetDate) <= 1900 THEN NULL ELSE CONVERT(VARCHAR(9), A.actTargetDate, 6) END AS RATgtDate ,dbo.FULLNAME(A.actNomineeId) AS ActNominee ,dbo.FULLNAME(A.actOwnerId) AS ActOwner ,A.actCompletionAmount AS CompletionAmount ,isnull((CASE A.actOwningTableNum WHEN 2 THEN dbo.RiskControlPrefixId(A.actOwningId)+ '/' + dbo.ActionPrefixId(A.actId)+ ':' + Cast(actCommentary as Varchar(MAX)) WHEN 3 THEN dbo.ActionPrefixId(A.actId) + ':' + Cast(actCommentary as Varchar(MAX)) END),'') AS ActionDetail ,actOwningTableNum AS AType ,dbo.[ActionStatusStr](A.actState) FROM Actions A INNER JOIN @RiskData RD ON RD.rskId = A.actOwningId INNER JOIN @TopRisk TR ON TR.rskId = A.actOwningId WHERE A.actOwningTableNum = 3 AND (A.actState < 4) AND (A.actstate != 3) UNION -- Risk Control Actions for all the risk controls in @RiskCtrl table SELECT RC.rcnRiskId AS ActionRiskId ,RC.rcnId AS ControlID ,CASE WHEN (YEAR(A.actTargetDate) > 1900) AND ( -- A.actState > 2 AND A.actState <= 4 ) THEN CONVERT (VARCHAR(9), A.actTargetDate, 6) ELSE NULL END AS actTgtCompleteDate ,CASE WHEN (YEAR(A.actTargetDate) > 1900) AND (A.actState < 3) AND (A.actTargetDate <= @ThisDate) THEN CONVERT (VARCHAR(9), A.actTargetDate, 6) ELSE NULL END AS actTgtCompleteDate ,CASE WHEN (YEAR(A.actTargetDate) > 1900) AND (A.actState < 3) AND (A.actTargetDate <= @ThisDate) THEN NULL --'overdue' WHEN YEAR(A.actTargetDate) > 1900 AND ( A.actState = 3 OR A.actState = 4 ) THEN NULL --'compete' WHEN YEAR(A.actTargetDate) <= 1900 THEN NULL ELSE CONVERT (VARCHAR(9), A.actTargetDate, 6) END AS RATgtDate ,dbo.FULLNAME(actNomineeId) AS ActNominee ,dbo.FULLNAME(actOwnerId) AS ActOwner ,A.actCompletionAmount AS CompletionAmount ,CASE A.actOwningTableNum WHEN 2 THEN dbo.RiskControlPrefixId(A.actOwningId) + '/' + dbo.ActionPrefixId(A.actId) + ':' + Cast(actCommentary as Varchar(MAX)) WHEN 3 THEN dbo.ActionPrefixId(A.actId) + ':' + Cast(actCommentary as Varchar(MAX)) END AS ActionDetail -- A.actDescription END AS ActionDetail ,actOwningTableNum AS AType ,dbo.[ActionStatusStr](A.actState) FROM Actions A RIGHT OUTER JOIN RiskControls RC ON A.actOwningId = RC.rcnId INNER JOIN @RiskCtrl TRC ON TRC.rcnRiskId = RC.rcnRiskId WHERE (A.actOwningTableNum = 2) AND (A.actState < 4) AND (A.actstate != 3) IF (@RsdlInh = '1') BEGIN SELECT RD.* ,NULL AS rcnId ,NULL AS ControlInfo ,NULL AS Performance ,NULL AS Design ,NULL AS ControlOwner ,NULL AS ControlNominee ,NULL AS ControlReviewer ,A.* FROM @RiskData RD INNER JOIN @TopRisk TR ON TR.rskId=RD.rskId LEFT OUTER JOIN @ACTIONS A ON ( RD.rskid = A.ActionRiskId AND A.AType = 3 ) UNION SELECT RD.* ,RC.rcnId ,RC.ControlInfo ,RC.Performance ,RC.Design ,RC.ControlOwner ,RC.ControlNominee ,RC.ControlReviewer ,A.* FROM @RiskData RD INNER JOIN @TopRisk TR ON TR.rskId=RD.rskId INNER JOIN @RiskCtrl RC ON RD.rskId = RC.rcnRiskId LEFT OUTER JOIN @ACTIONS A ON ( A.AType = 2 AND RC.rcnId = A.ControlID ) ORDER BY RD.RsdAnnualExp DESC END IF (@RsdlInh = '2') BEGIN SELECT RD.* ,NULL AS rcnId ,NULL AS ControlInfo ,NULL AS Performance ,NULL AS Design ,NULL AS ControlOwner ,NULL AS ControlNominee ,NULL AS ControlReviewer ,A.* FROM @RiskData RD INNER JOIN @TopRisk TR ON TR.rskId=RD.rskId LEFT OUTER JOIN @ACTIONS A ON ( RD.rskid = A.ActionRiskId AND A.AType = 3 ) UNION SELECT RD.* ,RC.rcnId ,RC.ControlInfo ,RC.Performance ,RC.Design ,RC.ControlOwner ,RC.ControlNominee ,RC.ControlReviewer ,A.* FROM @RiskData RD INNER JOIN @TopRisk TR ON TR.rskId=RD.rskId INNER JOIN @RiskCtrl RC ON RD.rskId = RC.rcnRiskId LEFT OUTER JOIN @ACTIONS A ON ( A.AType = 2 AND RC.rcnId = A.ControlID ) ORDER BY RD.InhAnnualExp DESC END IF (@RsdlInh = '3') BEGIN SELECT RD.* ,NULL AS rcnId ,NULL AS ControlInfo ,NULL AS Performance ,NULL AS Design ,NULL AS ControlOwner ,NULL AS ControlNominee ,NULL AS ControlReviewer ,A.* FROM @RiskData RD INNER JOIN @TopRisk TR ON TR.rskId=RD.rskId LEFT OUTER JOIN @ACTIONS A ON ( RD.rskid = A.ActionRiskId AND A.AType = 3 ) UNION SELECT RD.* ,RC.rcnId ,RC.ControlInfo ,RC.Performance ,RC.Design ,RC.ControlOwner ,RC.ControlNominee ,RC.ControlReviewer ,A.* FROM @RiskData RD INNER JOIN @TopRisk TR ON TR.rskId=RD.rskId INNER JOIN @RiskCtrl RC ON RD.rskId = RC.rcnRiskId LEFT OUTER JOIN @ACTIONS A ON ( A.AType = 2 AND RC.rcnId = A.ControlID ) ORDER BY RD.RsdAnnualExp DESC END

推荐答案

好吧，对于 Priyanka 来说可能为时已晚，但其他人可能会觉得这很有用.

Well, it's probably too late for Priyanka, but someone else may find this useful.

有一种在临时表上创建非唯一索引的方法，使用一个小技巧:添加一个标识列并将其作为主键的最后一个字段.

There IS a way to create a non unique index on a temp table, using a small trick: add an identity column and make it the last field of your primary key.

DECLARE @MyTable TABLE (IXField1 int, IXFiled2 int, Field3 bit, HelperIX int IDENTITY (1,1), PRIMARY KEY/UNIQUE (IXField1, IXField2, HelperIX)