Post reply

Is sequential indexing possible ?

  • August 18, 2003 at 12:06 pm

    #82219

    Is sequential indexing possible ?

    Can anyone please direct me to an article or some guidance as to how I could index a table sequentially ?

    My automatically inserted by SQL primary key in a table looks like this:

    1

    2

    3

    5

    12

    45

    345

    455

    667

    1245

    …

    Is there a way to make this 1,2,3,4,5,6,7,8,9..etc sequentially ?

    Thanks for your reply.

    NG

  • August 18, 2003 at 12:37 pm

    #470318

    Use the IDENTITY attribute in the ALTER/CREATE TABLE statement to specify the increment value. Look in BooksOnLine, "ALTER TABLE", or "IDENTITY" for more information. BTW, the table is not "indexed" sequentially this way unless there is a clustered primary key also on the IDENTITY field...

  • August 18, 2003 at 12:49 pm

    #470320

    Hi Jpips:

    Thanks for the reply. I do have the identity setup in my table to increment it by 1. I removed whole bunch of data in the table then I guess the index numbers left they way it was..Is there a way re-index the table sequentially ?

  • August 18, 2003 at 12:55 pm

    #470322

    AHA, now I see what you were trying to do...

    Your best bet is to just create a new table that is a duplicate of your old table, then COPY the data into the new table from the old table, leaving off the IDENTITY column in the INSERT statement, then renaming the OLD table to OldTable_bak (or something like that), and naming the new table your old table name.

    HTH,

    Jay

  • August 18, 2003 at 12:57 pm

    #470323

    One thing to watch out for, though: if you have foriegn key constraints that are dependent on the IDENTITY key, then you will have to DROP the constraints before you do this, and include a referring field in your new table so that you can tie the foreign key records to the new table using the old identity value temporarily until you can update the foriegn key values to reflect the new IDENTITY values.

  • August 18, 2003 at 1:08 pm

    #470324

    Thnaks Jay. I recreated the table. Is this how one would re-index the table when

    data is deleted.

    NG

  • August 18, 2003 at 1:25 pm

    #470325

    What you are doing here has nothing to do with indexing actually. You are simply "resetting the IDENTITY" field in the table. To ReIndex a table's indexes, which affects index statistics, storage, and fragmentation, use the DBCC command:

    

    DBCC DBREINDEX ('MyTable')

    Look in BooksOnLine for more information on DBCC DBREINDEX and DBCC INDEXDEFRAG. Good luck.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply