0

I am using Microsoft SQL Server Migration Assistant v6 (aka SSMA) to update a database from Access to SQL Server. I am getting informational messages that say "A2SS0029: Index name 'Name' was changed during conversion.".

As these are just index names, that shouldn't cause a problem for me. But I do wonder why the name has to be changed. Particularrly since I was able to create an index with the given name manually after the migration.

Why are the index names being renamed? And am I wrong about that not causing any problems (given that I don't have any queries that use index hints, if that is even possible with Access).

4
  • 1
    For certain entities (e.g. a primary key) the name of the index/constraint must be unique. For example this works but this does not. Isn't Name a terrible name for an index in any case? Why are you upset that SQL Server is changing it on you? Jan 15, 2015 at 21:27
  • So is it renaming indexes that have the same name in different tables? What is it renaming them to? It is probably just being conservative and preventing any duplicate names. Jan 15, 2015 at 21:43
  • @AaronBertrand: That sounded reasonable, assuming that it found duplicates and then renamed those. But further investigation shows it seems to simply rename all indexes. Possibly it renames all indexes when it finds any duplicates, I haven't tried having all unique names.. Ah, well, now that I know it is doing it to everything, I'm less concerned about it somehow causing problems.
    – jmoreno
    Jan 15, 2015 at 22:18
  • Try the second link again. Jan 16, 2015 at 1:25

1 Answer 1

2

While there are reasons that a tool might make intelligent decisions about index names (like to prevent duplicate names in cases where there are actual collisions, and further when it actually matters, like when they are in the same table), it would appear that SSMA just creates new, unique names for all indexes. Perhaps only in the case where it finds at least one index name that is reused, not sure. I would test this, but I don't have Access installed anywhere.

Why does it do that? Who knows? Extreme conservatism? Paranoia? I think it's just being extremely safe.

There are cases where duplicate names would be a problem, but these are isolated to first class entities that end up in sys.objects, like primary key constraints. Index names are only enforced to be unique at the table level (since the name is only exposed in sys.indexes), not at the database level.

3
  • @AaronBertand: I tested it with all unique names, and SSMA still renames them all. As a side note, while it won't really cause me any real problems as the code that does it will be going away, the index names ARE being being used by the application for some some queries.
    – jmoreno
    Jan 16, 2015 at 3:07
  • @jmoreno How is the application using index names? With index hints? Please be very careful with that. Jan 16, 2015 at 3:08
  • It's a VB4/5 app that was upgraded to VB6, it uses DAO and the index names are specified for several queries. Why it did that, I have no idea. I'm currently planning on a .NET webapp and MS SQL. No need for index hints, even if I was tempted to do so.
    – jmoreno
    Jan 16, 2015 at 3:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.