Customer IDs without identity

I am aware of the problems with using identity. I would like a bit
more information on implementation. Concurency is a concern because
this is a net application.
Do I do this at the application level, in a trigger, or using another
method?
I want to make sure that the method I choose is robust and maintains
the integrity of the data.

>From what I know, in this case not much, it would seem that the
trigger is the best bet.
If I were to do a table that stores the unique number counter, I'm
thinking TableName and NextNumber fields could be created and then
used for all tables that need this requirement. Then my trigger
would look up the next number based on its table name, save the
number as the ID and then increment the value by 1 or whatever step
I choose. Is this thread safe?
Thanks,
GregWhat problems? You may be misinformed... There are quite a few
"Identiphobes" within the community who continue to proseletize against the
use of Identity columns on philosophical grounds which have nothing to do
with most modern database implementations... And these folks often repeat ol
d
arguments about issues which existed in SQL Server 6.5 but have been long
since corrected..
There ARE consequences of using Identities, which, when well understand, are
of minor concern. But they also have substantial performance and
maintainability advantedges, which in certain (common) scenarios can far
outweigh the negatives.
I'm not sure which category the "problems" you are referring to falls into,
but be completely informed before you make irrevocable design decisions.
And be aware that there are advocates on both sides of this issue who are no
t
completely forthright or 100% honest in the facts they use, or the manner in
which they present their arguments.|||Why not use something other than sequential values? Is this a requirement?
I would not us an identity value as my value that I share with the customer,
but I might as a surrogate key. There are a few concerns when using simple
sequential values as keys. First, odd patterns in data. If you work with
certain organizations, like I do, you would never want to have customer
0000666. Or 6660666, just because it might be wierd to the customer. And
some companies would not be keen on having customer 00000013. Second, it is
often best not to give customers keys where a value might be guessed by
someone else. Including a check digit can help with this:
0000000134 - customer 13 + check digit 4 (simple add digits up, take ones
place check digit, make yours more complicated if you do this sort of thing)

