Solved

URGENT: Need to reindex a table

Posted on 2003-12-09
15
945 Views
Last Modified: 2012-08-14
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
0
Comment
Question by:IThema
  • 6
  • 6
  • 2
  • +1
15 Comments
 
LVL 18

Expert Comment

by:ShogunWade
ID: 9903269
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')
0
 

Expert Comment

by:rsrsm
ID: 9903324
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
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 9903369
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!

0
 
LVL 8

Expert Comment

by:dishanf
ID: 9903380
drop Identity column
and
ALTER TABLE tablename ADD colname int NOT NULL IDENTITY(1,1)

RESEED doent arange the exising identity values
0
 
LVL 5

Author Comment

by:IThema
ID: 9903459
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.
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 9903470
it seems obscure as all you need to do is :

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

this will pickup the last id automatically.
0
 
LVL 5

Author Comment

by:IThema
ID: 9903486
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
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 5

Author Comment

by:IThema
ID: 9903491
Sorry, current Identity was 13
0
 
LVL 18

Accepted Solution

by:
ShogunWade earned 125 total points
ID: 9903501
oops sorry a typo on my part there, thould read:


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

Expert Comment

by:rsrsm
ID: 9903507
the resolution cleared most my doubts...
0
 
LVL 5

Author Comment

by:IThema
ID: 9903542
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.
0
 
LVL 5

Author Comment

by:IThema
ID: 9903546
ps: I really need to have the single quotes being kept in the value.. I do NOT want to replace them with these: "
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 9903575
I use this reseed method here on tables containing over 1 billion records  In my tests it has proved substantially faster.
0
 
LVL 5

Author Comment

by:IThema
ID: 9903676
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.
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 9903695
you are welcome
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now