Adding index for table created in stored procedure

Question

I need to create clustered and non-clustered index for the table shown below. When I try to create an index for temporary table its working fine. It's throwing an syntax error when I try to create index for this table.

DECLARE @SearchReviseBudget TABLE(           
    id                 INT IDENTITY(1, 1),           
    fundid             BIGINT,           
    programid          BIGINT ,          
    projectid          BIGINT,           
    applicationnumber  VARCHAR(50),           
    applicationname    VARCHAR(100),           
    startdate          DATETIME,           
    enddate            DATETIME,           
    programtypeidfkid  VARCHAR(50),           
    applicationbudget  MONEY,           
    utlized            MONEY ,       
    [Left]             MONEY,           
    companyname        VARCHAR(100),           
    multiyearbudgetid  BIGINT,           
    totalprogrambudget MONEY )           

  ---inserting sum of amount for projects                         
  INSERT INTO @SearchReviseBudget           
              (programid,           
               fundid,           
               utlized)           
  SELECT programid,           
         fundid,           
         SUM([Utilized])           
  FROM   dbo.getapplicationbudgetandutilized           
  WHERE  fundid IN(SELECT pkid                                                     
               FROM   fundrequestheader                                      
        WHERE  pkid IN (select pkid from @TempInvoice                                

              ) )              

         AND programid IN (SELECT programidfkid           
                           FROM   usermaster u,           
                                  programaccountconfiguration pac           
                           WHERE           
             u.accountcontactidfkid = pac.acccontactidfkid           
             AND pac.accountidfkid = (SELECT accountidfkid           
                                      FROM   accountcontacts           
                                      WHERE           
                 pkid IN ( @AccContactPKID )           
                                     )           
             AND u.isactive = 1           
             AND pac.isactive = 1           
             AND u.accountcontactidfkid IN (SELECT contactid           
                                            FROM           
                 #tempcontactid))           
         --and ProjectID not in (select pkid from installationtransactionheader where parentprjnumber is null and isnull(paymentschedule,0)=1)                                              
         AND [Left] > 0.00           
  GROUP  BY fundid,           
            programid

Is that MS SQLServer? And _what_ error are you getting? – Mat Feb 06 '12 at 09:14 — Mat, Feb 06 '12 at 09:14

score 2 · Accepted Answer · answered Feb 06 '12 at 11:33

You can implicitly create indexes on table variables as part of a unique constraint.

The below will create exactly the same indexes as in the other answer (except the CI is declared as unique which is ommitted there). Though it looks as though UNIQUE(fundid, startdate,id) includes an additional key column that is not present in the second index in that answer it will be included implicitly there as the clustered index key is included at the key level of all non unique non clustered indexes.

DECLARE @SearchReviseBudget TABLE(           
    id                 INT IDENTITY(1, 1) PRIMARY KEY,           
    fundid             BIGINT,           
    programid          BIGINT ,          
    projectid          BIGINT,           
    applicationnumber  VARCHAR(50),           
    applicationname    VARCHAR(100),           
    startdate          DATETIME,           
    enddate            DATETIME,           
    programtypeidfkid  VARCHAR(50),           
    applicationbudget  MONEY,           
    utlized            MONEY ,       
    [Left]             MONEY,           
    companyname        VARCHAR(100),           
    multiyearbudgetid  BIGINT,           
    totalprogrambudget MONEY ,
    UNIQUE(fundid, startdate,id)
    )

That said table variables have no statistics maintained for them other than total row count (and you need to use OPTION (RECOMPILE) for that to be used) so #temp tables are often a better choice anyway.

Adding index for table created in stored procedure

1 Answers1