SQL SERVER NON-Clustered Index on table variable?

Question

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??

---

My proc

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

Answer 1

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)

• Ercument Eskar: Fixing the wrong field name and missing bracket DECLARE @MyTable TABLE (IXField1 int, IXFiled2 int, Field3 bit, HelperIX int IDENTITY (1,1), PRIMARY KEY/UNIQUE (IXField1, IXField2, HelperIX))

Answer 2

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.

• Reversed Engineer: Fortunately that's possible in SQL Server 2016, but wasn't in 2011 when the question was asked.

Answer 3

The only indexes you can apply to table variables are the implicit indexes which are behind PRIMARY KEY or UNIQUE constraints. If what you're wanting to index isn't unique, there's no way to index it in a table variable.

If it is unique, you have to specify the constraint when you declare the variable:

declare @t table (
 ID int not null,
 Val1 varchar(10) not null,
 PRIMARY KEY NONCLUSTERED (ID),
 UNIQUE CLUSTERED (Val1)
)
insert into @t (ID,Val1)
select 1,'abc'
select * from @t

Answer 4

You can't freely create indexes on a Table Variable like that, however you can create a PRIMARY KEY like this (note you can also make it a NONCLUSTERED PK):

DECLARE @risk TABLE (rskid int PRIMARY KEY)

Of course, assumes you will be inserting unique values.

Alternatively, you would need to use a temp table.

• Priyanka Chandok: riskid is not primary in my case . It can have duplicate values. :( I want to create index on it to increase the speed of my query.
• AdaTheDev: @Priyanka - in which case, you'd need to switch to using temporary tables instead as you can then create indexes on those.

Answer 5

Starting SQL Server 2014, you can create nonclustered index inline while declaring the table variable.

DECLARE @TabVar TABLE 
(
 ID INT PRIMARY KEY, 
 FNAME NVARCHAR(100) INDEX IX2 NONCLUSTERED
)

For earlier versions, where the indexes would get created behind the constraints, you could create an unique constraint (with an identity column included at the end if the column on which the unique constraint is to be created is not supposed to contain unique values) which would implicitly create a nonclustered index.