SqlPackage Extract -> Publish does not round-trip a DiskANN vector index (schema-only publish fails with Msg 42266)
Nobody has claimed this yet.
- Dominant language
- C#
- Stars
- 460
- Forks
- 29
- Avg merge
- 4d 9h
- Merged PRs (30d)
- 7
Description
### Summary
`SqlPackage` (DacFx) cannot round-trip a **DiskANN vector index** through an
`Extract` → `Publish` cycle. Extracting a schema-only `.dacpac` from a database
that contains a `CREATE VECTOR INDEX ... WITH (TYPE = 'diskann')` succeeds, but
**publishing that `.dacpac` to a new, empty database fails**. The generated
deployment plan creates the table empty and then issues `CREATE VECTOR INDEX`,
which the engine rejects because a DiskANN index requires at least 100 non-null
vector rows. Since a schema-only dacpac carries no table data, the precondition
can never be satisfied and the publish is unrecoverable.
### Environment
- **SqlPackage / DacFx version:** 170.3.93.6
- **Target platform:** Azure SQL Database (SQL Server 2025 engine; `VECTOR(n)` type + DiskANN vector index)
### Steps to reproduce
**1. Apply this schema to a source database — succeeds (the index builds on the 128 seeded rows):**
```sql
DROP TABLE IF EXISTS dbo.vec_repro;
GO
CREATE TABLE dbo.vec_repro (
id INT IDENTITY(1,1) PRIMARY KEY,
embedding VECTOR(4) NULL
);
GO
-- Seed >= 100 non-null vector rows so the DiskANN index can build on the source.
-- This data lives only in the source database; a schema-only dacpac does not carry it.
INSERT INTO dbo.vec_repro (embedding)
SELECT TOP (128) '[0.1, 0.2, 0.3, 0.4]'
FROM sys.all_objects;
GO
CREATE VECTOR INDEX vec_repro_diskann
ON dbo.vec_repro (embedding)
WITH (METRIC = 'cosine', TYPE = 'diskann');
GO
```
**2. Extract a schema-only dacpac:**
```
sqlpackage /Action:Extract \
/SourceServerName: /SourceDatabaseName: /SourceUser: \
/TargetFile:repro.dacpac
```
This succeeds; the exported `model.xml` contains the DiskANN vector index.
**3. Publish the dacpac to a new, empty database — fails:**
```
sqlpackage /Action:Publish \
/SourceFile:repro.dacpac \
/TargetServerName: /TargetDatabaseName: /TargetUser:
```
### Expected behavior
Publish completes and produces a schema-equivalent copy, or DacFx orders/handles
vector-index creation so that a schema-only deployment does not hit the engine's
≥100-row precondition (or surfaces a clear, documented limitation).
### Actual behavior
```
Creating Table [dbo].[vec_repro]...
Creating SqlVectorIndex [dbo].[vec_repro].[vec_repro_diskann]...
An error occurred while the batch was being executed.
Updating database (Failed)
*** Could not deploy package.
Error SQL72014: Core Microsoft SqlClient Data Provider: Msg 42266, Level 16,
State 1, Line 1 Cannot create a vector index. The table contains only 0 rows
with non-null vectors, but at least 100 are required for vector index creation.
Error SQL72045: Script execution error. The executed script:
CREATE VECTOR INDEX [vec_repro_diskann]
ON [dbo].[vec_repro] ([embedding]) WITH (METRIC = 'COSINE');
```
The target database is left with the table created but the index missing — a
partial, failed deployment.
### Additional observation — DDL fidelity loss
The DDL DacFx generated on publish **dropped the `TYPE = 'diskann'` option**. The
index was authored as `WITH (METRIC = 'cosine', TYPE = 'diskann')`, but the
script DacFx executed was `WITH (METRIC = 'COSINE')`. So beyond the data-precondition
failure, the index *type* specifier is not preserved through extract/publish.
### Questions
1. Is deploying a DiskANN vector index via schema-only `Publish` supported? If not, is it planned?
2. Is there a publish property to defer vector-index creation past the ≥100-row precondition during DDL deploy?
3. Is the loss of `TYPE = 'diskann'` in the generated script a known issue?
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Reproduce the schema-only Extract → Publish cycle described for SqlPackage and inspect the exported model.xml and generated deployment script. Confirm whether the DiskANN TYPE option and the index creation ordering are preserved; done means identifying a supported deployment behavior or a clear limitation and required fix.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- azure, csharp, sql
- Domain
- cloud, database, tooling
- Issue type
- Bug
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Quiet
- Clarity
- Needs clarification
- Newbie friendliness
- 35/100