Is sequential indexing possible ?

Question

Is sequential indexing possible ?

Maxme

SSC Veteran

Points: 234
More actions
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

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

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

jpipes SSChampion Points: 12230 More actions · Answer 1

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...

Maxme SSC Veteran Points: 234 More actions · Answer 2

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 ?

jpipes SSChampion Points: 12230 More actions · Answer 3

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

jpipes SSChampion Points: 12230 More actions · Answer 4

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.

Maxme SSC Veteran Points: 234 More actions · Answer 5

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

data is deleted.

NG

jpipes SSChampion Points: 12230 More actions · Answer 6

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.