Post reply

Non clustered Index on #temp table

  • April 4, 2021 at 10:44 pm

    Go to Answer

    #3865390

    Hi,

    In order to avoid a SORT operation in my execution plan, I want to create a Non clustered Index on a Temp table. However I am getting the below error which makes sense since it is a temp table that is created on the fly, however, is there any other way? please advise.

    ERROR : Msg 1088, Level 16, State 12, Line 5

    Cannot find the object "#tbDemoEmp" because it does not exist or you do not have permissions.

    Index I am trying to create: CREATE NONCLUSTERED INDEX ix_COL_TEST ON #tbDemoEmp(COL:);

     

     

    • This topic was modified 3 years, 7 months ago by  sizal0234.
    • This topic was modified 3 years, 7 months ago by  sizal0234.
    • This topic was modified 3 years, 7 months ago by  sizal0234.

  • April 5, 2021 at 2:53 am

    Answer

    #3865439

    This is resolved, I added the IDX Inside the SP and it works fine. Please ignore.

    Not sure how to delete this post hence posting the below reply to save your time. Sorry for any inconvenience.

  • April 5, 2021 at 7:03 am

    #3865487

    Vastly better to instead create a clustered index before you load the temp table.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply