@@IDENTITYin very busy site - if many insert in same time What happend?

Question

Post reply

@@IDENTITYin very busy site - if many insert in same time What happend?

Viewing 13 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply

tfifield SSCrazy Eights Points: 9655 More actions · Answer 1

There could also be something really silly going on in the logic. Something like:

CREATE TABLE #Temp

( RecID INT IDENTITY(1, 1)

, SomeText VARCHAR(50)

)

INSERT INTO #Temp

( SomeText )

SELECT 'Some Text'

WHERE 1 = 2

Nothing is inserted - no Scope Identity no Rows Affected.

Todd Fifield

Valentin Petkov SSC Rookie Points: 49 More actions · Answer 2

Thank you very much for all responses

Yes

There is one column INDENTITY all the rest columns accept NULLS no duplicate check nothing

ONLY SIMPLE INSERT

There NO DB Error log when call the SQL statement, But fail to insert (because there is no GAPS into indentity column)

There is trace GUID from the client they came to our web site with this guid we store it in DB in this table and we redirect to thier page with the GUID and they have confirmation that this person visit our web site and COMPLETE his purpose.

BUT OUR problem is there is NO RESPONSE FROM THIS PERSON

BECAUSE IN THIS TABLE where we store this guid, cometime .... + @@INDENTITY return NULL

WE USE THIS INDENTITY ID and store his response in other table

The margin error is fine 6000 clients / 40-50 lose / 1-3 clients per second

I just want to know MORE about this...

it is SIMPLE INSERT INTO then SELECT @INDENTITY as ID ....but NULL :w00t:

Thank you very much again for responses

valentin

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

As I said in my last post. Run a Trace against the server to make sure the application is actually doing the insert. If the insert was failing you WOULD have a gap in the Identity values because, even when you do not explicitly say BEGIN TRANSACTION, there is a transaction by default and the identity value is incremented.

You NEED the trace to verify what you think is happening IS really happening. I assume the web application code is running the INSERT code either directly or through a stored procedure and, based on the information you have provided and the accumulated SQL Server knowledge of all those who have commented on this thread, I would check the Web Code for a bug.

A TRACE WILL help you find out if there is a call to the INSERT when you expect it to happen.

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

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 4

Valentin Petkov (8/7/2008)
...it is SIMPLE INSERT INTO then SELECT @INDENTITY as ID ....but NULL

You have got to get off the track you are on or it will be very difficult to find the answer. If you execute an Insert statement, and a row is inserted into the table, @@Identity WILL return the correct value.

You are more willing to think that there is something wrong with @@Identity than to think there may be something wrong with your code.

Get this straight right now: the problem is with your code. It is not doing (or not always doing) what you think it should be doing. Once you accept that fact, you will already be much closer to finding the problem.

I have asked a simple question: is there an Instead of trigger on the table? Another comes to mind: Is the "Insert/Select @@Identity" sequence being executed in the database (a stored procedure perhaps) or in the application? If possible, show us this code.

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms

CussonC Grasshopper Points: 10 More actions · Answer 5

Like a number of previous posters said, @@IDENTITY is not a good thing to use...

I normally use ident_current. Here's an example...

CREATE TABLE Temp1 (ID int IDENTITY(1,1) NOT NULL, someChar Varchar(4) not null)

insert into temp1 (someChar) values ('junk')

select ident_current ('temp1')

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 6

Have you tried using SCOPE_IDENTITY() function yet?

If you have many users running inserts against same table, @@IDENTITY can returns any users last ID (regardless of scope).

N 56°04'39.16"
E 12°55'05.25"

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 7

http://msdn.microsoft.com/en-us/library/ms187342.aspx

Do you have some transactions around the insert statement?

N 56°04'39.16"
E 12°55'05.25"

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 8

Here is another reason causing @@IDENTITY returning "wrong" value

declare @abc table (i int identity(100,1), j int)

insert @abc values (1)

SELECT @@IDENTITY AS [WhatID?]

declare @mytab table (id int identity(1, 1), myfield tinyint)

INSERT INTO @myTab (myField) VALUES(123456)

SELECT @@IDENTITY AS [WhatID?]

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

Heh... even SCOPE_IDENTITY() doesn't fix that problem. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tfifield SSCrazy Eights Points: 9655 More actions · Answer 10

Here's an actual example of why using @@IDENTITY is risky:

CREATE TABLE Test

( TestID INT IDENTITY(1, 1)

, SomeText VARCHAR(50)

)

GO

CREATE TABLE TestLog

( LogID INT IDENTITY(100, 1)

, TestID INT

, LogText VARCHAR(50)

)

GO

CREATE TRIGGER Test_ITrig

ON dbo.Test

AFTER INSERT

AS

BEGIN

SET NOCOUNT ON;

INSERT INTO TestLog

( TestID, LogText )

SELECT

TestID, SomeText

FROM inserted

END

GO

-- An insert

INSERT INTO Test

( SomeText )

SELECT

'Some Text'

SELECT

@@IDENTITY AS AtATIdentity

, SCOPE_IDENTITY() AS ScopeIdentity

-- AtAtIdentity = 100 - The identity from TestLog in the Trigger

-- ScopeIdentity = 1 - The identity from the Insert statement

Todd Fifield

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 11

Jeff Moden (8/9/2008)
Heh... even SCOPE_IDENTITY() doesn't fix that problem. 🙂

Well put.

I just demonstrated that we know to little about OP environment and code to make helpful suggestions.

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 12

Peter, are you, by any chance, going to PASS this year? Sure would like to me you in person, someday.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 13

I thought PASS in Europe happened in April?

http://www.european-pass-conference.com/[/url]

Are there more PASS conferences?

Oh, I see now that you are one of those "yankees" 😀

November 18-21, 2008

Washington State Convention & Trade Center, Seattle WA.

Well, not very likely to happen this year (unless I get sponsored 😛 ).

But... Me and my girlfriend have plans to hike (buy a car and drive anti-clockwise) around the states not so far in the future.

N 56°04'39.16"
E 12°55'05.25"