Menu
How can we create non-clustered index on table variable?
Create Table @risk (rskid int)
Create nonclustered index r_rskid_nc on @risk(rskid)
It does not work??
How can i optimize it ??
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
923
Each table can have up to 999 nonclustered indexes, regardless of how the indexes are created: either implicitly with PRIMARY KEY and UNIQUE constraints, or explicitly with CREATE INDEX .
Short answer: Yes. A more detailed answer is below. Traditional tables in SQL Server can either have a clustered index or are structured as heaps. Clustered indexes can either be declared as unique to disallow duplicate key values or default to non unique.
A non-clustered index doesn't sort the physical data inside the table. In fact, a non-clustered index is stored at one place and table data is stored in another place. This is similar to a textbook where the book content is located in one place and the index is located in another.
Creating an index on a table variable can be done implicitly within the declaration of the table variable by defining a primary key and creating unique constraints. The primary key will represent a clustered index, while the unique constraint a non clustered index.
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)
I do not know which version you are using but I am using SQL server 2016 and it is as simple as
declare @Stock table
(
StockId int NOT NULL
, FieldId int NOT NULL
, StockDate date NOT NULL
, StockValue float NULL
, INDEX IX_Priyanka NONCLUSTERED(StockId, FieldId, StockDate desc)
)
Hope it covers some aspects of what you are looking for.
22
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
