Activity overview
Latest activity by 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. / comments
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(...
duplicate response. / comments
duplicate response.
duplicate response. sorry. / comments
duplicate response. sorry.
duplicate response. really sorry. / comments
duplicate response. really sorry.
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. / comments
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 Entr...
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 ...