November 5, 2008 at 3:09 pm
[font="Verdana"][font="Verdana"][/font]
I am new to sql server and I need you suggestions:
which one is the best for PK generation(@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT ). If i have transactions like this.
begin tran
Insert into customer(id,fname,lname) (4,'sameer','dutts')
@cid=SCOPE_IDENTITY --cid will be 4
Insert into address (adrsid,customerid,'addressvalue')(1,4,'dutts')
end tran for such transactions what identity variable we should use.
Pls let me know.
Thanks for the Help. [/font]
November 5, 2008 at 3:32 pm
First - a little clarification: none of the items you mentioned are "PK Generation system" for several reasons. First - they don't GENERATE squat: they report on IDENTITY values that were just created. And 2 - they don't technically have anything to do with Primary Keys: just because they identity column might happen to be also set to be the primary key doesn't have any bearing on their function, nor does their behavior change if the Identity column happens to NOT be the Primary Key.
That being said - in most cases, the SCOPE_IDENT is the most useful in my experience. Because it stays limited to the current scope (meaning your execution context), you should have a pretty good idea of what table the identity value is from (since you're "in charge" of the execution process, you should know what might have last done an insert within this execution thread).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 5, 2008 at 4:21 pm
Thanks for your valuable response.I have mentioned the inserts into customer and then address table. Will it be accurate to add customerid in address table after getting it from scope_identity() within begin tran end tran. Pls let me know.
Another issue I am facing, my business needs the PK in this format:
YYMMDD01000001,YYMMDD01000002
Date (year, month, day), then then 7 numeric sequential digits), on every next day I need to change the sequence number and it should start from 1000001...
Do we have any DB Trigger which can auto populate the user table with the date change value and int value=1 so I can use this as counter? Kindly Pls suggest me for this.
November 5, 2008 at 7:10 pm
In case anyone else is wondering after reading this (I was and decided to look it up rather than ask), here's how triggers that perform inserts affect these:
From BOL:
SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.
For example, there are two tables, T1 and T2, and an INSERT trigger is defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 by the trigger.
Assuming that both T1 and T2 have identity columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1. @@IDENTITY will return the last identity column value inserted across any scope in the current session. This is the value inserted in T2. SCOPE_IDENTITY() will return the IDENTITY value inserted in T1. This was the last insert that occurred in the same scope. The SCOPE_IDENTITY() function will return the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.
November 6, 2008 at 2:43 am
This I read in BOL. And I am using SCOPE_IDENTITY(). My question if different how we can get the auto generated values every next day starting from 1.
November 7, 2008 at 8:36 am
Why does anyone impose such requirements as what must be coded in the primary key? In my opinion, this is not a good idea. I would go with IDENTITY column as the one that will be used as FK in other tables. That code od date and sequence number can be in another column as "secondary ID" or whatever you want to call it - that is, the thing that users can view. There is always a possibility that someone decides, that beginning from next month a different code will be used and all existing rows must be updated to match new definition. If this column is used as FK, all references in other tables must be updated as well (yes, if everything is properly defined, they will do do automatically - but anyway it's some activity)... and what if you have some old data in other databases?
It is always better to keep the actual ID hidden, so that you never have to change it.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply