Solved

URGENT: Need to reindex a table

Posted on 2003-12-09
15
941 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

706 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

17 Experts available now in Live!

Get 1:1 Help Now