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

ScottSS

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' ".

pramodhkumar_tammana

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 

ScottSS

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.

ScottSS

duplicate response.

ScottSS

duplicate response. sorry.

ScottSS

duplicate response. really sorry.

Jessica R

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)

ScottSS

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.

Pete_Ruiz

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.

Pete_Ruiz

Hi @ScottSS,

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

Pete_Ruiz

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