> If I were to do a table that stores the unique number counter, I'm
> thinking TableName and NextNumber fields could be created and then
> used for all tables that need this requirement. Then my trigger
> would look up the next number based on its table name, save the
> number as the ID and then increment the value by 1 or whatever step
> I choose. Is this thread safe?
The real hard part is that it is VERY thread safe. Every process that needs
a new customerID has to go to the table, insert or update the row, get the
new value, all while holding exclusive access to the table. It is best to
use a separate table if you go this way, and try to get the next customerId
as an independent operation, NOT within another transaction, or you will
find things slowing WAY down when multiple new customers are registering.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
<yzarcman@.gmail.com> wrote in message
news:1114974533.696965.29150@.f14g2000cwb.googlegroups.com...
>I am aware of the problems with using identity. I would like a bit
> more information on implementation. Concurency is a concern because
> this is a net application.
> Do I do this at the application level, in a trigger, or using another
> method?
> I want to make sure that the method I choose is robust and maintains
> the integrity of the data.
>
> trigger is the best bet.
> If I were to do a table that stores the unique number counter, I'm
> thinking TableName and NextNumber fields could be created and then
> used for all tables that need this requirement. Then my trigger
> would look up the next number based on its table name, save the
> number as the ID and then increment the value by 1 or whatever step
> I choose. Is this thread safe?
> Thanks,
> Greg
>|||Well in this case we are dealing with survey id #s and having to
convince visitors that their data is "anonymous" so I think #s are
best. We only get email addy and name but none of it is stored in the
DB
thanks,
Greg
Louis Davidson wrote:
> Why not use something other than sequential values? Is this a
requirement?
> I would not us an identity value as my value that I share with the
customer,
> but I might as a surrogate key. There are a few concerns when using
simple
> sequential values as keys. First, odd patterns in data. If you work
with
> certain organizations, like I do, you would never want to have
customer
> 0000666. Or 6660666, just because it might be wierd to the customer.
And
> some companies would not be keen on having customer 00000013.
Second, it is
> often best not to give customers keys where a value might be guessed
by
> someone else. Including a check digit can help with this:
> 0000000134 - customer 13 + check digit 4 (simple add digits up, take
ones
> place check digit, make yours more complicated if you do this sort of
thing)
>
> The real hard part is that it is VERY thread safe. Every process that
needs
> a new customerID has to go to the table, insert or update the row,
get the
> new value, all while holding exclusive access to the table. It is
best to
> use a separate table if you go this way, and try to get the next
customerId
> as an independent operation, NOT within another transaction, or you
will
> find things slowing WAY down when multiple new customers are
registering.
> --
>
----
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> <yzarcman@.gmail.com> wrote in message
> news:1114974533.696965.29150@.f14g2000cwb.googlegroups.com...
another
maintains|||>I am aware of the problems with using identity. I would like a bit
> more information on implementation. Concurency is a concern because
> this is a net application.
> Do I do this at the application level, in a trigger, or using another
> method?
> I want to make sure that the method I choose is robust and maintains
> the integrity of the data.
Identity columns are designed to deal with concurrency and are quite robust.
Neither of these reasons are valid reasons for steering away from them. This
may
have been an argument ten years ago, but is no longer
In general, you are talking about system-generated values. IMO, identity col
umns
are one of the more effective solutions. Another solution you mentioned is a
"key" table which contains a value and the table name. The procedures you us
e to
get a key would lock, query, increment and unlock this table. Essentially, t
his
solution, while more ANSI-hoyle is recreating the Identity wheel.
The argument of Identity values creating superstitious values is vacuous.
Firstly, you can add check constraints that prevent those values from happen
ing.
In the rare situation when an entity gets a superstitious value *and* someon
e
complains about it, you can always update that entity's data. The number of
scenarios where a superstitious person encounters a value that is unlucky to
their culture is so rare that it is ridiculous to design your system around
this
situation.
Another argument against Identity values is a lack of check digits. However,
check digits should be treated as a feature. If it is specifically requested
and
needed for a specific scenario it can be added after the fact.
Another argument against Identity values relates to their sequential nature
and
the ability to "predict" the next value. Firstly, remember that you are not
using Identity columns as a way of guaranteeing perfect sequence. There will
be
gaps and that's fine. The Identity column is merely being used to generate a
unique identifier. Coming back to the predictive problems, as with everythin
g,
this is a cost-benefit question. If the system matches surveys to logins, th
en
the predictive potential is somewhat moot. However, if this is not the case,
and
you need a way to ensure the lowest probability of guessing a key value and
the
key value will be visible then use a Guid or perhaps a hash value of the ema
il
address and name (although I think that expecting Email address and Name to
be
unique is dicey).
HTH
Thomas|||I have learned a lot from the posts so far.
Let me explain a situation that has occured for which I need to
address.
I have a table that uses Identity for the customer IDs, it doesn't
matter that they are predictable because numbers are often created and
never used. Identity appealed to me, but now I can't put data in the
table with assigned values.
I have a copy of the database when it was an older version. Some
customers come back and want to use their ID #s so that their data can
be trended over time. The original customers #s somehow were removed
when I used DTS to update the database. I want to put the old numbers
back in, but they will get new numbers if I do an append.
How do I get the old numbers back in and still have the Identity?
Is this a case where I should use
SET IDENTITY_INSERT [table name] ON
Insert the records
SET IDENTITY_INSERT [table name] OFF
Thanks,
Greg
Thomas Coleman wrote:
another
maintains
>
> Identity columns are designed to deal with concurrency and are quite
robust.
> Neither of these reasons are valid reasons for steering away from
them. This may
> have been an argument ten years ago, but is no longer
>
> In general, you are talking about system-generated values. IMO,
identity columns
> are one of the more effective solutions. Another solution you
mentioned is a
> "key" table which contains a value and the table name. The procedures
you use to
> get a key would lock, query, increment and unlock this table.
Essentially, this
> solution, while more ANSI-hoyle is recreating the Identity wheel.
>
> The argument of Identity values creating superstitious values is
vacuous.
> Firstly, you can add check constraints that prevent those values from
happening.
> In the rare situation when an entity gets a superstitious value *and*
someone
> complains about it, you can always update that entity's data. The
number of
> scenarios where a superstitious person encounters a value that is
unlucky to
> their culture is so rare that it is ridiculous to design your system
around this
> situation.
>
> Another argument against Identity values is a lack of check digits.
However,
> check digits should be treated as a feature. If it is specifically
requested and
> needed for a specific scenario it can be added after the fact.
>
> Another argument against Identity values relates to their sequential
nature and
> the ability to "predict" the next value. Firstly, remember that you
are not
> using Identity columns as a way of guaranteeing perfect sequence.
There will be
> gaps and that's fine. The Identity column is merely being used to
generate a
> unique identifier. Coming back to the predictive problems, as with
everything,
> this is a cost-benefit question. If the system matches surveys to
logins, then
> the predictive potential is somewhat moot. However, if this is not
the case, and
> you need a way to ensure the lowest probability of guessing a key
value and the
> key value will be visible then use a Guid or perhaps a hash value of
the email
> address and name (although I think that expecting Email address and
Name to be
> unique is dicey).
>
>
> HTH
>
>
> Thomas|||Ah...that's a horse of a different color. Yes, identity insert is the tool y
ou
want. Of course, if there are duplicates, identity insert will not help you.
Thomas
<yzarcman@.gmail.com> wrote in message
news:1114987674.157743.201990@.o13g2000cwo.googlegroups.com...
>I have learned a lot from the posts so far.
> Let me explain a situation that has occured for which I need to
> address.
> I have a table that uses Identity for the customer IDs, it doesn't
> matter that they are predictable because numbers are often created and
> never used. Identity appealed to me, but now I can't put data in the
> table with assigned values.
> I have a copy of the database when it was an older version. Some
> customers come back and want to use their ID #s so that their data can
> be trended over time. The original customers #s somehow were removed
> when I used DTS to update the database. I want to put the old numbers
> back in, but they will get new numbers if I do an append.
> How do I get the old numbers back in and still have the Identity?
> Is this a case where I should use
> SET IDENTITY_INSERT [table name] ON
> Insert the records
> SET IDENTITY_INSERT [table name] OFF
> Thanks,
> Greg
> Thomas Coleman wrote:
> another
> maintains
> robust.
> them. This may
> identity columns
> mentioned is a
> you use to
> Essentially, this
> vacuous.
> happening.
> someone
> number of
> unlucky to
> around this
> However,
> requested and
> nature and
> are not
> There will be
> generate a
> everything,
> logins, then
> the case, and
> value and the
> the email
> Name to be
>|||This is easy, add a new column for the old number, but make it nullable...
Then, anytime you get a situation like what you describe, (Old customer want
s
to be added back in, first, determine if their record is still there, and if
so, update the OldID column with their Old ID Number. If they're not in
there, insert a new record, with the Old ID Number and a new Identit value.
The table then contains it's own mapping from OldID to new Identity ID for
those rows that have both values, and you can still use the new Identity
value for all other data processing.
"yzarcman@.gmail.com" wrote:

> I have learned a lot from the posts so far.
> Let me explain a situation that has occured for which I need to
> address.
> I have a table that uses Identity for the customer IDs, it doesn't
> matter that they are predictable because numbers are often created and
> never used. Identity appealed to me, but now I can't put data in the
> table with assigned values.
> I have a copy of the database when it was an older version. Some
> customers come back and want to use their ID #s so that their data can
> be trended over time. The original customers #s somehow were removed
> when I used DTS to update the database. I want to put the old numbers
> back in, but they will get new numbers if I do an append.
> How do I get the old numbers back in and still have the Identity?
> Is this a case where I should use
> SET IDENTITY_INSERT [table name] ON
> Insert the records
> SET IDENTITY_INSERT [table name] OFF
> Thanks,
> Greg
> Thomas Coleman wrote:
> another
> maintains
> robust.
> them. This may
> identity columns
> mentioned is a
> you use to
> Essentially, this
> vacuous.
> happening.
> someone
> number of
> unlucky to
> around this
> However,
> requested and
> nature and
> are not
> There will be
> generate a
> everything,
> logins, then
> the case, and
> value and the
> the email
> Name to be
>|||You would be better off NOT to use Identity Insert, because,
A) An old client, with Old ID, may have an OLD ID Value tha is the same as
an existing new CLient...
B) Or the client may already be in the database with a different New
Identity ID value. Then you would have to cascade update the value
everywhere else it was used as a foreign key...
"yzarcman@.gmail.com" wrote:

> I have learned a lot from the posts so far.
> Let me explain a situation that has occured for which I need to
> address.
> I have a table that uses Identity for the customer IDs, it doesn't
> matter that they are predictable because numbers are often created and
> never used. Identity appealed to me, but now I can't put data in the
> table with assigned values.
> I have a copy of the database when it was an older version. Some
> customers come back and want to use their ID #s so that their data can
> be trended over time. The original customers #s somehow were removed
> when I used DTS to update the database. I want to put the old numbers
> back in, but they will get new numbers if I do an append.
> How do I get the old numbers back in and still have the Identity?
> Is this a case where I should use
> SET IDENTITY_INSERT [table name] ON
> Insert the records
> SET IDENTITY_INSERT [table name] OFF
> Thanks,
> Greg
> Thomas Coleman wrote:
> another
> maintains
> robust.
> them. This may
> identity columns
> mentioned is a
> you use to
> Essentially, this
> vacuous.
> happening.
> someone
> number of
> unlucky to
> around this
> However,
> requested and
> nature and
> are not
> There will be
> generate a
> everything,
> logins, then
> the case, and
> value and the
> the email
> Name to be
>