10
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)