Why is an index created more than one time in SQL via Entity Framework

I have written a service, which imports every night more than 960.000 rows to a SQL Database. This data will be used for other operations few hours later.
The application is a .Net application, with Entity Framework on it. Import is being made with EntityFramework BulkExtensions.

After 3-4 Imports, I receive TimeoutExceptions.

  • Partitioned view execution plan
  • change data source dynamically from .net application & pass to connectiong string
  • SQL Server: Why xp_cmdshell is disabled by default?
  • Script to save varbinary data to disk
  • nvarchar(max) vs NText
  • Error - LINQ/TransactionScope with multiple database connections
  • Some experts from my company reviewed my whole code. From Code-Side everything seems to be ok.

    Now I have taken a look to the SQL Server. It is running a SQL Server 2014 Professional.

    After running the query:

    SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName, 
           i.name AS IndexName, 
           indexstats.avg_fragmentation_in_percent 
           FROM sys.dm_db_index_physical_stats(DB_ID(), 
                                               NULL, 
                                               NULL,
                                               NULL, 
                                               'DETAILED') indexstats 
           INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID 
           WHERE indexstats.avg_fragmentation_in_percent > 30 
           AND i.index_id = indexstats.index_id
    

    I got following Result:
    Result of the Query

    Now my question is, why is Index2 being created more than 1 time?
    I have expected to get only one row for Table 2 with one Index (IX_Index2).

  • How To Add Value To A Column And Update Database
  • How to encrypt Sql connection string?
  • Code for inserting data into SQL Server database using Enterprise library
  • Using a List as a condition for LINQ to SQL query
  • How to execute stored procedure in C# with different parameters?
  • SQL Query that runs fine in SSMS runs very slow in ASP.NET
  • One Solution collect form web for “Why is an index created more than one time in SQL via Entity Framework”

    This doesn’t answer the question directly I’m afraid.

    Regardless of your above query, you may want to consider dropping or disabling your index prior to the data load and then rebuilding / enabling it after the data load. This will probably be more efficient as otherwise the index will get updated as the data is being imported.

    This article provides some simple code to show you how to do this if you’re not already doing so:

    To disable in index, issue an ALTER INDEX command.

    ALTER INDEX IX_IndexName ON Schema.TableName DISABLE; GO
    

    If you want to re-enable the index, you need to rebuild it – there is
    no ALTER INDEX…ENABLE command. The rebuild works like this:

    ALTER INDEX IX_IndexName ON Schema.TableName REBUILD; GO
    

    To drop a clustered or nonclustered index, issue a DROP INDEX command.

    DROP INDEX IndexName ON Schema.TableName; GO
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.