1

We use SSDT to deploy our database changes. We have a script that recreates the index every week. Our script looks like this:

declare @cmd varchar(max)
set @cmd = '
CREATE NONCLUSTERED INDEX [iAudit-ModifiedDateTime] ON [dbo].[Audit]
(
[ModifiedDateTime] ASC
)
WHERE ModifiedDateTime > ''###''
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 75) ON [PRIMARY]
'
set @cmd = replace(@cmd, '###', convert(varchar(8), dateadd(day, -3, getdate()), 112))
exec (@cmd)

Unfortunately when we run SSDT to update the database it changes the index to the definition in the project, or drops it when it is not included. Is there some way I can get around this? The reason we need the filtered index is to add the latest records from an Audit table with 100's of millions of rows, into a data warehouse.

Ron Osmo
  • 11
  • 2

1 Answers1

2

There are some options, in order of complexity:

  • Don't include the index definition in the project and disable the "Drop indexes not in source" option. In Visual Studio this is found in the Advanced options dialog of the Publish dialog. When using SqlPackage.exe to publish, you can use the parameter /p:DropIndexesNotInSource=false
  • Don't include the index definition in the project and put the index creation script into a post-deployment script. This will ensure that the index is always recreated after schema updates are deployed.
  • Use a community-authored deployment contributor to filter out modifications to this index. See https://the.agilesql.club/Blogs/Ed-Elliott/HOWTO-Filter-Dacpac-Deployments
  • Author a deployment contributor to filter out modifications to this index. See https://github.com/Microsoft/DACExtensions/
Steven Green
  • 3,387
  • 14
  • 17