Re: Renumbering MS db ID?

From: William Ryan eMVP (dotnetguru_at_comcast.nospam.net)
Date: 08/16/04 

Date: Sun, 15 Aug 2004 23:42:49 -0400

Norman:

I'm not an Access user, but as far as the notion that 'there is no need to
care what the ID field's value is', at least with SQL Server I can think of
more than a few instances where this wouldn't be the case - it really
depends on the app requirements don't you think?. You can use for instance
TinyInt, SmallInt, BigInt, Numeric, Decimal etc. If you had a lot of
deletions and a scenario where you'd never have more than say 100 items in
a given table, you could safely specify the use of a TinyInt value and just
recycle things. If this was a pretty volatile table you could easily go
over 255 necessitating the use of another datatype. We do this currently
with our employee badge system and since the license comes in one block of
100, then 700 then 1000 - recycling the ones makes a lot of sense. I've
also seen a few implementation where they used identity fields to count
concurrent users for licensing purposes and reusing those values was used to
facilitate this counting. I'd agree it's easy enough to use int instead of
tinyint and implement a different scheme for concurrency tracking, but in
those instances it was a fairly straightfoward way to accomplish the tasks.
Similiary if your app is such that you can keep everything sequential, it
would lend itself a little better to iteration and the like. Again not a
big deal and certainly something you could do without, but not necessarily
something I'd write off entirely.
Cheers,

Bill 

-- 
W.G. Ryan MVP Windows - Embedded
Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
Let Microsoft know!
https://www.windowsembeddedeval.com/community/newsgroups
"Norman Yuan" <nobody@nowhere.no> wrote in message
news:e%23xsTxugEHA.704@TK2MSFTNGP09.phx.gbl...
> What is the "MS db"? MS Access or MS SQL Server?
>
> If I understand you correctly, you use AutoNumber (MS Access), or
> Identity(SQL Server) as ID field value. There is no need to care what the
ID
> field's value is, as long as it is unique in the table,
autonumber/identity
> guarrantees that and it is the only purpose autonumber is used for. If you
> want the ID value in particular order, you have to roll out your own logic
> to achieve that, and it is not an easy task to do.
>
> However, if you regularly delete every thing in the table and really want
> the new record's ID starting fro 1, you can compact the database after
> deleting, if you use Access DB (there is also a way to do it in SQL Server
> to re-seed the idetity field, I believe, but, why bother?).
>
> "Mr. B" <User@NoWhere.com> wrote in message
> news:1n3vh05bjp07gmapaq1rfr4u7b92l81hsr@4ax.com...
> > I have a small MS db which I update daily with new info.  I've noticed
> that
> > the ID numbers are increasing sequentially.  How can I reset them back
to
> 1
> > when I purge my data prior to adding in the new stuff?
> >
> > Here is how I'm currently deleting the info from my db (TSUpdate):
> >
> >
> >   Private Sub ClearMyData()
> >     ' Open Connection and Clear Data
> >     OleDbConnection1.Open()
> >     Dim MyCommand As New OleDbCommand("DELETE FROM TSUpdate",
> > OleDbConnection1)
> >     MyCommand.ExecuteNonQuery()
> >     OleDbConnection1.Close()
> >     MyCommand.Dispose()
> >   End Sub
> >
> >
> > Thanks in advance!
> >
> > Bruce
>
>

Relevant Pages

  • Re: Renumbering MS db ID?
    ... I'm not an Access user, but as far as the notion that 'there is no need to ... MS Access or MS SQL Server? ... There is no need to care what the ... > guarrantees that and it is the only purpose autonumber is used for. ...
    (microsoft.public.data.ado)
  • Re: Renumbering MS db ID?
    ... I'm not an Access user, but as far as the notion that 'there is no need to ... MS Access or MS SQL Server? ... There is no need to care what the ... > guarrantees that and it is the only purpose autonumber is used for. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Multi-Field Primary Key
    ... I.E. the worst arrangement is to have the autonumber alone be the ... Such a key could implemented using a UNIQUE constraint i.e. ... you are a fan of the 'CASCADE to null' feature in Jet 4.0 ). ... SQL Server will disregard all values in the referencing columns if one ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Two Tables Insert Into with Select @@ Identity
    ... If you're using SQL Server ... INSERT commands - you can't enter values into an AutoNumber column w/o ... If you're using straight JET you can use VBA to open a Recordset to ... ' Run the 2nd INSERT INTO command. ...
    (microsoft.public.access.queries)
  • Re: Two Tables Insert Into with Select @@ Identity
    ... If you're using SQL Server ... INSERT commands - you can't enter values into an AutoNumber column w/o ... ' Run the 2nd INSERT INTO command. ... if field2 was a string: ...
    (microsoft.public.access.queries)