CODEWITHSUNDEEP
sql-servervisual-studiostatisticsdbproj
Alsin
Alsin

Reputation: 1618

STATISTICS_INCREMENTAL in CREATE TABLE

This syntax is legitimate as you can see there

CREATE TABLE dbo.test1
(
  ID int not null,
  SomeDate datetime2(7) not null,
  CONSTRAINT PK_test1 PRIMARY KEY CLUSTERED (ID, SomeDate) WITH (STATISTICS_INCREMENTAL = ON)
  ON PS(SomeDate)
) ON PS(SomeDate)
go

I can run it on SQL Server 2017 without any issues, but Visual Studio is not happy with this script in a DB project. I get this error

SQL70616: Unsupported index option specified 'StatisticsIncremental' for [dbo].[test1].

I tried to fix this with

UPDATE STATISTICS dbo.test1 PK_test1 WITH RESAMPLE, INCREMENTAL = ON

but no luck, I got

SQL70001: This statement is not recognized in this context.

EXEC and sp_executesql also not recognized.

Any chance to explain this to DB Project? I afraid automatic deployment will detect this discrepancy between model and DB and would try to rebuild a table on every deployment.

Visual Studio 16.8.1

SQL Server Data Tools 16.0.62010.06180

Edit 1: It raises the same error if I put it in a separate statement.

ALTER TABLE dbo.test1
ADD CONSTRAINT PK_test1
PRIMARY KEY CLUSTERED (ID, SomeDate) 
WITH(STATISTICS_INCREMENTAL = ON)
ON PS(SomeDate)

Edit 2: It does work as a clustered unique index. No errors were raised. It would do, but I'd prefer to have it as a constraint.

CREATE UNIQUE CLUSTERED INDEX PK_test1 ON dbo.test1(ID, SomeDate)
WITH(STATISTICS_INCREMENTAL = ON)
ON PS_SportsHub(SomeDate)

Edit 3: The target platform is Microsoft Azure SQL Database. It makes no difference, I tried SQL Server 2019/2017 as well.

Upvotes: 0

Views: 413

Answers (1)

JoeF
JoeF

Reputation: 843

A bug fix in version Visual Studio 16.11.8 was created to address this issue:

https://developercommunity.visualstudio.com/t/VS-sqldb-project-unable-to-create-primar/1528961

Upvotes: 0

Related Questions