Help me o' SQL gurus for my faith is weak



  • I see that this works:

    INSERT INTO tMembers (MemberName) VALUES ('Manohar');
    SELECT @@IDENTITY AS NewID;


    But for some reason, it makes me feel unhappy and unsafe.  What do you guys do to get the ID of the record just inserted?



  • It will work under very strict conditions, but you should be using SCOPE_IDENTITY().  @@IDENTITY is a global variable and will give you the last identity generated across all connections to the database.  SCOPE_IDENTITY() will return the last identity generated from the current connection.

    Larry




  • @lpope187 said:

    It will work under very strict conditions, but you should be using SCOPE_IDENTITY().  @@IDENTITY is a global variable and will give you the last identity generated across all connections to the database.  SCOPE_IDENTITY() will return the last identity generated from the current connection.

    Larry



    Close.

    @@IDENTITY is connection specific, but not scope specific.  The biggest downfall of @@IDENTITY is if you insert a row into a table and a trigger inserts a row into a log table because of your insert, you'll get the log table's identity instead of the table you inserted into.  However, SCOPE_IDENTITY will return the identity value from your insert, not the trigger.  You should pretty much always use SCOPE_IDENTITY.  If you want a cross-session identity for an object then use IDENT_CURRENT.

    BTW, doing a select after insert may feel a little weird, but it is one of the few ways to eliminate insert locking hotspots.  Oracles SEQUENCE does the same thing, but in a totally different way.

     



  • @jsmith said:

    BTW, doing a select after insert may feel a little weird, but it is one of the few ways to eliminate insert locking hotspots.  Oracles SEQUENCE does the same thing, but in a totally different way.



    For years, I wrote (in Oracle; "foobar" is the table, "seq_foobar" is the sequence used to generate the IDs):

    insert into foobar (foobar_id, foo, bar) values ( seq_foobar.nextval, v_foo, v_bar );
    select seq_foobar.currval into v_id from dual;


    but then I learned that the right way to do it is

    insert into foobar (foobar_id, foo, bar) values ( seq_foobar.nextval, v_foo, v_bar )
        returning foobar_id into v_id;



    (Works also if foobar_id is set in a trigger)



  • is SCOPE_IDENTITY a standard kind of thing that will work in all databases, or is it MS SQL Server specific?




  • @tofu said:

    is SCOPE_IDENTITY a standard kind of thing that will work in all databases, or is it MS SQL Server specific?

    I think it's MS SQL Server specific feature, though it might work in Sybase too (since earlier version of MS SQL Server were derived from Sybase).



  • yuck!

    ok, how about this idea then, given this table:

    create table a (
        i int identity(1,1),
        x char(1),
        y char(1),
        z char(1)
    ) go
    alter table a add constraint a_pk primary key (i) go

    when I insert a row there, I'll be inserting three characters. 

    insert a (x,y,z) values ('x','y','z')

    here's what I've been doing for years:  select i from a where x='x' and y='y' and z='z'

    and in the real world, I've never seen that fail, though I recognize that it might.  Is it completely wtf for me to just keep doing that for compatibility reasons?



  • tofu,

    I have bene doing the same thing as I am not always working with SQL Server so I can't rely on @@IDENTITY, So I just select back out my value.  It hasn't failed me...



  • @tofu said:

    yuck!

    ok, how about this idea then, given this table:

    create table a (
        i int identity(1,1),
        x char(1),
        y char(1),
        z char(1)
    ) go
    alter table a add constraint a_pk primary key (i) go

    when I insert a row there, I'll be inserting three characters. 

    insert a (x,y,z) values ('x','y','z')

    here's what I've been doing for years:  select i from a where x='x' and y='y' and z='z'

    and in the real world, I've never seen that fail, though I recognize that it might.  Is it completely wtf for me to just keep doing that for compatibility reasons?


    ... and then you execute [i]another[/i] query to nullify x y and z, because otherwise the table will be filled with 'x', 'y' and 'z' and a select will return a whole lotta records in abritrary order?



  • Er, if x, y, and z together form a primary key why not just use a compound PK instead of a surrogate PK and save yourself all the hassle of having to retrieve the autogenerated ID?

     



  • @DrPizza said:

    Er, if x, y, and z together form a primary key why not just use a compound PK instead of a surrogate PK and save yourself all the hassle of having to retrieve the autogenerated ID?



    'greed.

    That non-official compound PK is reinventing the wheel, where the actual wheel is @@identity or scope_identity.



  • @tofu said:

    is SCOPE_IDENTITY a standard kind of thing that will work in all databases, or is it MS SQL Server specific?


    Methods of getting the last inserted value of an identity/sequence/autoincrement/whatever column are all DBMS specific, to my knowledge.



  • @DrPizza said:

    Er, if x, y, and z together form a primary key why not just use a compound PK instead of a surrogate PK and save yourself all the hassle of having to retrieve the autogenerated ID?

    That would depend on which side of the natural vs. synthetic primary key debate one falls.  On one hand, it would be folly for anyone to read your response and assume a natural key approach is the obvious and only answer.  On the other hand, Google Trends suggests that in general people are looking for natural much more than synthetic.



  • I fall on the sane side.

     



  • @dhromed said:


    ... and then you execute [i]another[/i] query to nullify x y and z, because otherwise the table will be filled with 'x', 'y' and 'z' and a select will return a whole lotta records in abritrary order?


    Are you joking?  Do you not understand that this is an example and that 'x', 'y', and 'z' represent real values?

    @DrPizza said:
    Er, if x, y, and z together form a primary key why not just use a compound PK instead of a surrogate PK and save yourself all the hassle of having to retrieve the autogenerated ID?


    x,y, and z together do not form the primary key.  That's I my post specifically said in my post that I recognized that this might fail.  If x,y, and z together were a key (for example, if x was the SSN and y and z were the first and last name) then there's no way it would ever fail.  Right?  So what I'm saying is, I recognize that it isn't perfect and I wanted to discuss it.

    @dhromed said:
    That non-official compound PK is reinventing the wheel, where the actual wheel is @@identity or scope_identity.


    have you been reading the thread?  I specifically asked if this would work on other databases and was told no.  So it's not the wheel, it's a Microsoft wheel.

    Do you guys want a real-world example?  Here's something that I did this morning.  We have an internal documentation system where someone can post a note about a service.  So for example, someone might go to the "wireless network" page and post a note that says, "the network is down."

    With me so far?  Ok, on this page, an integer key ServiceID is passed to me and I display a form that lets a user post a comment.  After they save the comment the page reloads in edit mode because, you know, everybody likes to edit their posts.

    So what I have to do is, insert the new text that the user just entered, and then get the key to that row and go into edit mode.  The table looks something like this:

    create table ServiceAlert (
        alertID int identity(1,1),
        serviceID int,
        postDate datetime,
        alertText ntext
    )

    serviceID is obviously a FK.  Just before I do the insert, I get a timestamp from the server and store that in a variable, TStamp = now().  I do that rather than set the default value of postDate to getDate().  My insert looks like this:

    insert ServiceAlert (serviceID, postDate, alertText) values (#ServiceID#,'#TStamp#','#alertText#')

    and then after that I select alertID from ServiceAlert where serviceID = #ServiceID# and postDate = #TSTamp#

    Then on the edit form I can have a hidden field containing the alertID that we are editing.  It works, I just wanted to see if anyone had any better ideas.  scope_identity is database specific.

    thanks



  • @tofu said:



    Are you joking?  Do you not understand that this is an example and that 'x', 'y', and 'z' represent real values?

    @DrPizza said:
    Er, if x, y, and z together form a primary key why not just use a compound PK instead of a surrogate PK and save yourself all the hassle of having to retrieve the autogenerated ID?


    x,y, and z together do not form the primary key.  That's I my post specifically said in my post that I recognized that this might fail.  If x,y, and z together were a key (for example, if x was the SSN and y and z were the first and last name) then there's no way it would ever fail.  Right?  So what I'm saying is, I recognize that it isn't perfect and I wanted to discuss it.

    I think I understand what you're trying to get at.  The only thing I can think of that might work in a more platform independent way is, assuming that the latest entry is always the highest numbered one, to run the insert and immediately read back the highest numbered alertID within a serializable transaction.

    The trade-off there would be speed, of course.  Another thing you might do that would be "less transactional" would be to go ahead and assume the postDate is going to be unique.  That's much like your alternative, but with millisecond level granularity and only a few users making manual entries, it's unlikely that you'd run into a conflict.  It sounds like the worst case scenario here anyway is some coworkers get the wrong post to edit, and since it's internal, you're likely just to get a good laugh out of the situation if it even ever comes up.

    Can I ask how important it is to you that the code be portable?  I'd probably go with scope_identity(), myself, but if I absolutely couldn't use that, those are the alternatives I can think of.

    Of course the Pedants' Professional Chorus™ will no doubt chime in soon, but hopefully you get a chance to see this before the HiveMind delivers its verdict on everything from your choice of platform to you taste in clothing.

     



  • @tofu said:


    My insert looks like this:

    insert ServiceAlert (serviceID, postDate, alertText) values (#ServiceID#,'#TStamp#','#alertText#')

    and then after that I select alertID from ServiceAlert where serviceID = #ServiceID# and postDate = #TSTamp#

    Then on the edit form I can have a hidden field containing the alertID that we are editing.  It works, I just wanted to see if anyone had any better ideas.  scope_identity is database specific.

    thanks


    If your database survives this treatment, a large XML file would probably do just as well. Some reasons why you should not do it:
    - performance - if you have no indexes on the other columns, it must suck
    - duplicate rows - break your system
    - WYIINWYS (what you insert is not what you select) - accuracy limits, columns changed by triggers etc. might easily lead to a situation where you can't find the row you've just inserted

    but the most important reason is
    - no matter which database you use, your database definitely supports a better way to do it



  • @tofu said:


    My insert looks like this:

    insert ServiceAlert (serviceID, postDate, alertText) values (#ServiceID#,'#TStamp#','#alertText#')

    and then after that I select alertID from ServiceAlert where serviceID = #ServiceID# and postDate = #TSTamp#

    Then on the edit form I can have a hidden field containing the alertID that we are editing.  It works, I just wanted to see if anyone had any better ideas.  scope_identity is database specific.

    thanks


    If you do write code like this, and you really want that identity PK, you better damn well also have a unique constraint on (serviceID, postDate).  Not just an index, but a unique constraint. 



  • If your coding an internal business application, why even worry about database compatibility? Afterall its unlikely that it will suddenly be changed.

    In SQL Server 2005 you can use the new "OUTPUT clause" to retrieve the new id:-
    insert into some_table(some_date)
        output inserted.id as new_id
        values(GetUtcDate())

    of course there are better uses of output and scope_identity() is probably still the way to go for single insert statements but it would allow you to get all the new ids when doing an insert...select statement.



  • @Ixpah said:

    If your coding an internal business application, why even worry about database compatibility? Afterall its unlikely that it will suddenly be changed.

    In SQL Server 2005 you can use the new "OUTPUT clause" to retrieve the new id:-
    insert into some_table(some_date)
        output inserted.id as new_id
        values(GetUtcDate())

    of course there are better uses of output and scope_identity() is probably still the way to go for single insert statements but it would allow you to get all the new ids when doing an insert...select statement.


    This "output" clause is relatively similar to Oracle's "returning"; if you use it, it's probably a little bit easier to switch to Oracle should anyone anytime for any reason wish to.


Log in to reply