Reset IDs so they are sequential

[sparkbyte]

I have a primary table that had an ID field that is auto numbering and indexed and I would like to force this field to renumber all the records so that they are sequential. Is this possible?

This is a new DB that all data has been imported from multiple Access DBs so the existing IDs are invalid anyway.

The tables currently do not have any relationships that will cause problems.

It is just that during the testing and conversion of the Access Applications to Access projects added test records mess up the sequential numbering and I would like to have the IDs sequential when it goes into production.

The other option I have thought about is using a GUID data type as the ID but don't have any experience using GUIDs as an ID field and think it would limit a query like..

Select * from Activity
Where (ID In (Select Max(ID) as MaxID from Activity))

I am just not sure if the 'Max' would work correctly using GUID ID fields or if there would be another way to accomplish the same thing.

A query very similar is used as the data source for a AddNewCase form to speed it up. Without the prefilter, the form it can take 20 to 30 seconds before the form is ready for data entry because the Activity table has more than 3 mil. records for this year alone.


Thanks

John Fuhrman
faq329-6766
thread329-1334328
thread329-1424438

 

[markros]

In SQL Server you can use Identity(1,1) field. Usually this fiels is a primary key for the table. So, design your table to match the structure of the Access table, add Identity field and then you can do

insert into SQLTable (field1, field2,..., all fields except for Identity field) OpenQuery(AccessSrv,'select ... from myAccessTable')

 

[sparkbyte]

Mark that is essentially what I am doing except I am using SSIS to convert some date only and time only fields during the data transfer. The SSIS package is also multicasting the data to 2 server while I am converting the Access Application. One server is SQL2k and the other SQL2005.

I have found that using Access2002/2003 and SQL2005 causes the query designer in Access to not work so I am testing it to see if anything else has problems. So for the query designer is the only thing I have found that does not work.

In deployment that sould not cause too much of a problem since we would like to keep the users from creating/changing the queries anyway. Not too many of our uses can write SQL queries without it.

Back to the subject at hand. I was looking for a way to make the Identity field to renumber all the records without reimporting the data.

The only way I have found is to remove the identity field and then add it back. I was just hoping for an easier way.

Thanks

John Fuhrman
faq329-6766
thread329-1334328
thread329-1424438

 

[markros]

If you need to reseed identity field use DBCC CHECKIDENT (myTable, RESEED, 0)

See

http://www.howtogeek.com/howto/database/reset-identity-column-value-in-sql-server/

 

[mrdenny]

There is no benefit (as far as the SQL Server is concerned) in having the numbers be sequential or not. Leave them as they are and don't worry about it.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[JoeAtWork]

My suggestion would be a slight re-design of your Acces front-end instead of trying to change the ID field (which I assume is a primary key, which shouldn't have any significant meaning to the user).

For something like your AddNewCase form, try setting the "Data Entry" property to Yes - that should keep it from loading all 3 million records (since Data Entry means "add new records only"). If that doesn't work, a couple other alternatives are:
1. Change the query so it's where clause does not return any records
2. Make the form unbound, and write some code to construct an INSERT statement directly to the SQL server

I have found that using Access2002/2003 and SQL2005 causes the query designer in Access to not work so I am testing it to see if anything else has problems. So for the query designer is the only thing I have found that does not work.

It should work, the problem you have may be due to not including the primary key of the table(s) in the query. If any tables don't have a primary key, you need to add one and include it in the query.

 

[sparkbyte]

JoeAtWork,

You 2nd sugestion was what i had ended up doing...almost.

SELECT     ID, LoginID, ContactName, ContactPhone, ContactFax, OfficeCode, TypeOfContact, Digitized, RPCCode, ProgramName, AfileNumber, ReasonForCall, 
                      ReceivedFile, StatusOfActivity, StartTimeDate, LoginIDClosed, ClosedTimeDate, Complex, Noncomplex, Track1, Track2, Track3, FileReviewButton, 
                      AlienStatusButton, ApplStatusButton, RemovalDocsButton, PhotoOnlyButton, PrintOnlyButton, PhotosButton, PrintsButton, PhotosAndPrintsButton, 
                      NatzCertButton, AffSupportButton, BondButton, FAXButton, ccMailButton, OtherButton, phonebutton, Photos3Button, Prints3Button, UpdatedCIS, 
                      AFileCreate, BioDataButton, ResearchButton, FRCFileButton, FTFButton, ResponseMemo, LastDate, Project, Memo, Login1, Start1, End1, Login2, 
                      Start2, End2, Login3, Start3, End3, StaffingSheetToggle, SEIPrint, StartTimeOnly, StartDateOnly, ClosedTimeOnly, DateOnly
FROM         dbo.Activity
WHERE     (ID IN
                          (SELECT     MAX(ID) AS ID
                            FROM          dbo.Activity))

This way the AddNewCase only looks at the most resent record and appends the next record. I have not had a chance to test it with multiple users adding records at the same time yet though.


Thanks

John Fuhrman
faq329-6766
thread329-1334328
thread329-1424438

 

[JoeAtWork]

Do something simpler, such as:

WHERE ID = 0

It will be more efficient, especially if ID is indexed.