Not Able To Build Index on imported table

mlcktmguy
mlcktmguy used Ask the Experts™
on
I'm pretty new to SQL, using SQL 2014 Developer edition.

I imported an MS Access table into my SQL DB using the 'SQL Server 2014 Import and Export Data'

For some reason the 'Import' did not preserve any of the indexes set up on the table in Access.

I want to re-create the indexes from within SQL Server Manager.

However, when I right click in the 'Index' option underneath the table name the 'New Index' option is grayed out and cannot be selected.

On all of the other tables the 'New Index' option is not grayed out.  I can select it and build an index on the table.

Any ideas on what could be causing this issue?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Why don't you try creating the Indexes using Queries(CREATE INDEX) and see if it is allowing?
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
For some reason the 'Import' did not preserve any of the indexes set up on the table in Access.
Import and Export wizard only works with data, so no indexes, triggers, permissions are imported/exported.
You'll need to script your Access table and adapt the script to run in SQL Server.
Database Analyst
Commented:
Solution: Close your table designers. If that doesn't help, close all windows in Management Studio.
                 ....Actually better and easier to just quit SQL SSMS and get back in.
Cause: The "New Index" option gets disabled when the table is schema-locked.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial