0

I use the following code to import a table from an Oracle DB to SQL Server every day:

TRUNCATE TABLE mytable

INSERT INTO mytable (firstname, lastname, city, state)
SELECT * FROM OPENQUERY (mylinkedserver, 'SELECT firstname, lastname, 
city, state FROM mylinkedtable')

The above causes massive fragmentation (90% to 99%) on my nonclustered indexes on "mytable". If I run the below code

SELECT * INTO mytesttable
FROM mytable

TRUNCATE TABLE mytable

INSERT INTO mytable
SELECT * FROM mytesttable

DROP TABLE mytesttable

I end up with fragmentation of between 1% and 3% on my nonclustered indexes. What is causing this? Both statements are truncating the table and inserting new data into a blank table.

Lloyd Banks
  • 35,740
  • 58
  • 156
  • 248
  • 1
    What indexes exist in the top version? How many pages are created? Is it possible you're worried about fragmentation for nothing? – Aaron Bertrand Aug 14 '12 at 20:16
  • For example, please have a good read through this, and make sure that you're looking at numbers other than just fragmentation percent. If you've loaded 100 pages onto disk, fragmentation is absolutely 100% irrelevant. http://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/ – Aaron Bertrand Aug 14 '12 at 20:34

0 Answers0