Skip to content

sqlserver.md

Latest commit

 

History

History
54 lines (43 loc) · 2.85 KB

sqlserver.md

File metadata and controls

54 lines (43 loc) · 2.85 KB
Raw

SQL Server

Provided by the EFCore.ComplexIndexes.SqlServer package. The core package is included automatically, and migrations need no runtime wiring — every option flows as a native SQL Server annotation that the provider's own migrations SQL generator renders.

One optional call exists: UseSqlServerComplexIndexes() registers the differ at runtime, so Database.EnsureCreated() and GenerateCreateScript() include the complex indexes and the pending-model-changes check in Migrate() sees one that was never scaffolded. See the runtime wiring section in the root README.

Index options

The EFCore.ComplexIndexes.SqlServer package brings the SQL Server option set to complex-property indexes. Like the PostgreSQL GIN/GiST options, everything flows as native provider annotations that SQL Server's own migrations SQL generator renders:

builder.ComplexProperty(x => x.Email, c =>
    c.Property(x => x.Value).HasColumnName("email"));

builder.HasComplexIndex(x => x.Email.Value, ix => ix
    .IsUnique()
    .HasName("ux_person_email")
    .IncludeProperties("name")   // covering index
    .IsCreatedOnline()           // ONLINE = ON
    .HasFillFactor(80));
// CREATE UNIQUE INDEX [ux_person_email] ON [person] ([email])
//   INCLUDE ([name]) WITH (FILLFACTOR = 80, ONLINE = ON);

IsClustered(), SortInTempDb(), and UseDataCompression(DataCompressionType.Page) are also available. Filtered indexes (filter:) and DbOrder.Desc work out of the box, since both ride on EF's native operation. A filter may name properties — filter: "{DeletedAt} IS NULL" resolves to [deleted_at] IS NULL at migrations add, bracket-quoted, and is baked into the migration.

IncludeProperties(...) entries are resolved as property paths — complex members included — with a verbatim column-name fallback, so IncludeProperties("Email.Value") finds the real column.

Deliberate rejections

Declarations SQL Server cannot express fail at dotnet ef migrations add with a targeted error rather than producing DDL that cannot apply:

  • Expression parts — SQL Server has no functional-index DDL. Model the expression as a persisted computed column and index that column instead.
  • DbOrder.NullsFirst / NullsLast — there is no NULLS FIRST/NULLS LAST in T-SQL.
  • Clustered index with INCLUDE columns — included columns are a nonclustered-index feature; a clustered index already stores every column.
  • Clustered filtered index — filtered indexes must be nonclustered.
  • A second clustered index on a table — including the usual case, where the primary key already holds the clustered slot. SQL Server clusters the primary key unless you declare HasKey(...).IsClustered(false), so that is normally what a clustered complex index collides with.