File Group Changes: Getting list of recently added IDENTITY items

In a multi-user environment, I would like to get a list of Ids
generated, similar to:

declare @.LastId int
select @.LastId = Max(Id) From TableMania

INSERT INTO TableMania (ColumnA, ColumnB)
SELECT ColumnA, ColumnB From OtherTable Where ColumnC > 15

--get entries just added
SELECT * FROM TableMania WHERE Id > @.LastId

The above works fine, except I'm assuming it will not work in a
multi-user environment. Is there any way to get the set of Ids that
were just added in the previous statement (similar to @.@.IDENTITY)
without doing all of this in a serializable transaction or making a
temp table of every single Id before the insert statement?pb648174 (google@.webpaul.net) writes:
> In a multi-user environment, I would like to get a list of Ids
> generated, similar to:
> declare @.LastId int
> select @.LastId = Max(Id) From TableMania
> INSERT INTO TableMania (ColumnA, ColumnB)
> SELECT ColumnA, ColumnB From OtherTable Where ColumnC > 15
> --get entries just added
> SELECT * FROM TableMania WHERE Id > @.LastId
>
> The above works fine, except I'm assuming it will not work in a
> multi-user environment. Is there any way to get the set of Ids that
> were just added in the previous statement (similar to @.@.IDENTITY)
> without doing all of this in a serializable transaction or making a
> temp table of every single Id before the insert statement?

Actually, I don't know.

Say that you insert a couple of rows into a table with a column ident
that has the IDENTITY property. @.@.identity or scope_identity gives
you the highest value for ident for the lnserted rows. But is the
lowest value @.@.identity - @.@.rowcount + 1?

I have never seen any documentation that guarantees this to be true.
It is likely to be, but what if you insert 10000 rows, and in the
middle of this another users needs to insert a single row. Will he
steal a value?

