Most efficient way to retrieve top identity value.

Question

Most efficient way to retrieve top identity value.

GabyYYZ

SSCertifiable

Points: 7913
More actions
February 2, 2010 at 10:01 am

#220161

We are working on a database that is composed of a table holding identity values and an encrypted credit card column, plus some other data. Inserts/updates/retrievals of that column are done through a third party app which runs a select on a corresponding view and decrypts if the user is authorized. So far this part is working and not the main issue.
At the end of an insert procedure, we would like to retrieve the corresponding identity value that goes with that cc #. We would like to know the most efficient way to do so. Because there will be multiple inserts from various sources, SELECT MAX(identity_column) variations would not be the best, and neither would @@identity, SCOPE_IDENTITY, or IDENT_CURRENT.
Performing the equivalent of a SELECT ident_column WHERE cc_value = @insertvariable results in an index scan, rather than a seek, so with millions of rows, this would be an issue.
I'm thinking the best way to do this would be to start with a BEGIN TRAN part, do the insert, retrieve the identity value into a variable via a IDENT_CURRENT('tablename') call, and then COMMIT TRAN. Obviously this would involve blocking, but if it is very quick, especially with many inserts a minute, this shouldn't be an issue, is it?
Anyone have some real world advice on how they dealt with this?
Thanks.
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein

Viewing 5 posts - 1 through 4 (of 4 total)

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

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

If you want to retrieve the identity value for the row just inserted in the same stored procedure, I don't know why SCOPE_IDENTITY wouldn't work. Can you elaborate on that?

Greg

Lowell SSC Guru Points: 323518 More actions · Answer 2

Gaby if you are inserting single rows at a time, even if there are lots of them per second, scop-identity() should work fine.

if you have a single statement that is inserting mulitple rows, you need to use the optional OUTPUT parameter to return any information you need; it is basicalyl a way to access the INSERTED virtual table when an insert occurs; you create a temp table to capture the results, and use the multi row results for subsequent processing:

here is a basic example:

CREATE TABLE adds(

adid INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

code VARCHAR(30) )

DECLARE @MyResults TABLE(

ID int,

newcode VARCHAR(30),

oldcode VARCHAR(30) )

INSERT INTO adds(code)

OUTPUT

INSERTED.adid,

INSERTED.code,

NULL

INTO @MyResults

SELECT 'aliceblue' UNION ALL SELECT 'antiquewhite' UNION ALL

SELECT 'aqua*' UNION ALL SELECT 'aqua*' UNION ALL

SELECT 'aquamarine' UNION ALL SELECT 'azure' UNION ALL

SELECT 'beige' UNION ALL SELECT 'bisque' UNION ALL

SELECT 'black*' UNION ALL SELECT 'black*' UNION ALL

SELECT 'blanchedalmond' UNION ALL SELECT 'blue*' UNION ALL

SELECT 'blue*' UNION ALL SELECT 'blueviolet' UNION ALL

SELECT 'brown' UNION ALL SELECT 'burlywood' UNION ALL

SELECT 'cadetblue'

UPDATE dbo.adds

SET code = UPPER(SUBSTRING(code, 1, LEN(code) - 3) )

OUTPUT

INSERTED.adid,

INSERTED.code,

DELETED.code

INTO @MyResults

WHERE LEFT(code,1) = 'a'

SELECT * FROM @MyResults

--results of update

ID newcode oldcode

1 ALICEB aliceblue

2 ANTIQUEWH antiquewhite

3 AQ aqua*

4 AQ aqua*

5 AQUAMAR aquamarine

6 AZ azure

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

GSquared SSC Guru Points: 260824 More actions · Answer 3

Like Lowell said, the Output clause can get that for you pretty efficiently. I've used that lots of times in lots of procs.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

GabyYYZ SSCertifiable Points: 7913 More actions · Answer 4

GSquared (2/2/2010)
Like Lowell said, the Output clause can get that for you pretty efficiently. I've used that lots of times in lots of procs.

Thanks everyone for their help, and you've given me food for thought. One of the developers also came up with a different solution. Use the encryption function/proc to generated the encrypted value, find that on nonclustered indexed column of encrypted data, and retrieve the identity value that way. It's backwards, but with the index on it, it should go much quicker.

I will practice though with the various identity functions and see which works.

Thanks again.

Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein