How to create Indexes with SSDT

Question

Creating a SQL Function in SSDT works smoothly. When I modify the function's SQL, I can generate the DACPAC, then publish the DACPAC and the database is automatically updated. My function SQL looks like:

CREATE FUNCTION [dbo].[foo]
(
...
)
RETURNS int
AS
BEGIN   
...   
END

This is a file called foo.sql with build action to Build.

When I need to add a database index, I add an Index file to my project and put in:

CREATE NONCLUSTERED INDEX [idxFoo]
ON [dbo].[tblFoo] ([id])
INCLUDE ([fooVal])

If I try to build it I get several SQL71501 errors.

I was forced to add all Indexes in a common file set to PostDeploy.

Do you have a GO between your Table Create and Index Create statements in your table definition? I haven't seen a lot of errors unless I forget little things like that to separate the batches. If you're in the table designer area and add an index there, what happens? Are you trying to add a CREATE INDEX statement after your CREATE FUNCTION statement? — Peter Schott
Separate file for each element. No "GO". If I copy the table definition into the project, the errors resolve. — jlo-gmail
I guess I'd assumed you had a working/building project already. You really need everything from the DB in your project to have something that will work well for you. Either import your DB into the project or do a compare against the DB to update the project. At that point, you'll have everything you need to add new indexes, functions, procs, etc. — Peter Schott
That is what the DacPac reference is SUPPOSED to do ("Extract Data Tier Application...) — jlo-gmail

jlo-gmail jlo-gmail · Accepted Answer · 2016-09-29T18:40:53

I have found numerous references to including a DACPAC reference to the project-which I did. This works for most items, but not Indexes. I have no idea why.

I needed to add Table definitions to the project of the "missing" referenced objects in the Indexes. In order to get the script to create the tables, I used the VS Sql Server Object Explorer. Right click on table and select View Code (includes the table's existing Indexes and other elements). NOT Script As->(table create sql only) If you don't SQLPackage.exe will delete the Indexes not defined in your project.

How to create Indexes with SSDT

2 Answers