displaying an auto-incremented SQL identity column

[thedougster]

Most databases have multiple users. For these databases it is a good idea to auto-increment the identity column.

I understand that an SQL identity column is not incremented until the newly-created record is inserted.

I guess this means the identity column is incremented when you actually reconnect to the database and insert the new record there, rather than just add the record to the locally-resident DataSet, correct?

And I guess this also means that for auto-incremented identity columns it is not possible to accurately display the newly-incremented identity column in the data entry WinForm used to create the new record and add it to the database, right?

For what it’s worth, the environment I’m working in is:

32-bit
SQL Server 2008 Express with Advanced Services
SQL2008 AdventureWorks
SQL Server 2008 Management Studio Express
Visual C# 2008 Express

 

[danvlas]

Correct and right.
Identity is generated when attempting to commit a new record in the database.
If insert fails or transaction is rolled back, the identity value is lost.
Losing identity values does not mean anything, there is no damage to the data or structures.
Do not give any meaning to the identity field except unicity and you are safe.



Daniel Vlas
Systems Consultant

 

[sqlfable]

>>Do not give any meaning to the identity field except unicity and you are safe.

What's the sense in having it then? If this identity is not going to become a strength in you relational integrity by forming it as a key, then why have the wasted space in teh database. Use row_number() if the intention is a simplistic task such as unicity.

As far as showing the identity values before and after. scope_identity() will show you on insert. Be careful of others like @@identity. They can prove false. If you want to show a potential identity prior to insert then I suggest creating your own unique identity values and retain them once taken to prevent other users from grabbing the same. If the key is destroyed before utilization then trash it so it can be used later on

 

[thedougster]

sqlfable:

>If this identity is not going to become a strength in you
>relational integrity by forming it as a key, then ...Use
>row_number() if the intention is a simplistic task such as
>unicity.

What more is involved in using the identity column as a key than mere unicity?

 

[danvlas]

sqlfable, I am sorry. I meant 'meaning for the user'. I was sure everyone would understand that, but you proved me wrong.
Now...in my opinion, row_number() is a function that has nothing to do with unicity. Try to use the 'partition' argument before 'order by', I'm sure you will get my point. Not to mention that the function will not have any effect on the rows that are not returned by the query at a certain moment.
And I know at least one case where scope_identity will fail, but @@identity will not. Even though the former should be used in 99.9% of the situations.
The secret is to know when to use one and when the other.


Daniel Vlas
Systems Consultant

 

[sqlfable]

>>The secret is to know when to use one and when the other.

Amen!

>>row_number() is a function that has nothing to do with unicity

I think "What more is involved in using the identity column as a key than mere unicity?" gave no end to the thread because it is quite ridiculous to think that. I do agree on row_number() but for the task it seems that is trying to be accomplished and hinted towards, I see no reason to cause more table space to be consumed and fragmentation to be monitored. Identity seeds are severely misused in a lot of cases. Was hoping to try and get that across.

 

[danvlas]

the dougster: I will take the liberty to answer that:
Any table should have a natural key (a constraint that prevents duplicates from being entered into the table). In theory, that constraint should make the primary key of the table, and in some cases it does.
However, this implies foreign keys to have the same number of fields, so if you have a 5-field primary key, those 5 fields have to be also present in the child table..
A simple solution is to use a unique identifier for the row and use the Identity property. This field does not prevent record duplication (that is still done by the natural key), but helps you build one-field relationships, easier to maintain than natural keys.

So, the answer to your question is: unicity and relationship simplicity. But, since the latter is just an effect, unicity is the only real reason for it to exist.


Daniel Vlas
Systems Consultant

 

[markros]

How does everyone of you solve the problem with parent, child and grandchild tables? Do you always use SP, transactions and pass all new values to this SP using the identity field as primary key or there is another method?

We want to move our application from VFP native tables to SQL Server and this is the question I don't have a clear answer in my mind yet.

Thanks in advance.

 

[danvlas]

markros: It seems that your question opens a new, different topic.
I would suggest that you start another thread.




Daniel Vlas
Systems Consultant