SQL Server Performance

Rename table will affect indexes?

Discussion in 'General DBA Questions' started by enum5, Dec 5, 2006.

  1. enum5 New Member

    I am trying to rename a table in a database. The table name is Message_Log and has about 300 Million records. This table has one clustered index and 5 non-clustered indexes. I want to rename it to Message_Log_200611. Then I create a new table with name Message_Log. Will this affect the indexes in old Message_Log table? Will the process takes long time to finish?
  2. thomas New Member

    The indexes will stay with the table even as it's renamed. If you need the indexes on the new table, you need to create them explicitly.
  3. enum5 New Member

    Thank you for your answer. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />May I know is index just used for query record in the table? Does it have any other usage?
  4. madhuottapalam New Member

    Hi,

    Index is nothing but a auxillary table which is used to improve the database quries performance. I do not thinks is there any other use of indexes. yes.... in SQL 2005 you can do a trick using Clustered Indexe. There is a new feature available to ENable and Disable indexes in SQL Server 2005. If you disable a clustered index on a table the table will not be available till u rebuilt or recreate that index. ie you can temporarily block the table from users.

    madhu


  5. thomas New Member

    Indexes are used to enforce uniqueness.
    They enable fast querying of data by efficiently locating required data.
    They are used for joining related tables together.
  6. enum5 New Member

    I already renamed Message_Log to Message_Log_200611. Then I create a new table called Message_Log again. The structure of this table is same with the previous Message_Log table, which means all fields are same. Then I create index for the new table. I found that the index name for clustered index cannot be same with previous table index name. I have some doubt here.

    1) Will the indexes in my new table work fine if the clustered index has different name with the previous table and the non-clustered indexes have same name with the previous table?

    2) If both tables has same name for non-clustered indexes, will the indexes of new and old tables corrupted or the searching speed of these 2 tables become slower?

    3) Is it the best way if I create all the indexes in new table with new name?

    Thank you.
  7. Roji. P. Thomas New Member

    quote:Originally posted by enum5

    I already renamed Message_Log to Message_Log_200611. Then I create a new table called Message_Log again. The structure of this table is same with the previous Message_Log table, which means all fields are same. Then I create index for the new table. I found that the index name for clustered index cannot be same with previous table index name. I have some doubt here.

    1) Will the indexes in my new table work fine if the clustered index has different name with the previous table and the non-clustered indexes have same name with the previous table?
    Index names need not be unique across the database, but unique for a table. That means you can have the same name for a clustered or non-clustered index for different tables.


    quote:
    2) If both tables has same name for non-clustered indexes, will the indexes of new and old tables corrupted or the searching speed of these 2 tables become slower?
    The index names have no significance other than that of an identifier.

    quote:
    3) Is it the best way if I create all the indexes in new table with new name?
    Doesn't matter.

    You need to have a deeper understanding about indexes in general.
    Have a look at the Index related topics in BOL

    Roji. P. Thomas
    http://toponewithties.blogspot.com

Share This Page