Suggestions for the SQL Server Upsizing Wizard

[ SQL Server Upsizing | Access Tips | Main ] (Updated 2010/09/27) 

I noticed some indexes using GUID names were created as per the following example
CREATE INDEX [{21A84A97-5492-4027-83AA-33C67D68D1EA}] ON [dbo].[WeldDetailsWelders]([wdwShiftID]) ON [PRIMARY]
These are caused by relationships from within Access 97 and newer which clearly isn't required in SQL Server. Thus these should be ignored by the Upsizing Wizard. 

Also note though that many times these GUID index names are duplicates of index names automatically created by the Auto Index feature. Tools >> Options >> Tables/Queries >> AutoIndex on Import/Create. Where fields ending in 'ID; key; code; num' are automatically indexed. And my naming standards dictate that all my primary and foreign keys end in ID. Or these GUID indexex are duplicates of indexes create by the MDB designer.

Thus the upsizing wizard should check to see if these are duplicates of already existing indexes and delete them.

I still haven't figured out why Access97/Jet requires a unique index on a primary key for each and every table with a foreign key to that field.   Waste of indexes in my not so humble opinion.

By DG. When you create a relationship, Access creates foreign-key indexes. Under some circumstances, these foreign-key indexes wind up with GUID names. 'Table lookup' also creates foreign-key indexes, but you can get indexes with names like this just by defining table relationships for DRI. If you have 'automatic indexing' turned on, and are using DRI, it is easy to get duplicate indexes. A good idea would be if the upsizing wizard deleted indexes with GUID names if there was an identical index.

With respect to the replacing field types ncharvar with charvar, nchar with char and bit with int I feel Microsoft should add these options to the Upsizing Wizard and default them where appropriate.  For example if the MDB is in Access 97 or earlier format or if, possibly, Unicode Compression is Yes, then default to the non unicode field types.

[ SQL Server Upsizing | Access Tips | Main ]

   Auto FE Updater distribute new and updated Front End databases to your users with several mouse clicks.

Granite Fleet Manager - the best designed fleet maintenance tracking and management system available