SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Check Your SQL Server Identity


Check Your SQL Server Identity

Author
Message
Andy Warren
Andy Warren
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: Moderators
Points: 25727 Visits: 2746
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/checkyoursqlserveridentity.asp

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
jbreffni
jbreffni
SSChasing Mays
SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)

Group: General Forum Members
Points: 660 Visits: 271

There is a mistake in the article.

Where it says "Returns a value of 5.", the line above should be scope_identity() instead of @@identity

Jim.





Michael Lysons
Michael Lysons
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2231 Visits: 1453
I've just started coding my first asp.net app and I'm glad to see that the approach I've taken matches the 'right answer' from the article!
sushila
sushila
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8543 Visits: 639
huh - this is a "neither here nor there" comment - just yesterday when I was browing through the ssc articles I read one by Brian that addressed the same topic...except his was with reference to triggers....

Brian's article

Brian also addresses IDENT_CURRENT() which should've gotten a mention in Andy's as well!!! Otherwise, as usual - an informative article!







**ASCII stupid question, get a stupid ANSI !!!**
Alex-217289
Alex-217289
SSC Veteran
SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)

Group: General Forum Members
Points: 271 Visits: 3
Using this as a way to educate and bring developers up to speed makes sense. Using this as a question for a prospective DBA or developer is hogwash. There are too many assumptions made by this scenario to expect any developer or DBA to get the right answer without alot of conversation first.


Cheers,

Alex


Rogue DBA
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45453 Visits: 14925
I have to disagree with Alex. This is something any SQL Server DBA or developer should know and be able to answer relatively quickly. We were "caught" by @@identity in SQL Server 7 because we had triggers that created an identity column as well. Another location inf our compnay would have been caught by it as well in SQL 2000 had I not found their use of @@Identity and informed them of Scope_Identity and Ident_Current.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
ITBandits
ITBandits
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 1

I think that the fact that there are assumptions makes it even better. In a case like this, conversation is necessary. It's developers or DBAs going off half-cocked that very often causes problems. That there could be problems forces a person to evaluate the possible problems using a solution would cause. Sure, it'll take possibly a full minute (with the discussion of the environment) instead of 5 seconds to give a solution, but it'll be a solution that will work with less chance of failure.


Sameer Raval
Sameer Raval
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1050 Visits: 255

Excellent Article.This is the problem wih every dba /developer

if you are senior and started carrier from sql server 7.0.

Now again this is year their is need of lot of learning for Yukon.

which is complete rewrite, not patch work to sql2000



Kindest Regards,

Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
Steve Jones
Steve Jones
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: Administrators
Points: 148719 Visits: 19446
I think it's a great question. If you are presented with any question in an interview it won't be with a complete understanding of the situation. That's why you ask questions and look to answer the question. Your thought process and interaction when someone asks you something is critical to both sides learning how you will fit in.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Mike C
Mike C
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6581 Visits: 1172

I think it's a fair/decent question. A good job candidate should be prepared to ask the only relevant question when presented with this question: "What is the scope?" That's the only piece of information you need in order to determine which method of returning the last inserted identity value is appropriate.

Another good interview question involves how to retrieve a GUID after an INSERT.


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search