What are the challenges you face when working across database platforms? Take the survey
Options

Is there a work-around for temp tables with inline indexes (containing included columns)?

We're having problems trying to check in stored procedures when they contain a temp table definition that has an inline index that has included columns. Example:

CREATE TABLE #TempExample (
EntryId INT NOT NULL,
UserId INT NULL,
Points DECIMAL(10,2) NOT NULL,
[Rank] INT NULL,
PRIMARY KEY (EntryId),
INDEX IDX_TempExample_Rank([Rank]) INCLUDE (EntryId, Points)
);

When trying to check in a procedure with something like this, the commit looks like it takes, but the stored procedure continues to show up in the list of changes.

Also, I've noticed that  SQL prompt underlines "(EntryId" in the INCLUDE portion of the index definition and the mouseover text says: " Incorrect syntax near '(' " and " Incorrect syntax near 'EntryId' ".
Tagged:

Answers

  • Options
    I believe you cannot included column with primary key .you can do you can create a temp table and them create a index after the table creation which will be easiest solution 
  • Options
    ScottSSScottSS Posts: 6 New member
    Thanks for your response. The primary key CAN be specified as an included column on in index. You can run my sample code in SQL 2019 and see. But for sake of argument, you can also just remove EntryId from the index as well and our problem still exists.

    You are right that we could get around this Red Gate SQL Source Control issue by creating the indexes after the table creation. However, for performance reasons, we do not want to do this. You can see this article: https://sqlperformance.com/2017/05/sql-performance/sql-server-temporary-object-caching which states:

    "A common pattern that disables caching for temporary tables is the creation of indexes after the initial table creation statement. ... In SQL Server 2014 and later, we have the option of adding non-unique nonclustered indexes directly in the table creation statement using the INDEX clause."

    Basically, this seems like valid SQL that the Red Gate SQL Prompt and SQL Source Control aren't seeing as valid.
  • Options
    ScottSSScottSS Posts: 6 New member
    edited May 29, 2020 2:39PM
    duplicate response.
  • Options
    ScottSSScottSS Posts: 6 New member
    edited May 29, 2020 2:39PM
    duplicate response. sorry.
  • Options
    ScottSSScottSS Posts: 6 New member
    edited May 29, 2020 2:39PM
    duplicate response. really sorry.
  • Options
    Jessica RJessica R Posts: 1,319 Rose Gold 4
    Hi @ScottSS ,

    Apologies as I don't have a SQL Server 2019 instance on hand to quickly test with, but while I get this instsalled, can I just confirm if 

    INDEX IDX_TempExample_Rank([Rank]) INCLUDE (EntryId, Points) 

    is SQL 2019 syntax?

    (Trying to execute your query on SQL 2017 fails with "incorrect syntax" error)

    Jessica Ramos | Product Support Engineer | Redgate Software

    Have you visited our Help Center?


  • Options
    ScottSSScottSS Posts: 6 New member
    edited June 2, 2020 4:55PM
    Yes, the whole create table with the inline index is valid in SQL 2019.

    Here's a slightly different example:

    CREATE TABLE #TempExample ( 
    	EntryId INT NOT NULL, 
    	UserId INT NULL, 
    	Points DECIMAL(10,2) NOT NULL, 
    	[Rank] INT NULL, 
    	PRIMARY KEY (EntryId), 
    	INDEX IDX_TempExample_Rank([Rank]) INCLUDE (UserId, Points)
    );

    Edit: It's actually valid SQL in version 2016 as well.
  • Options
    Hi @ScottSS ,

    Thanks for the clarification. I was able to reproduce the behavior in a 2019 instance so I'm going to escalate this to our development team for review. Unfortunately, I don't have a workaround for this just yet but I'll get back to you with further instruction. So sorry for any inconvenience.
  • Options

    This issue has been logged in our internal bug tracking system and could now be referenced by SC-10856. I will update you once I hear back from our developers on the progress.

    Kind regards,

    Pete Ruiz


  • Options

    Hi @ScottSS,


    Thanks for your patience with this issue SC-10856.


    The development team have reviewed the bug as part of a periodical review and prioritization of open issues and, unfortunately, they are unable to fix this bug.


    When reviewing bugs the development team take into account the impact it has and the number of users affected and unfortunately we are unable to fix all bugs and have to prioritize fixing the ones that cause the greatest impact.


    I am going to mark this ticket as resolved, however if you have any questions regarding this please do not hesitate to reply to this message.


    Thank you for your understanding!


    Pete Ruiz
Sign In or Register to comment.