Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    2

    Unanswered: Indexes on table variable of table valued function

    Hi there,

    Can someone tell me if it is possible to add an index to a Table variable that is declare as part of a table valued function ? I've tried the following but I can't get it to work.

    ALTER FUNCTION dbo.fnSearch_GetJobsByOccurrence
    (
    @param1 int,
    @param2 int
    )
    RETURNS @Result TABLE (resultcol1 int, resultcol2 int)
    AS
    BEGIN

    CREATE INDEX resultcol2_ind ON @Result

    -- do some other stuff

    RETURN
    END

  2. #2
    Join Date
    Nov 2003
    Posts
    94
    http://support.microsoft.com/default...&Product=sql2k


    Non-clustered indexes cannot be created on table variables, other than the system indexes that are created for a PRIMARY or UNIQUE constraint.

  3. #3
    Join Date
    Jan 2004
    Posts
    2
    Thanks for the reply. That link certainly explains it all.

    Also I didn't realise that table variables can be stored on disk if MSSQL deems necessary! Taking everything into account I would think temporary tables are somewhat more useful/versatile!?

    Cheers.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •