INDEXIES – Inserting of large amount of records

Is there any way how to create indexies after insertion of anynumber of records (I dont want to create index after insertion of every record,but for example after insertion of 1000 records) ?

I heard it should be possible with ?bulk insert" or withtransactions. Is it right ? I need do this with MS SQL Server 2005 (Workgroupedition).

Thank you for your ideas!

Jan

You could add a trigger to the table to fire on Inserts.

In the trigger, you could count the number of rows in the table.

If the number of rows is greater than 1000, use SQL "Create Index..." to create an index (check first to make sure it doesn't already exist).

But I don't understand this request. Why not create the index when the table is created?

It's true having an index will slow down inserts and on small tables will not help lookup speed much. But having to do a row count on each insert will also cause inserts to be slower.

|||If you are using SQL 2005 you might as well take advantage of the ONLINE Indexing. Bulk Insert/Transactions will not initiate any Index creation. You need to manually create the Index. And creating an Index from Trigger is not such a good idea. You might as well create a job that runs every 2-3 hrs and does a count on the table and then creates the index if the appropriate row count is reached. Understand however, that index creation is a resource intensive process. It will lock up the tables during the process of index creation.|||

Sorry, I expressed myself badly, I dont need create indexies, I meant rebuild indexies. .

The reason is that rebuilding indexies take some time andperformance so that is why I would like rebuild them just afterinsertion of any number of records (not every time).

Is there any way how to do this?

Thank you,
Jan

|||

I think you can set it up as a Maintanence Plan Task:

Use the Rebuild Index Task dialog to re-create the indexes on the tables in the database with a new fill factor. The fill factor determines the amount of empty space on each page in the index, to accommodate future expansion. As data is added to the table, the free space fills because the fill factor is not maintained. Reorganizing data and index pages can re-establish the free space.

Maintenance plans can be used to create a workflow of the maintenance tasks required to make sure that your database performs well, is regularly backed up in case of system failure, and is checked for inconsistencies. Although the Maintenance Plan Wizard can be used for creating core maintenance plans, creating these plans manually gives you much more flexibility. In SQL Server 2005 Database Engine, maintenance plans create a SQL Server Agent job that performs these maintenance tasks automatically at scheduled intervals.

|||As SGWellems mentioned you can set up a job to rebuild the indexes..depending on how many records get inserted/deleted/modified everyday you could set it to run every night or during the weekends. I wuold recommend dropping and recreating the index. This will also remove any fragmentation in the indexes.