microsoft / microsoft/DacFx

SqlPackage Extract -> Publish does not round-trip a DiskANN vector index (schema-only publish fails with Msg 42266)

Open
#799 4 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

sql-feature
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

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.