Re: Renumbering MS db ID?

From: Terry Kreft (terry.kreft_at_mps.co.uk)
Date: 08/26/04 

Date: Thu, 26 Aug 2004 16:17:38 +0100

If you're using an Autonumber field or an Identity column to represent
something in the real world (such as a badge ID) then you are using it for
the wrong reasons. They should be used as an easy way to achieve a unique
key on the table, that's all. 

-- 
Terry Kreft
MVP Microsoft Access
"William Ryan eMVP" <dotnetguru@comcast.nospam.net> wrote in message
news:uu$hnM0gEHA.3548@TK2MSFTNGP09.phx.gbl...
> 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?
    ... If you're using an Autonumber field or an Identity column to represent ... 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. ... We do this currently> with our employee badge system and since the license comes in one block of ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Renumbering MS db ID?
    ... If you're using an Autonumber field or an Identity column to represent ... 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. ... We do this currently> with our employee badge system and since the license comes in one block of ...
    (microsoft.public.data.ado)
  • Re: sum with increment
    ... autonumber value ... Apparently you want to sum pairs of rows: ... an auto-numbered IDENTITY column. ... State what version of SQL Server you are using and specify the content ...
    (comp.databases.ms-sqlserver)
  • Re: Multiuser record lock query?
    ... sure why or if it makes sense in a relational database), the Identity column ... a GUID is too long. ... documentation and as I understand it is much like the autonumber feature in ...
    (microsoft.public.data.ado)
Loading