Need Udf for next sequential key operation

Question

Post reply

Need Udf for next sequential key operation

Ron Cicotte

SSC Veteran

Points: 274
More actions
October 28, 2006 at 6:54 am

#114899
I have a client with a large database using cursors everywhere. I'm in the process of doing performance analysis and designing solutions to minimize the cursors but need a quick fix for one particular condition. There is a procedure that gets the next sequential integer value for a table's primary key. They don't want to use an identity for some unknown reason.
The procedure is a simple one and we have all seen variations of this:
CREATE PROCEDURE
[dbo].[cps_FetchDataBaseKey] @KEYNAME VARCHAR ( 80 ) ,
@KEYVALUE
INT OUTPUT ,
@RETCODE
INT OUTPUT
AS
BEGIN
SELECT
@RETCODE = 1 , @KEYVALUE = 0
UPDATE TBL_DATABASEKEYS SET @KEYVALUE = KEYVALUE = KEYVALUE + 1 WHERE KEYNAME = @KEYNAME
IF ( ( @@ERROR <> 0 ) OR ( @KEYVALUE IS NULL ) OR ( @KEYVALUE = 0 ) ) GOTO PROC_ERR
SELECT @RETCODE = 0

PROC_ERR
SELECT @RETCODE = 1
END
I would like to convert this into a function to be used in a set based UPDATE:
UPDATE tbl_TEST
SELECT uniqueIdentifier = udf_getNextKey('RowId',0,0)
targetCOL1 = s.sourceCOL1
...
targetCOLn = s.sourceCOLn
FROM sourceTable s
WHERE ...
The problem with this is of course is that you cannot perform an update or call a procedure from within a function. I could create an extended proc but I'm somewhat cautious about that.
I have a couple of questions for the TSQL Gurus at SSC:
- Is using an extended stored procedure a viable option here?
- Is there any performance benefit to this approach vs leaving the Update a cursor based operation?
- What are the other options short of a total redesign. That's coming but I need the quick fix now.
Thanks in advance for all replies!
Ron Cicotte
ron.cicotte@gmail.com
Data Transformations, LLC

Viewing 7 posts - 1 through 6 (of 6 total)

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

jezemine SSCrazy Eights Points: 8059 More actions · Answer 1

It looks like you need to generate sequential guids. correct?

if so you should check out Gert Draper's XPGUID library:

http://www.sqldev.net/xp/xpguid.htm

and if you are on 2005 (guessing you are not) you should be using newsequentialid()

http://msdn2.microsoft.com/en-us/library/ms189786.aspx

---------------------------------------
elsasoft.org

Lowell SSC Guru Points: 323518 More actions · Answer 2

id like to hear why they cannot use an identity column. is it because the application is using the borland BDE as the provider to the database?

the correct solution is to use an identity column. no doubt about it. the functionality is just a replacement for something SQL can already do, so you need to make them justify why they don't want to use an industry accepted practice.

i've encountered an issue with the BDE, and they used code in the application to call a stored proc to get teh next value, and then inserted the data along with the results from the proc. Is that what is happening here?

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!

Sergiy SSC Guru Points: 110208 More actions · Answer 3

I don't see any other use of this function but for cursoring.

If you mean to get rid of cursors, you don't need this function at all.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

I agree with the others.... the "quick fix" would be to convince folks that an IDENTITY column is the only way to go.

And, just to give you the warm fuzzies... the fastest a cursor can do updates is way slower than a good set based update using an IDENTITY column.

And, I agree with Serqiy... if you use the new function that's offered in SQL Server 2005 for this, then you're still using RBAR (pronouced "ree-bar" and is a "Modenism" for "Row By Agonizing Row") methods instead of the excellent utility and performance realized in an IDENTITY column. If their objection to using an IDENTITY column is because they want to have transportable code (Oracle using sequence functions and has no autonumbering column), then they need to look into some of the SQL extensions of both SQL Server and the other databases to see just how impractical that little idea really is.

If an IDENTITY column will break 3rd party code, then at least make your proc accept an INCREMENT instead of just using "+1" as a hardcoded increment. Then, you can load what would normally go into a cursor into a temp table that DOES have an IDENTITY column and use your NextID proc once with an increment and add that NextID to the IDENTITY column to come up with a "block" of ID's instead of some God awful RBAR function. Then, do a set based update from the temp table.

Still, you should convince them to switch to an IDENTITY column... it's the only way to go.

--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)

Ron Cicotte SSC Veteran Points: 274 More actions · Answer 5

All,

Sorry for taking so long to respond to your comments. They are greatly appreciated. I've been working 100+ hours per week on this project and don't get a lot of time to get back here often enough.

Talking to an old friend of mine who is also a TSQL guru we came up with a solution that was essentially what Jeff suggested. I first get the row count of the records to be inserted and then pass the value to increment the keys:

CREATE

PROCEDURE [dbo].[cps_FetchDataBaseKeySet]

@KEYNAME

VARCHAR ( 80 )

,

@KEYS INT

,

@KEYVALUE INT OUTPUT

AS

BEGIN

Declare

@v_errmsg varchar(200)

/*

** DISABLE SQL MESSAGING.

*/

SET NOCOUNT ON

BEGIN

TRY

-- INCREMENT THE KEY VALUE

-- Set the error message in case it fails

SET @v_errmsg = 'Failed while updating TBL_DATABASEKEYS'

UPDATE TBL_DATABASEKEYS with (rowlock)

SET @KEYVALUE = KEYVALUE = KEYVALUE + @KEYS

WHERE KEYNAME = @KEYNAME

END

TRY

BEGIN

CATCH

-- log @v_errmsg

RETURN -1

END

CATCH

END

GO

-------------------------------------------------------

This works great for now and solves the immediate problem. As for the Identity issue I'm still working on that and will eventually get them to convert everything to sets based logic. They really don't have a good reason for it except for legacy thought processes. I think that they originally ran into a problem with inconsistancies in replication processes when the system broke and didn't know how to fix it.

The problem is not about breaking third party code. It's about not having the time to make all of the fixes everywhere. The tables I am inserting into have existing processes that also insert new records so the key incrementing process has to be consistent. I cannot change all of the RBAR functions just yet. That will come as part of the overall solution following a formal performance analysis report.

-ron

Ron Cicotte
ron.cicotte@gmail.com
Data Transformations, LLC

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 6

Thanks for the feedback.

--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)