Poor index generation on SQL Server

[tadgh]

Indexes at play:

• IDX_SP_URI_HASH_IDENTITY_V2
• IDX_SP_URI_HASH_URI_V2

Problem

  The SQL Server dialect creates a filtered index on any unique indexes that contain a nullable column. On Non-partitioned systems, this causes all data to be omitted from the index, as the partition is null

  It creates an index create statement with a WHERE clause, creating a filtered index, which causes very poor performance on partitioned servers.

Solution Design

• Remove unique = true
• drop and recreate the indexes in migrations