URGENT: Need to reindex a table

Hello,

I'm writing a conversion tool that deletes all records in all tables in the destination database (SQL Server 2000) first. It then pumps all records from the source database (Access 97) into the destination database, using SET IDENTITY INSERT ON and SET IDENTITY INSERT OFF. I do this table by table. After all records of a table have been inserted, I have to reindex the table's ID

For example: A table in my destination database has the following records
ID            Description
5              MyRecord1
6              MyRecord2
10            MyRecord3

Considering no ID's >10 have been deleted, the current new index would be 11.

After conversion, this table can have the following records:
ID           Description
1             MyConvertedRecord1
2             MyConvertedRecord2
3             MyConvertedRecord3

Now I want to reindex this table, so that the next new index (ID) would be 4.

I can't seem to manage this. The new index will remain 11. I've tried DBREINDEX, but this doesn't work. I have a lot of tables that need to be converted like this, so I don't want the conversion program to delete all indexes and create them again. It'd cost me a lot of time examining all current indexes and then writing code to drop and create them.

I can't use the SQL Server 2000 Import/Export tool to do this, because additional logic is needed in order to have a succesfull conversion.


Thanks in advance,

Luc Derckx
LVL 5
IThemaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShogunWadeCommented:
Do you mean re-seed the identity rather than re-index?

If so then this is what you want:

DBCC CHECKIDENT('MyTable',RESEED)
DBCC CHECKIDENT('MyTable')
rsrsmCommented:
DBREINDEX rebuilds an index for a table or all indexes defined for a table. By allowing an index to be rebuilt dynamically, indexes enforcing either PRIMARY KEY or UNIQUE constraints can be rebuilt without having to drop and re-create those constraints. This means an index can be rebuilt without knowing the table's structure or constraints, which could occur after a bulk copy of data into the table.


Here..re-seed will reset the index value to using the commands

DBCC CHECKIDENT
    ( 'table_name'
        [ , { NORESEED
                | { RESEED [ , new_reseed_value ] }
            }
        ]
    )

RSRSM
ShogunWadeCommented:
I think is is important to enphasise the clear distinction between an index and an identity.   They are two entirely seperate concepts!

An index is esentially a tree structure that is used for rapid retrieval of data.

and identity is a constraint used to generate unique values.

rsrsm - "Here..re-seed will reset the index value to using the commands"     This is confusing and not technically accurate as you refer to the identity column current value as the "index value".   in the example detailed above there are 3 index values   1,2 and 3   but only once identity column current value!

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Dishan FernandoSoftware Engineer / DBACommented:
drop Identity column
and
ALTER TABLE tablename ADD colname int NOT NULL IDENTITY(1,1)

RESEED doent arange the exising identity values
IThemaAuthor Commented:
I did some searching on the internet and found the checkident too.
I now use the following after inserting all data. Tell me what you think of it.

DECLARE @MAXID bigint

SET @MAXID = (SELECT MAX(IDColumn) FROM TableName)

DBCC checkident ('TableName', RESEED, @MAXID)

This seems to work.
ShogunWadeCommented:
it seems obscure as all you need to do is :

DBCC CHECKIDENT('MyTable',RESEED)
DBCC CHECKIDENT('MyTable')

this will pickup the last id automatically.
IThemaAuthor Commented:
Nope. I figured that would work too, but it doesn't...  I tried this example:
ID          Description
2            Bla
3            BlaBla
4            BlaBlaBla
5            BlaBlaBlaBla

With current identity set to 11, because I deleted the rows with ID>5

dbcc checkident ('TableName', RESEED) Resulted in:
Checking identity information: current identity value '13', current column value '13'.

Adding another record gave me ID=14
IThemaAuthor Commented:
Sorry, current Identity was 13
ShogunWadeCommented:
oops sorry a typo on my part there, thould read:


DBCC CHECKIDENT('MyTable',RESEED,1)
DBCC CHECKIDENT('MyTable')

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rsrsmCommented:
the resolution cleared most my doubts...
IThemaAuthor Commented:
Yep, that one worked. How about performance? What's better: mine (with @MAXID) or ShogunWade's?

btw: Can someone help me with this too?:
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20817300.html#9881396

I either need a way to insert values that contain single quotes using the INSERT INTO commands in addition to SET IDENTITY INSERT ON and OFF, or a way to force identities using the ADO recordset's AddNew-method (rs!IDField = MyIDValue, rs!Field2 = MyValue2, ...)

This is the last thing I need actually to complete my conversion tool.
IThemaAuthor Commented:
ps: I really need to have the single quotes being kept in the value.. I do NOT want to replace them with these: "
ShogunWadeCommented:
I use this reseed method here on tables containing over 1 billion records  In my tests it has proved substantially faster.
IThemaAuthor Commented:
Ok, I finally got it to work... all...
I replaced the single quotes with a double single quote... so
INSERT ... VALUES('Ben's Workshop') which goes wrong, will now be
INSERT ... VALUES('Ben''s Workshop') which goes well, and the string in the database will then be:
Ben's Workshop

My insertion now looks the following:

    ...
    connOBS3.Execute "SET IDENTITY_INSERT Soort ON" & vbCrLf & _
                     "INSERT INTO Soort " & _
                     "  (IDSoort, Omschrijving) " & _
                     "  VALUES (" & rsOBS2!IDSoort & ",'" & strOmschr & "')" & _
                     "SET IDENTITY_INSERT Soort OFF"
    Reseed "Soort"
    ...

Private Sub Reseed(strTable as string)
  connOBS3.Execute "DBCC CHECKIDENT('" & strTable & "', RESEED, 1)" & vbCrLf & _
                   "DBCC CHECKIDENT('" & strTable & "')"
End Sub

Thank you very much. It all made me learn a few tricks.
ShogunWadeCommented:
you are welcome
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.