What's the difference between Local and Default SQLCMD variables in SQL Server Database Project properties?

Question

I have a SQL Server database project (VS2017) and in project properties, the SQLCMD Variables tab looks like this:

Question: I can create a SQLCMD variable and set a Default Value and a Local Value for it. What is the difference?

Note: I do currently use SQLCMD variables in my project, so I think I know how they work, but I can't get my head round this distinction. According to what documentation I can find (highlighting is mine):

In SQL Server Database Projects you can utilize SQLCMD variables to provide dynamic substitution to be used for debugging or publishing. You enter the variable name and values and during build, the values will be substituted. If there are no local values, the default value will be used. By entering these variables in project properties, they will automatically be offered in publishing and are stored in publishing profiles. You can pull in the project values of the variables into publish via the Load Values button.

So it seems that:

• If you only have a Default value, that will be used
• If you only have a Local value, that will be used
• If you have both, the Local value will always be used.

How does this help me? There seems to be no point in having both values set, so why do we need two different values?

Michaels

Answer 1

Local has higher precedence over default when Publishing.

This means that if you have both default and local values filled in, and click Publish, your variable values in Publish window will be automatically filled by local values, even without you clicking on the Load Values button.

If you only have default values, then no values will be automatically filled in the Publish window, until you click on the Load Values button.
Then values will get filled in with the default values.

You can always override the variable values in the Publish window, even the ones which were filled in as local.

So, purpose of local values is to prepare the filled in, final values when Publishing.
Leaving them empty demands for you to either click on Load Values to get the default values, or to manually fill the variable values.

Dogcatcher

"This means that if you have both default and local values filled in, and click Publish, your variable values in Publish window will be automatically filled by local values, even without you clicking on the Load Values button." Unfortunately, this is not my experience. When I click Publish, no values are loaded and the Publish button is greyed out. I have to click Load Values in order to publish. This is true whether I have Default values, Local values, or both. – Michaels

Answer 2

so why do we need two different values?

• The value you provide in the Default column will be stored in the project file (.sqlproj), therefore should be source controlled.

• The Local value is stored in the non-version controlled .user file (.sqlproj.user).

While the Local value will override the default value when building locally, if you use a build server - the default value has to be set because the .user file won't be used by the build server, it will use the default value.

Germinal