Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Performance on indexing

Author  Topic 

Champinco
Yak Posting Veteran

54 Posts

Posted - 2007-03-20 : 20:32:13
Just a quick qn.
I have a table that currently holds approx 66 million records, and am in the process of indexing the appropriate columns. My question is after I index this main table, i will be creating new tables from it according to months as well as certain columns. (ie: 12 new subset data tables). Will the indexes carry over from the main table? Or will i have to re-create the indexes on these new tables?
If so then should i first create the subset datasets then index or can i do the latter?
Cheers
Champinco

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2007-03-20 : 20:46:59
If I get your meaning, the indexes will not "carry over" from one table to another. When you create an index, you have to define what table it belongs to, so you will have to reference the new table anyway. If you have a lot of records, usually it will be faster to populate the table first and then create the indexes afterward.
Go to Top of Page

Champinco
Yak Posting Veteran

54 Posts

Posted - 2007-03-20 : 20:52:35
So there is no use in creating indexes on my main table, as I will be creating sub sets from this table. i.e: select * into table_January from (select the query from main table)
Then i should index the sub tables?
Champinco
Go to Top of Page

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2007-03-20 : 20:54:54
Right. If you use the SELECT INTO ... syntax, that will only create a new table structure. It won't create indexes, constraints, defaults, etc. Those will all have to be created after the new table has been created and populated.
Go to Top of Page

Champinco
Yak Posting Veteran

54 Posts

Posted - 2007-03-20 : 20:55:07
The indexes wont carry over to the subset tables from my main one?
Go to Top of Page

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2007-03-20 : 21:04:04
For the third time, no. You don't believe me? Try it yourself.
Go to Top of Page

Champinco
Yak Posting Veteran

54 Posts

Posted - 2007-03-20 : 21:10:41
Clarifying...not doubting. :)
Go to Top of Page
   

- Advertisement -