Check Your SQL Server Identity
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/checkyoursqlserveridentity.asp
-
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.
-
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!
-
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 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 !!!** -
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.
-
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
Consultant - Straight Path Solutions
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 -
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.
-
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 -
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.
-
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.
-
Mike c, that is a good interview question, how do retrieve a GUID after an INSERT. Perhaps you can do a writeup on it and post it to the community.
-
Wrong Answer #1 - Select max(contactid) from Test.
There are more reasons than the ones mentioned that this is the wrong answer. This assumes that the identity field is going in the positive direction. It also assumes that replication isn't involved and that check constraints aren't applied to the field in the table involved.
I'm interested in the uniqueidentifier question as well. I couldn't find any way to find out what the created value was, so I make my value in a variable before inserting the record into the table with the variable passed as a value.
Good article! A valid interview question, a knowedgeable DBA would red-flag it and start asking about the environment if that wasn't already established. This would tell the interviewer that this person has some experience with the identity field, it's strengths, it's weaknesses and it's environment dependencies.
-
That's really the best way to do it - assign NEWID() to a UNIQUEIDENTIFIER variable and insert it into a UNIQUEIDENTIFIER column; then return the value using an OUTPUT parameter.
I've seen too many developers try to return a "Row GUID" by using @@IDENTITY. It just doesn't work. Here's a really oversimplified example:
CREATE TABLE CarMake (
MakeGUID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
[Description] VARCHAR(100) NOT NULL
)
GO
CREATE PROCEDURE dbo.usp_InsertCarMake @CarMake VARCHAR(100),
@CarMakeGUID UNIQUEIDENTIFIER OUTPUT
AS
SET @CarMakeGUID = NEWID()
INSERT INTO CarMake (MakeGUID, Description)
VALUES (@CarMakeGUID, @CarMake)
RETURN @@error
GO
DECLARE @CarGUID UNIQUEIDENTIFIER
EXEC dbo.usp_InsertCarMake 'TOYOTA', @CarGUID OUTPUT
PRINT @CarGUID
-
Basically I wanted a procedure that would return a GUID when all the other fields in the table were matched because all the other fields should define a natural key.
If you added
[TypeDescription] VARCHAR(100) NULL,
to your example table, the proc would look something like:
CREATE PROCEDURE dbo.usp_GetCarMake @CarMake VARCHAR(100),@CarType VARCHAR(100)=NULL,
@CarMakeGUID UNIQUEIDENTIFIER OUTPUT
AS
DECLARE @TmpGUID UNIQUEIDENTIFIER
SET @TmpGUID = NEWID()
SET @CarMakeGUID = NULL
SELECT TOP 1 @CarMake=MakeGUID FROM CarMake
WHERE <A href="mailto
escription=@CarMake">Description=@CarMakeAND (TypeDescription=@CarType OR (TypeDescription IS NULL AND @CarType IS NULL))
ORDER BY MakeGUID
IF (ISNULL(@CarMake , @TmpGUID) <> @TmpGUID ) RETURN 0
SET @CarMakeGUID = @TmpGUID
INSERT INTO CarMake (MakeGUID, Description, TypeDescription)
VALUES (@CarMakeGUID, @CarMake, @CarType)
RETURN @@error
GO
I changed the name from "Insert" to "Get" because hopefully, this will generally be retrieving values. I did NOT want to apply a unique constraint to the table because I planned on this being used in replication. After the merge all new requests would consistently go to 1 value and hopefully there would be few duplicates. If it was a problem, I could always have an evening process run a process to select the natural key values with them in a GROUP BY list and a HAVING (COUNT(*)>1) Then I could find the multiple value(s??), change all the foreign keys to point to the correct value and remove the multiple row.
On a different note, everyone tells me it's a bad idea to use cursors. I've found places where cursors defined by someone else were terrible, but I keep running into situations where a cursor seems the way to go. At first glance, this was one. I just realized I could create a temp table that has all the group by values and the min(guid) using the having selection, join this table with the permanent table on the natural keys and where the temp table guid doesn't match the permanent table guid saving both guids in a new temp table. I then update the foreign key tables setting the guid to the min guid when the other guid matches in the table. It's ugly compared to cursor logic, but probably is MUCH faster.
So, how do I tell when I really have to use cursors and when I'm just being lazy? Right now, I'm building a script that builds a script. I really can't think of a way of doing it without using a couple of cursors. Thankfully, this wouldn't be run often, so it can afford to be slow, but am I being lazy? I have a table that lists tables and the order to process the tables. I want to write a script that will copy these tables from one DB to another DB. There is no gaurantee that the schema for the tables will match. Individual commands in the script can exceed 8000 characters. Some of the scripts will have to be manually changed. How the script is written varies based on the table schema.
-
Thanks to all for the comments so far.
Certainly this question is presented in person and the candidate encouraged to offer questions to help clarify any scenario presented. Few ask anything on this one. For me, it's a frustrating question to ask - I can understand why someone thinks @@Identity is the answer. I have to think MS would have done us a far better service by changing the behavior and then supporting the old behavior with the compatability level.
On the subject of using newid(), I think by far the most compelling reason to use it is to do the key generation on the client or middle tier which may well eliminate some round trips - think the prototypical order/orderdetails scenario.
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply