Refactor Rename only case changes are ignored

Question

Hi guys, just trying to tidy up a database I have inherited. In doing so and fixing some incorrect casing in some of the names the rename operations appear to be refusing to change it (see messages below).

Is there a deploy setting I can set on this?

Relevent part of my publish profile is below. Note that IgnoreWhitespace, Ignore column collation, Ignore Keyword casing are all off

Rename refactoring operation with key 64996dcd-cbcf-4d42-8500-b596e870f0c2 is skipped, element [dbo].[tblInstantAward].[INomiNeeID] (SqlSimpleColumn) will not be renamed to [INomineeID]
Rename refactoring operation with key 14772cbb-8724-42b5-9358-94ebe5a0d721 is skipped, element [dbo].[tblInstantAward_IMPORTED].[INomiNeeID] (SqlSimpleColumn) will not be renamed to [INomineeID]
Rename refactoring operation with key 6df319f2-1815-4846-9a17-9cf970813447 is skipped, element [dbo].[TblServiceLine] (SqlTable) will not be renamed to [tblServiceLine]

  <PropertyGroup>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <TargetDatabaseName>
    </TargetDatabaseName>
    <DeployScriptFileName>blah.sql</DeployScriptFileName>
    <TargetConnectionString>
    </TargetConnectionString>
    <ScriptDatabaseOptions>True</ScriptDatabaseOptions>
    <BlockOnPossibleDataLoss>True</BlockOnPossibleDataLoss>
    <DeployDatabaseInSingleUserMode>True</DeployDatabaseInSingleUserMode>
    <DropObjectsNotInSource>True</DropObjectsNotInSource>
    <GenerateSmartDefaults>True</GenerateSmartDefaults>
    <IgnoreExtendedProperties>False</IgnoreExtendedProperties>
    <IgnoreWhitespace>False</IgnoreWhitespace>
    <IncludeTransactionalScripts>True</IncludeTransactionalScripts>
    <ScriptDeployStateChecks>True</ScriptDeployStateChecks>
    <ProfileVersionNumber>1</ProfileVersionNumber>
    <IgnoreKeywordCasing>False</IgnoreKeywordCasing>
    <IgnoreSemicolonBetweenStatements>False</IgnoreSemicolonBetweenStatements>
  </PropertyGroup>

Answer 1

Brett,

Is the database collation case-sensitive or case-insensitive? Also can you tell if the "Validate casing on identifiers" option is on or off?

---

-GertD @ www.sqlproj.com

Answer 2

Hi Gert, you ask an interesting question (or rather what it alludes to is interesting).

Most of the databases I work with are case insensitive in reality although I like to build effectively case sensitive so that it will work if infrastructure choose to change.

So I tend to leave as the default - Validate casing on identifiers and leaving as SQL_Latin1_General_CP1_CI_AS.

If this is the cause of the comparer ignoring my case changes, then I would argue that it shouldn't.

Database Collation should be about SQL Engine -> SQL Engine comparison, not SSDT Deploy -> SQL Engine?

Answer 3

Hi Brett

I was able to reproduce this here. We debated this for a while and came to agreement that this is the right behavior. The way to do what you're asking for is to turn on case-sensitive collation in the engine. 

Here's an explanation from one of our developers. 

To us it looks like the object already exists because by collation rules it already does.  If we were to take this change we would issue renames when there actually isn’t a rename.  For example refactors a column from c1 -> c2, deletes the refactor table and then runs the deployment again. Would you expect us to rename again?  The check for the source element existing (in the above c1) is more complicated because individual refactor operations can be changed making the check for the source element incorrect. 

Issuing an unneeded rename like this will cause a large amount of additional work – for instance, if a computed column references this to-be-renamed column we will likely rebuild the table if the computed column is in the middle of the table.

I hope that makes sense.

Answer 4

Hi Sam, thanks for the response. I'm afraid I failed to follow the rename c1 -> c2 example as this doesn't change case (it is a change of name) and so wouldn't be affected by collation?

Do you mean employeeid -> EmployeeId (to take a likely example)? If you did this refactor, deploy, then delete the refactor table, then deploy again. I think this would simply mark the refactor as a skip (becasue the case is still correct) and reenter the refactor entry into the table on the skip. I don't see any great complexity here?

If what you are saying is that the incremental update engine collation somehow has to be bound by the sql collation then I don't understand why these 2 collations can't be independent. If they were independent then you wouldn't have the rename in the computed column *unless* the computed column is already in the incorrect case vs the model. In this case, I don't believe that it is an "unneeded rename" - either you want casing to be correct (despite the database case sensitivity) or you don't.

I agree that the behaviour should be optional, but I think SSDT already provides this option "Validate casing on identifiers". It just isn't quite wired up the way as comprehensively as I was expecting.

I believe that it is a good idea to build databases as case sensitive even when the majority of the time (due to sql defaults) you are deploying to case insensitive databases. I thought that the Project Setting "validate casing on identifiers" exists exactly to reflect this. If this is set,

It definately seems to be a gap in the software to have an option to "validate casing on identifiers" locally and to spend all that time making sure casing is correct all that  but then these changes that are made are not ever deployed. Seems like a somewhat less useful option and I don't believe it is intuitive.

The real world example is as I described it. I brought in an existing database into SSDT and wanted to tidy up the casing on the table names (some tables in capitals, some in lowercase, no consistency). SSDT can help me do the renames but can't actually deploy them... there must be someway to improve the tool to help us solve this experience?

It isn't an option to change the database collation as this could break things outside of SSDT (SSRS, SSIS projects etc) and it is hard to detect those cases where only case has changed and then issue manual rename object commands in post-deploy.

An option to tweak this behaviour would be very welcome and I hope this can be reconsidered.