One strategy would be to retrieve ident_current() before the insertion (or
MAX(ident), and then after the insertion check that the interval is equal
to @.@.rowcount, and bail out if it's not.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||sure.
get a timestamp, including milliseconds from the local workstation.
insert that timestamp into a column.
then you can group by timestamp.|||I guess I will just go with a serializable transaction for now...|||pb648174 wrote:
> In a multi-user environment, I would like to get a list of Ids
> generated, similar to:
> declare @.LastId int
> select @.LastId = Max(Id) From TableMania
> INSERT INTO TableMania (ColumnA, ColumnB)
> SELECT ColumnA, ColumnB From OtherTable Where ColumnC > 15
> --get entries just added
> SELECT * FROM TableMania WHERE Id > @.LastId
>
> The above works fine, except I'm assuming it will not work in a
> multi-user environment. Is there any way to get the set of Ids that
> were just added in the previous statement (similar to @.@.IDENTITY)
> without doing all of this in a serializable transaction or making a
> temp table of every single Id before the insert statement?

This is easy to solve provided you have an alternate key. IDENTITY
should not be the only key of a table and this is one example of why -
without an alternate key you have no entity integrity so you cannot
always guarantee reliable results from the data.

Try the following example. Notice that the reason this works is that
the INSERT list always must include the primary key when you are
inserting multiple rows (otherwise there is no key). The only potential
exception is where you assign a default value that forms part of the
key - for example DEFAULT CURRENT_TIMESTAMP. In that case you need to
retrieve the default value before you do the INSERT so that you can use
it in the SELECT.

CREATE TABLE tablemania (id INT IDENTITY PRIMARY KEY, a INT, b INT,
UNIQUE (a,b));
CREATE TABLE othertable (a INT, b INT, c INT, PRIMARY KEY (a,b,c));

INSERT INTO othertable (a,b,c)
SELECT 1,2,16 UNION ALL
SELECT 1,3,16 UNION ALL
SELECT 1,4,16 UNION ALL
SELECT 1,5,16 ;

DECLARE @.t TABLE (a INT, b INT, PRIMARY KEY (a,b));

INSERT INTO @.t (a, b)
SELECT a, b
FROM othertable
WHERE c > 15 ;

INSERT INTO tablemania (a, b)
SELECT a, b
FROM @.t ;

SELECT T.id, T.a, T.b
FROM tablemania AS T
JOIN @.t AS O
ON T.a = O.a
AND T.b = O.b ;

In SQL Server 2005 you have a more concise alternative. Use the OUTPUT
option:

INSERT INTO tablemania (a, b)
OUTPUT inserted.id, inserted.a, inserted.b
SELECT a, b
FROM othertable
WHERE c > 15 ;

Hope this helps.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Erland Sommarskog wrote:
> pb648174 (google@.webpaul.net) writes:
> > In a multi-user environment, I would like to get a list of Ids
> > generated, similar to:
> > declare @.LastId int
> > select @.LastId = Max(Id) From TableMania
> > INSERT INTO TableMania (ColumnA, ColumnB)
> > SELECT ColumnA, ColumnB From OtherTable Where ColumnC > 15
> > --get entries just added
> > SELECT * FROM TableMania WHERE Id > @.LastId
> > The above works fine, except I'm assuming it will not work in a
> > multi-user environment. Is there any way to get the set of Ids that
> > were just added in the previous statement (similar to @.@.IDENTITY)
> > without doing all of this in a serializable transaction or making a
> > temp table of every single Id before the insert statement?
> Actually, I don't know.
> Say that you insert a couple of rows into a table with a column ident
> that has the IDENTITY property. @.@.identity or scope_identity gives
> you the highest value for ident for the lnserted rows. But is the
> lowest value @.@.identity - @.@.rowcount + 1?
> I have never seen any documentation that guarantees this to be true.
> It is likely to be, but what if you insert 10000 rows, and in the
> middle of this another users needs to insert a single row. Will he
> steal a value?

Tibor posted a repro that demonstrates the values are not always
contiguous. Also there is a related problem with IGNORE_DUP_KEY, which
causes gaps if rows are ignored.
http://groups.google.co.uk/group/mi...75cbb8f978decc9

> One strategy would be to retrieve ident_current() before the insertion (or
> MAX(ident), and then after the insertion check that the interval is equal
> to @.@.rowcount, and bail out if it's not.

See my solutions in this thread.

--
David Portas, SQL Server MVP

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> Tibor posted a repro that demonstrates the values are not always
> contiguous. Also there is a related problem with IGNORE_DUP_KEY, which
> causes gaps if rows are ignored.
http://groups.google.co.uk/group/mi...rogramming/msg/
375cbb8f978decc9

Ah, that was interesting!

> This is easy to solve provided you have an alternate key. IDENTITY
> should not be the only key of a table and this is one example of why -
> without an alternate key you have no entity integrity so you cannot
> always guarantee reliable results from the data.

That is about as useful as saying "this is a good car, but you should
not drive it at night". If databases would only include data that
have natural keys, there wouldn't be much data in them.

What I didn't say in my first post, is that my take on this is usually
to not have IDENTITY on my surrogate key, but instead bounce data over
a temp table with IDENTITY, and then add that to a SELECT MAX(id)
from the target table. This comes with scaling problems obviously, but
that has not been an issue for me, luckily.

> In SQL Server 2005 you have a more concise alternative. Use the OUTPUT
> option:
> INSERT INTO tablemania (a, b)
> OUTPUT inserted.id, inserted.a, inserted.b
> SELECT a, b
> FROM othertable
> WHERE c > 15 ;

I will have to admit that I not really seen the point with the OUTPUT
clause for INSERT, but this an excellent use for it!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:
> That is about as useful as saying "this is a good car, but you should
> not drive it at night". If databases would only include data that
> have natural keys, there wouldn't be much data in them.

You said "data" so you could be right. If you'd said "information"
you'd be wrong. Here's an analogy. If I have two copies of "Inside SQL
Server" on my bookshelf do I have more information than if I have one
copy of that book? Now if I write 1 on the cover of the first book and
2 on the cover of the second, do I have any more information? So should
I spend money and storage space on two books or one? The smart money is
invested in information not in data.

--
David Portas, SQL Server MVP

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> You said "data" so you could be right. If you'd said "information"
> you'd be wrong. Here's an analogy. If I have two copies of "Inside SQL
> Server" on my bookshelf do I have more information than if I have one
> copy of that book? Now if I write 1 on the cover of the first book and
> 2 on the cover of the second, do I have any more information? So should
> I spend money and storage space on two books or one? The smart money is
> invested in information not in data.

Then again, you could have scribbled notes in one of the copies, and
the other could have Kalen's highly valuable autograph.

More importantly, there is data - or information - out there that
users want to - and need to - deal with, despite that we cannot define
a unique key for them. You already know the prime example too well:
customers.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

In that case they wouldn't be duplicates any more. To complete the
analogy, you now have a natural key. (Alternatively you might want to
decompose the notes and the signature into separate tables)

> More importantly, there is data - or information - out there that
> users want to - and need to - deal with, despite that we cannot define
> a unique key for them. You already know the prime example too well:
> customers.

Users are concerned with information. Data (how the information is
represented) is primarily the concern of database professionals. As a
database designer you have a choice because the same information can
always be modelled with natural keys or without. You can argue that the
developer may lack the time, the resources or the authority to redesign
his database. He may even be unable to analyse his business problem
well enough to identify a suitable key with a high degree of
confidence. However, those constraints are not problems we can solve in
a newsgroup. They are project management problems rather than technical
ones.

The technical solution to the OP's problem is simple: elminate
redundancy. It is always possible to eliminate redundancy as a
consequence of the simple fact that duplicate rows cannot contain more
information than a single row. That applies equally whether the row
represents customers or books or anything else. I suggest we let the OP
to decide if he has the will or the resources to implement the solution
in his case.

--
David Portas, SQL Server MVP

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> Users are concerned with information. Data (how the information is
> represented) is primarily the concern of database professionals. As a
> database designer you have a choice because the same information can
> always be modelled with natural keys or without. You can argue that the
> developer may lack the time, the resources or the authority to redesign
> his database. He may even be unable to analyse his business problem
> well enough to identify a suitable key with a high degree of
> confidence. However, those constraints are not problems we can solve in
> a newsgroup. They are project management problems rather than technical
> ones.

Thus, it is not the right thing to say in a newsgroup that you should
not use IDENTITY. Maybe it was a poor database design. Maybe there were
time constraints in the project. Whatever, it's not our business.

> The technical solution to the OP's problem is simple: elminate
> redundancy. It is always possible to eliminate redundancy as a
> consequence of the simple fact that duplicate rows cannot contain more
> information than a single row. That applies equally whether the row
> represents customers or books or anything else. I suggest we let the OP
> to decide if he has the will or the resources to implement the solution
> in his case.

Say that you are importing a file from some less good source. The file may
contain lines that are true duplicates. Still we way want to import that
file in whole, if nothing else because we think SQL is the best tool to
find the duplicates. You still need some way to identify the lines. This
can be achieved in several ways, whereof IDENTITY is one.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

I didn't say don't use IDENTITY. I said it shouldn't be the only key.
That's good advice in any situation as I think the OP's problem
demonstrates. IDENTITY is still useful as a surrogate key and I'm not
against that.

> Say that you are importing a file from some less good source. The file may
> contain lines that are true duplicates. Still we way want to import that
> file in whole, if nothing else because we think SQL is the best tool to
> find the duplicates. You still need some way to identify the lines. This
> can be achieved in several ways, whereof IDENTITY is one.

That's true. It is possible to solve this in an integration layer
before the data reaches the database however. For obvious reasons a
staging table without a natural key isn't a good place to do any
processing other than data cleansing. In the example posted in this
thread the data is being inserted from another SQL table so there's no
reason to suppose there shouldn't be a candidate key. Of course DDL
would have helped.

--
David Portas, SQL Server MVP

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
>> Thus, it is not the right thing to say in a newsgroup that you should
>> not use IDENTITY. Maybe it was a poor database design. Maybe there were
>> time constraints in the project. Whatever, it's not our business.
> I didn't say don't use IDENTITY. I said it shouldn't be the only key.
> That's good advice in any situation as I think the OP's problem
> demonstrates. IDENTITY is still useful as a surrogate key and I'm not
> against that.

Whether you say "don't use IDENTITY" or "don't use IDENTITY without a
candidate key" does not matter as long as you have no knowledge of the
business domain.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Lets say I give you one dollar bill. Now I give you another dollar
bill. Do you immediately throw away one of them as being redundent?

Another example. "Existence" is valid data, and valid information. The
simple fact that I got a website hit is important. If I get two website
hits at the exact same time, I may still need to track that website
hit, especially if I get paid by the website hit. There may be no
unique natural key, but it is still awfully nice to have a way to
manipulate them individually if need be, and have "record" they came
in.

Finally, I don't know of any requirement that identity needs to be
unique. In fact, I think it is pretty much written somewhere in the BOL
they don't have to be, ESPECIALLY if a transaction is rolled back, or a
row is deleted.
Sequential is inconsequential. Even bad code shouldn't count on
sequential identities.

Regards,
doug miller
mba|||Your whoel mental model of RDBMS is wrong and it is about to kludge you
to death.

When you look for a key, start with your industry standards. I have
been in IT for a bit over 35 years, and would estimate that this covers
~80% of the properly defined systems -- VIN, ISBN, UPC, EAN, etc. which
can be verified and validated via a trusted exernal source. In the ode
days, this took time; you can Google it today.

After that, there is a natural key in the data, such as (longitude,
latitude), (store, cash register, ticket_nbr), etc. which can be
verified and validated in the reality of the data. This is ~18% of the
cases. if you have no natural key, then your schema is probably wrong.

Then, if that fails we invent a key with check digits, validation rules
and an audit trail within our enterprise. This is a great screaming
pain IF you do it right. This is why industry standards exists --
people got tired the pain (would you like to do Retail without UPC
barcodes on products?)

So, newbies prefer to do it wrong by using auto-increments or other
proprietary stuff that cannot be verified or validated BECAUSE it is
easier than real RDBMS design. It takes SIX years to become a Union
Journeyman carpenter in New York State, but newbies can be a DB
designer in less than 10 years. Hell, less than 10 months! WOW!

I know you want the quick easy answer that does not require you
ACTUALLY UNDERSTAND the business problem or your problem domain. Just
make up something in the software and hope you never write for a
company that has a SOX audit or a Data Warehouse project.

What you posted is just enough to get a lot of Kludges but not a real
answer.|||Doug (drmiller100@.hotmail.com) writes:
> Finally, I don't know of any requirement that identity needs to be
> unique. In fact, I think it is pretty much written somewhere in the BOL
> they don't have to be, ESPECIALLY if a transaction is rolled back, or a
> row is deleted.

IDENTITY itself does not guarantee uniqueness. However, the only way
an the same IDENTITY value could be generated twice for the same table,
is if you play with the RESED option of DBCC CHECKIDENT.

In case of a rollback, the identity value that was used is "lost"; the
sequnce is not rolled back.

Usually, you have a PRIMARY KEY or UNIQUE constraint on IDENTITY columns,
so it's not much of an issue anyway.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||(comments inline)

Doug wrote:
> Let me try two examples.
> Lets say I give you one dollar bill. Now I give you another dollar
> bill. Do you immediately throw away one of them as being redundent?

I don't see what relevance that has to databases.

> Another example. "Existence" is valid data, and valid information.

Agreed.

> The
> simple fact that I got a website hit is important. If I get two website
> hits at the exact same time, I may still need to track that website
> hit, especially if I get paid by the website hit. There may be no
> unique natural key, but it is still awfully nice to have a way to
> manipulate them individually if need be, and have "record" they came
> in.

So couldn't the (simplified) table look like this:

CREATE TABLE hits (hit_datetime DATETIME ip_address VARCHAR(15),
num_of_hits INTEGER NOT NULL, PRIMARY KEY (hit_datetime, ip_address));

It isn't obvious what you mean by "manipulate them individually if need
be". You are saying you want to see a row per hit? Why?

> Sequential is inconsequential. Even bad code shouldn't count on
> sequential identities.

I agree.

--
David Portas, SQL Server MVP

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> So couldn't the (simplified) table look like this:
> CREATE TABLE hits (hit_datetime DATETIME ip_address VARCHAR(15),
> num_of_hits INTEGER NOT NULL, PRIMARY KEY (hit_datetime, ip_address));

While unlikely, it cannot be excluded that you have two hits from the
same IP-address within 3 ms.

This is a typical case where you need something like IDENTITY. Using
a datetime value is just a gamble. Using a surrogate key which is
assigned for each row is safe.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

If you want to record more than 3ms precision then you'll have to use a
non-DATETIME datatype. IDENTITY won't help you do that. If you don't
need that level of precision then you can just increment the
num_of_hits for each hit within the same 3ms timeframe. Again, IDENTITY
doesn't help you. Either way my design is perfectly sound.

Unfortunately these types examples just tend to become rather tedious
"what if this..." and "what if that..." exchanges. As a result they
aren't very informative. Fundamentally, my observation is simply this:
that there is no information in duplicate data that cannot also be
modelled in relational form (i.e. with keys). Put another way: stating
the same fact twice doesn't convey any more information than stating it
once. This is one of the foundations of the relational model and its
basis in predicate logic.

--
David Portas, SQL Server MVP

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> If you want to record more than 3ms precision then you'll have to use a
> non-DATETIME datatype. IDENTITY won't help you do that. If you don't
> need that level of precision then you can just increment the
> num_of_hits for each hit within the same 3ms timeframe. Again, IDENTITY
> doesn't help you. Either way my design is perfectly sound.

No, it isn't. Using no_of_hits is a kludge, and also a performance problem,
because instead of a plain insert, you need to do an IF EXISTS and then
INSERT or UPDATE. Similarly, if you want count the number of hits, you
need to sum no_of_hits, rather than doing count(*) which is likely to
give the optimizer fewer choices for an effective query plan.

The problem is fairly apparent here, because SQL Server has a fairly
low resolution on time. But the problem is not the resolution, the
problem is that time is a contiguous entity that does not have discreet
values. Using time for as a primary key has the same problem as using a
floating-point as a primary key.

On the other hand, IDENTITY is a discrete set of value that is easy
to work with. After all, that is all we know. This was hit 56762 that
was registered. It happens to have the same values in the database as
hit 56763, but we don't know if the values they model were the same or
not. Thus, it would be incorrect to handle them as being the same. The
only thing we can to discern them, is to add a number to the observations,
so that we know that they are distinct.

> Unfortunately these types examples just tend to become rather tedious
> "what if this..." and "what if that..." exchanges. As a result they
> aren't very informative. Fundamentally, my observation is simply this:
> that there is no information in duplicate data that cannot also be
> modelled in relational form (i.e. with keys).

Yes, these exchanges are tedious, when someone insists on putting the cart
before the horse, and tries to press a circular reality into a squared
model.

no_of_hits here is perfect example of this. It adds no information to
the data, but only serves to make the data more difficult to work with.
Certainly, it makes the relational purists sleep better at night, but
that is usually not what the customer is paying for.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:
> David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> > If you want to record more than 3ms precision then you'll have to use a
> > non-DATETIME datatype. IDENTITY won't help you do that. If you don't
> > need that level of precision then you can just increment the
> > num_of_hits for each hit within the same 3ms timeframe. Again, IDENTITY
> > doesn't help you. Either way my design is perfectly sound.
> No, it isn't. Using no_of_hits is a kludge, and also a performance problem,
> because instead of a plain insert, you need to do an IF EXISTS and then
> INSERT or UPDATE. Similarly, if you want count the number of hits, you
> need to sum no_of_hits, rather than doing count(*) which is likely to
> give the optimizer fewer choices for an effective query plan.
> The problem is fairly apparent here, because SQL Server has a fairly
> low resolution on time. But the problem is not the resolution, the
> problem is that time is a contiguous entity that does not have discreet
> values. Using time for as a primary key has the same problem as using a
> floating-point as a primary key.
> On the other hand, IDENTITY is a discrete set of value that is easy
> to work with. After all, that is all we know. This was hit 56762 that
> was registered. It happens to have the same values in the database as
> hit 56763, but we don't know if the values they model were the same or
> not. Thus, it would be incorrect to handle them as being the same. The
> only thing we can to discern them, is to add a number to the observations,
> so that we know that they are distinct.
> > Unfortunately these types examples just tend to become rather tedious
> > "what if this..." and "what if that..." exchanges. As a result they
> > aren't very informative. Fundamentally, my observation is simply this:
> > that there is no information in duplicate data that cannot also be
> > modelled in relational form (i.e. with keys).
> Yes, these exchanges are tedious, when someone insists on putting the cart
> before the horse, and tries to press a circular reality into a squared
> model.
> no_of_hits here is perfect example of this. It adds no information to
> the data, but only serves to make the data more difficult to work with.
> Certainly, it makes the relational purists sleep better at night, but
> that is usually not what the customer is paying for.

Now here is a more interesting slant on the key problem. When does a
metric become a kludge? I don't model account balances or stock as one
row per dollar or one row per pack - and I'm sure you don't either. We
use values in columns for those things. No_of_hits is surely a value
that is likely to be of interest to our users - in fact you've already
suggested aggregating the rows to get that value. So when does it
become "right" to put it in a column and "wrong" to create N rows
instead of just 1 row for the same data?

You seen to suggest that performance should be the deciding factor. My
view is that Normal Form is a better criteria. In any event, the
designer must make that choice and live with the consequences. In my
opinion it does no harm to point out yet again that the choice exists.
On the other hand it does a lot of harm to perpetuate the transparently
silly idea that "There isn't a natural key".

--
David Portas, SQL Server MVP

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||As far as DDL, just assume a simple lookup table, Id and Description.

The main reason I use identities is for ease of use. Sometimes I use
natural keys, when they make sense and are a fairly small number of
columns, but other times we have tables that are basically a lookup
table, Id and Description. I suppose I could make the 500 character
description the primary key and only have a single column, but there
QueryString length limitations to deal with, index performance and
other things to worry about with having a large piece of text as a
primary key. I also don't like the idea of having that description data
duplicated when used as a foreign key for all the other tables that
relate to it. If that is just a personal preference and not an actual
problem, I don't know, but as Donald Rumsfeld says, you go to war with
the army you have.

Sure, we could re-architect the solution to make Celko and others treat
us nicely, but that would be hard to sell to the customers - 1 yr delay
to rewrite the software so purists are happy in the discussion forums.
As of now a serializable transaction seems to be the best way to handle
this issue in my mind using my original posted code since we can't also
force our customer to spend 25K to upgrade their SQL servers.|||"no_of_hits here is perfect example of this. It adds no information to
the data, but only serves to make the data more difficult to work with.
Certainly, it makes the relational purists sleep better at night, but
that is usually not what the customer is paying for.
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se "

Well phrased. I am sure the SQL ANSI spec does not require an identity
type. However, it is SUCH a common and useful tool that any practicing
DBA or developer is going ot use them.
As an example, the journeyman carpenter specs require green sinker
nails. Purists use a claw hammer to drive them in.

Guys that want to make money at the business use nail guns.|||"You seen to suggest that performance should be the deciding factor. My
view is that Normal Form is a better criteria. In any event, the "

Real world stuff. I've played some fairly significant games violating
all sorts of normalization because the "right" way wasn't fast enough.
Early on I tried to teach the customer that it was in their best long
term interests to suffer 3 second response times. The danged customers
kept saying "FIX IT."

I've built systems to create and guarantee unique keys. It is a giant
PITA as you have to learn specificities about the particular engine in
place to figure out how unique keys could be created. As an example, i
read about 3 milliseconds on this forum. I didn't know that, and, it
turns out I don't really care.

For the past 15 years or so, I try to find the EASIEST way to get
guaranteed unique keys from the engine. And the engines of today all
seem to have them, just like most modern cars have parking brakes, and
I don't need to carry a large rock around any more.

regards,
doug|||pb648174 wrote:
> As far as DDL, just assume a simple lookup table, Id and Description.
> The main reason I use identities is for ease of use. Sometimes I use
> natural keys, when they make sense and are a fairly small number of
> columns, but other times we have tables that are basically a lookup
> table, Id and Description. I suppose I could make the 500 character
> description the primary key and only have a single column, but there
> QueryString length limitations to deal with, index performance and
> other things to worry about with having a large piece of text as a
> primary key. I also don't like the idea of having that description data
> duplicated when used as a foreign key for all the other tables that
> relate to it. If that is just a personal preference and not an actual
> problem, I don't know, but as Donald Rumsfeld says, you go to war with
> the army you have.
> Sure, we could re-architect the solution to make Celko and others treat
> us nicely, but that would be hard to sell to the customers - 1 yr delay
> to rewrite the software so purists are happy in the discussion forums.
> As of now a serializable transaction seems to be the best way to handle
> this issue in my mind using my original posted code since we can't also
> force our customer to spend 25K to upgrade their SQL servers.

Continue to use IDENTITY. Just declare the other key as well. Does my
solution work for your original problem?

--
David Portas, SQL Server MVP

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> Now here is a more interesting slant on the key problem. When does a
> metric become a kludge?

When the kludge is not a metric.

Of course, two hits from the same IP address in the same ms is a little
funny - but so are two hits 10 ms apart, but your model does not account
for that. No_of_hits is just a mechanism you've added to solve the
problem with collisions on a key values that represents a contiguous
spectrum.

Take another example. Some process in laboratory or whereever generates
lots of measurements data, in rates of microseconds. We don't use datetime
to store the value, obviously, but would it be right to assume discrete
steps of microseconds? Maybe, but what if the frequency is somewhat
uneven?. We can get two values registered for the same millisecond, but
we know that they are nevertheless apart. And here we are not talking
number of hits, but some value - presumably floating-point.

What I am saying is that you cannot use en entity that is a continuous
spectrum as a key. Key values must be discrete. Of, course, in a
digital computer, everything is discrete - but that only means that
two analogue values can get the same representation.

And this is just one example where the real world does not have that
fine key the relational model wants. Again, think customers.

> You seen to suggest that performance should be the deciding factor.

Not really, as you can see above. However, for registering hits on a
busy web site, performance is probably argument enough to kill that
model.

> On the other hand it does a lot of harm to perpetuate the transparently
> silly idea that "There isn't a natural key".

It isn't silly. It's fact of life that in many cases. Common cases.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland,

Can you post your definition of a "natural key"?

>> And this is just one example where the real world does not have that fine
>> key the relational model wants.

However, real world examples of entities, by virtue of there mere existance,
have keys regardless of what relational model mandates.

>> Again, think customers.

What about them?

--
Anith|||Erland Sommarskog wrote:
> David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> > Now here is a more interesting slant on the key problem. When does a
> > metric become a kludge?
> When the kludge is not a metric.
> Of course, two hits from the same IP address in the same ms is a little
> funny - but so are two hits 10 ms apart, but your model does not account
> for that. No_of_hits is just a mechanism you've added to solve the
> problem with collisions on a key values that represents a contiguous
> spectrum.
> Take another example. Some process in laboratory or whereever generates
> lots of measurements data, in rates of microseconds. We don't use datetime
> to store the value, obviously, but would it be right to assume discrete
> steps of microseconds? Maybe, but what if the frequency is somewhat
> uneven?. We can get two values registered for the same millisecond, but
> we know that they are nevertheless apart. And here we are not talking
> number of hits, but some value - presumably floating-point.
> What I am saying is that you cannot use en entity that is a continuous
> spectrum as a key. Key values must be discrete. Of, course, in a
> digital computer, everything is discrete - but that only means that
> two analogue values can get the same representation.

See Kimball on slowly changing dimensions. Or Date, Darwen and
Lorentzos on Temporal Data and The Relational Model. Time-variant data
is commonplace and all the techniques I know of expect the time
dimension to be part of the key. Kimball actually asserts that we
should choose fixed, granular periods for facts - although I don't
think he develops a proper argument to support that. So this is
interesting but given that we have widely implemented and proven
industry standard solutions for these problems I don't think you should
dismiss them without hard evidence that you can better them.

> And this is just one example where the real world does not have that
> fine key the relational model wants. Again, think customers.

Been there. Done that.

--
David Portas, SQL Server MVP

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Just out of curiosity, lets say we are told that only a basic
description of something is needed, let's say a list of projects that a
user is going to work on, that needs to be 500 characters long. What
should the primary key be? Is it the 500 character description or an
identity column? Are there performance issues with having 500 character
foreign keys, indexes, etc.? In what ways is it going to be better than
using an identity column taking into account that it is going to be
passed through the QueryString, posted in links, etc.?

Doug, I think your solution would mostly work, but be a pain to
implement when there are lots of columns. I think perhaps a more
general way to do it would be to store only the primary key of the
copied data (identity or not) and use that to know which records were
copied. As long as the amount of copied data is fairly small I don't
see that there would be a very big performance impact for that. I think
I might just do that to avoid needing the serializable transaction.

--CELKO-- wrote:
> Your whoel mental model of RDBMS is wrong and it is about to kludge you
> to death.
> When you look for a key, start with your industry standards. I have
> been in IT for a bit over 35 years, and would estimate that this covers
> ~80% of the properly defined systems -- VIN, ISBN, UPC, EAN, etc. which
> can be verified and validated via a trusted exernal source. In the ode
> days, this took time; you can Google it today.
> After that, there is a natural key in the data, such as (longitude,
> latitude), (store, cash register, ticket_nbr), etc. which can be
> verified and validated in the reality of the data. This is ~18% of the
> cases. if you have no natural key, then your schema is probably wrong.
>
> Then, if that fails we invent a key with check digits, validation rules
> and an audit trail within our enterprise. This is a great screaming
> pain IF you do it right. This is why industry standards exists --
> people got tired the pain (would you like to do Retail without UPC
> barcodes on products?)
> So, newbies prefer to do it wrong by using auto-increments or other
> proprietary stuff that cannot be verified or validated BECAUSE it is
> easier than real RDBMS design. It takes SIX years to become a Union
> Journeyman carpenter in New York State, but newbies can be a DB
> designer in less than 10 years. Hell, less than 10 months! WOW!
> I know you want the quick easy answer that does not require you
> ACTUALLY UNDERSTAND the business problem or your problem domain. Just
> make up something in the software and hope you never write for a
> company that has a SOX audit or a Data Warehouse project.
> What you posted is just enough to get a lot of Kludges but not a real
> answer.|||Just out of curiosity, lets say we are told that only a basic
description of something is needed, let's say a list of projects that a

user is going to work on, that needs to be 500 characters long. What
should the primary key be? Is it the 500 character description or an
identity column? Are there performance issues with having 500 character

foreign keys, indexes, etc.? In what ways is it going to be better than

using an identity column taking into account that it is going to be
passed through the QueryString, posted in links, etc.?|||On 15 Mar 2006 08:51:40 -0800, pb648174 wrote:

>Just out of curiosity, lets say we are told that only a basic
>description of something is needed, let's say a list of projects that a
>user is going to work on, that needs to be 500 characters long. What
>should the primary key be? Is it the 500 character description or an
>identity column? Are there performance issues with having 500 character
>foreign keys, indexes, etc.? In what ways is it going to be better than
>using an identity column taking into account that it is going to be
>passed through the QueryString, posted in links, etc.?

Hi pb648174,

CREATE TABLE Projects
(ProjectId int NOT NULL IDENTITY,
ProjectName varchar(500) NOT NULL,
PRIMARY KEY (ProjectId),
UNIQUE (ProjectName)
)

The ProjectId can be used in referencing columns. The ProjectName is the
column that is used as key externally; the UNIQUE constraints is what
enforces the unicity of the business key. No user should ever get to see
the PrjoectId value. All reports showing projects should join to the
Projects table and usse the ProjectName column. This is easily
accomplished by setting up the appropriate views and restricting access
to the underlying base tables (or, better yet, use stored procedures
only).

--
Hugo Kornelis, SQL Server MVP|||Bob: Hey what project are you working on?
John: The really, really, long description of a project that would
otherwise be simply reference by a number v2.1.3.4.5.1.8
Bob: What?
John: Project 12
Bob: Oh.|||pb648174 wrote:
> Bob: Hey what project are you working on?
> John: The really, really, long description of a project that would
> otherwise be simply reference by a number v2.1.3.4.5.1.8
> Bob: What?
> John: Project 12
> Bob: Oh.

You have answered your own question. In reality the users need some way
of identifying the project that is a lot more practical than a 500
character description. So your stated business requirement ("only a
description") is incomplete. For sure an IDENTITY column isn't going to
help because you can't set its value, you can't update it, you can't
verify it, you can't perform set-based inserts or merge data sets with
it (e.g. see your original question).

One of the key benefits of an IDENTITY column is that it has no
external meaning whatsoever. Don't compromise that. If you need a
project number use a numeric / integer column by all means, but not
IDENTITY.

--
David Portas, SQL Server MVP

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||I guess what I'm getting at is that it is a heck of a lot easier to use
an identity to refer to the row than the long description, especially
within the application. Are you saying not to use the identity in
application code anywhere and not in foreign keys or just not to
display it to the end user?|||On 15 Mar 2006 13:07:57 -0800, pb648174 wrote:

>I guess what I'm getting at is that it is a heck of a lot easier to use
>an identity to refer to the row than the long description, especially
>within the application. Are you saying not to use the identity in
>application code anywhere and not in foreign keys or just not to
>display it to the end user?

Hi pb648174,

A surrogate key is a surrogate for another key.

The surrogate is used in the application, for technical reasons.

The "another key" (some people would call it "natural key", but I prefer
to use the term "business key" - it need not be natural [what's natural
about an SSN??], but it is what the business uses) is used outside of
the application.

In this context, "outside of the application" includes screensand
reports.

--
Hugo Kornelis, SQL Server MVP|||pb648174 wrote:
> I guess what I'm getting at is that it is a heck of a lot easier to use
> an identity to refer to the row than the long description, especially
> within the application. Are you saying not to use the identity in
> application code anywhere and not in foreign keys or just not to
> display it to the end user?

Use IDENTITY for your foreign key references if you need to. Reference
it in the application (as long as you don't persist it outside the
database). Just don't expose it to users.

Don't have IDENTITY as the ONLY key of a table.

Itzik Ben-Gan gives two alternative methods for generating sequence
numbers in the following article. They are both highly scalable
solutions and are intrinically better than IDENTITY if you DO need an
exposed sequence number. What you need to consider however, is that a
monotonically increasing number is often not a very user-friendly
solution. As Joe pointed out, many, many things have standard codes
available to you. Always look for those standards first.
http://www.sqlmag.com/Article/Artic...rver_48165.html

If you have to design your own system of codes then don't just assign
them arbitrarily. Use some scheme that you can verify and then apply it
consistently. Create a data dictionary so that the codes are known and
can be shared between systems. The last thing you want is to implement
different keys for the same elements in different databases. Anyone
familiar with data integration projects can tell you the fantastic
value of standardising codes across your organization.

--
David Portas, SQL Server MVP

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||I think we are getting a little off topic from the original discussion,
but I was thinking that you guys were saying to not use the identity at
all, or to maybe only use it as a secondary method of getting at the
data. But so far, you have proposed making the identity the primary key
(which it is), using it as the foreign key (which it is) and putting a
unique constraint on things that you wish to enforce uniqueness for
(which I already do).

The numbering issue is just not a problem, when users need their own
numbers we just let them type them in. 99% of the time we don't let
users see the identity values, but every now and then we do, such as
User Ids, just so we have a quick way to talk about a particular thing.
Almost all of our tables have an identity as the primary key or some
combination thereof and so far I haven't heard any reason not to do
that. You are just saying to also put a UNIQUE constraint on the
columns that need to be unique... not a problem.

My main point was just trying to determine how set you were on the
evilness of the identity column and so far what you are saying to do is
what we are currently doing...|||pb648174 (google@.webpaul.net) writes:
> I guess what I'm getting at is that it is a heck of a lot easier to use
> an identity to refer to the row than the long description, especially
> within the application. Are you saying not to use the identity in
> application code anywhere and not in foreign keys or just not to
> display it to the end user?

Obviously a number is easier, and how it's generated it doesn't really
matter. There is no reason to be religious about IDENTITY. If that
works best for the task, use it. If doesn't, don't use it. Important
thing to know: never use it, if you need contiguous numbers.

Assigning your own keys without IDENTITY is very simple:

BEGIN TRANSACTION

SELECT @.id = coalesce(MAX(id), 0) + 1
FROM tbl WITH (UPDLOCK, HOLDLOCK)

INSERT tbl (id, ...)
VALUES (@.id, ...)

-- Other stuff as needed.

COMMIT TRANSACTION

This solution is not very good for high-transaction environments, but
your Projects table is never going to be close to that.

On the other hand, the above makes it easier to do mass inserts with
known values. You bounce the data over a temp table with an IDENTITY
column, and then insert as

INSERT tbl (id, ...
SELECT @.nextid + identcol - 1, ...

It's all a matter of convenience. The idea that you should present a
value to the user only because it was generated with IDENTITY is very
difficult to take seriously. The only problem you could run into is
that user asks "by the way, why is there no project 22?", when an
IDENTITY values was consumed in a failed INSERT. Just answer with a
smile.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

But the last post of Erland's really hit the nail on the head, in a
concise and logical way.

What he said.

regards,
Doug|||On 15 Mar 2006 14:17:11 -0800, pb648174 wrote:

>I think we are getting a little off topic from the original discussion,
>but I was thinking that you guys were saying to not use the identity at
>all, or to maybe only use it as a secondary method of getting at the
>data. But so far, you have proposed making the identity the primary key
>(which it is), using it as the foreign key (which it is) and putting a
>unique constraint on things that you wish to enforce uniqueness for
>(which I already do).

Hi pb648174,

I'd like to add to this that each table that uses an IDENTITY for it's
PRIMARY KEY, MUST also have at least one UNIQUE constraint on it's real
business key. The IDENTITY column should _NEVER_ be the only key.

>The numbering issue is just not a problem, when users need their own
>numbers we just let them type them in. 99% of the time we don't let
>users see the identity values, but every now and then we do, such as
>User Ids, just so we have a quick way to talk about a particular thing.

I think you should never expose your surrogate keys - and your example
of userids is about the worst possible example! Are you aware that
making it easy for users to guess how other userids look makes it easier
for hackers to get in?

Suppose I place an order with UPS and receive a tracking no 110056123.
I'd think nothing of it. But if I place another order the neext day and
the new tracking number is 110057044, I'll realise that all numbers in
between will probably be valid tracking numbers as well. And that gives
me an advantage if I should decide to try and spy on someone else's
shipment.

>Almost all of our tables have an identity as the primary key or some
>combination thereof and so far I haven't heard any reason not to do
>that. You are just saying to also put a UNIQUE constraint on the
>columns that need to be unique... not a problem.

Here's the second error. Using an IDENTITY surrogate key should never be
an automated action. It should be a deliberate choice. Always start with
the business key. If it's short (1, 2, or maybe 3 columns and no long
char or varchar columns) and stable (little to no chance of change once
it's in use), then you're already done. If it's not short but stable and
there are little (or no) references to this table, then you're also
already done.

You should only use a surrogate key if the business key is either not
stable, or both long (3 or more columns, and/or long [var]char data) and
referenced in other tables.

>My main point was just trying to determine how set you were on the
>evilness of the identity column and so far what you are saying to do is
>what we are currently doing...

An identity columnn is no more evil than a chainsaw. When used properly
and with care, it does it's job very good. But in the hands of the
inexperienced, it can quickly become very dangerous.

--
Hugo Kornelis, SQL Server MVP|||I agree with you except for the security part.. If the security of a
system depends on how hard the user id is to guess than you have
greater issues. Someone can guess Ids all they want, we have sufficient
security measures to make sure that users see only what they have
explicit access to. Any hacker would be able to figure out the Id from
looking at the code anyway. Like I said, the use of that Id only
happens in 1% of the situations but is useful sometimes.|||On 18 Mar 2006 05:11:20 -0800, pb648174 wrote:

>I agree with you except for the security part.. If the security of a
>system depends on how hard the user id is to guess than you have
>greater issues. Someone can guess Ids all they want, we have sufficient
>security measures to make sure that users see only what they have
>explicit access to. Any hacker would be able to figure out the Id from
>looking at the code anyway. Like I said, the use of that Id only
>happens in 1% of the situations but is useful sometimes.

Hi pb648174,

You are of course right that security should not _depend_ on userid's
being hard to guess. But on the other hand - improving security can
often be done by adding extra layers of security on top of each other.
If two setups are both secured exactly the same, with the sole
difference of userids being easy to guess in one setup and hard to guess
in the second, the second will be harder to crack.

Of course, implementing security means weighing the cost versus the
benefit of each extra security layer, not just stacking everything you
can come up with on top of each other - but in the case of sequential or
non-sequential numbering of userids, the cost is almost non-existant, so
even though the benfit is small, it's still worthwile.

Just my 0.02

--
Hugo Kornelis, SQL Server MVP

Wednesday, March 7, 2012

Getting list of recently added IDENTITY items

No comments:

Post a Comment

File Group Changes

Blog Archive

About Me