Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Indexing During an INSERT INTO Statement Expand / Collapse
Author
Message
Posted Tuesday, May 13, 2008 4:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 8:31 PM
Points: 13, Visits: 61
I have a script that appends (INSERT INTO) records from a staging table to a production table that has many indexes on it. At the end of the script I refresh all the indexes automatically for that table (using DBCC DBREINDEX(tblName,'').

Is there a way to have the indexes update automatically during the INSERT INTO statement, so that I don't have to run the DBREINDEX statement?

I realize this might not be optimal from a performance standpoint, but would like to test it anyway, do some comparisons, etc.

Is there a setting that lets me do this?
Post #499439
Posted Tuesday, May 13, 2008 6:05 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 1:53 PM
Points: 1,030, Visits: 2,791
I have a script that appends (INSERT INTO) records from a staging table to a production table that has many indexes on it. At the end of the script I refresh all the indexes automatically for that table (using DBCC DBREINDEX(tblName,'').

Is there a way to have the indexes update automatically during the INSERT INTO statement, so that I don't have to run the DBREINDEX statement?

I realize this might not be optimal from a performance standpoint, but would like to test it anyway, do some comparisons, etc.

Is there a setting that lets me do this?


RD - in answer to your question the indexes will automtically 'be updated' when the insert happens, that is the new data will be included in all the indexes on that table.

Without going into detail on how indexes work many new inserts on a given table can cause the indexes to become fragmented and then not used in future query plans as a table scan is deemed more efficient. rebuiling the indexes at the end of insert script ensures that fragmentation is minimised and the index remains useful...Hope this helps


Gethyn Ellis

gethynellis.com
Post #499494
Posted Tuesday, May 13, 2008 6:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 8:31 PM
Points: 13, Visits: 61
Very helpful, thanks Ellis.
Post #499544
Posted Monday, May 19, 2008 2:20 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 06, 2012 10:04 AM
Points: 144, Visits: 455
DBCC INDEXDEFRAG is command to defrag an index.
Post #503138
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse