Clever Code or Foolish Functions?

I’ll also add another tip.  If you know the number of rows you’ll be adding ahead of time, it is much more efficient to reserve all new keys at once using the @increment parameter.  So if you know you are going to add ten new rows, you can execute like this:

Declare @increment int

Select @increment = COUNT(*) From #myTempTable  -- or whatever you need here

declare @next_id int

execute @next_id = dbo.AP_GET_NEXTID_function   @type = 'LO', @increment = 10

select @next_id

Insert dbo.LT_TABLE(id, description, some_other_value)

Select @next_id - 1 ROW_NUMBER() Over (Order by a_unique_identifier_column),

      description,

      some_other_value

From #myTempTable

The next_id returned is always the first ID in the set.  So if you get back a next_id of 100, you have reserved 100-109 for use.  Get a count of how many rows, reserve that number of Id’s (keys), then use the Row_number() function to increment the id  in a single insert select query.

+Ryan Creps

+Tessitura Network

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Steven Bras
Sent: Monday, December 12, 2011 5:18 PM
To: Ryan Creps
Subject: Re: [Tessitura Technical Forum] Clever Code or Foolish Functions?

Just to confirm: be sure to always use the ap_get_nextid_function; in version 11, there's a change in how nextid's are generated, and this function will be updated and any code using it will continue to function properly.

The version 11 change, in case I've piqued your curiosity: the next_id column will now contain the true "next id", ie, the one that should be assigned to the next object of a particular type. The ap_get_nextid_function will return that value, and then increment it in the t_next_id table, rather than the reverse as done in version 10 and prior.

I’ll also add another tip.  If you know the number of rows you’ll be adding ahead of time, it is much more efficient to reserve all new keys at once using the @increment parameter.  So if you know you are going to add ten new rows, you can execute like this:

Declare @increment int

Select @increment = COUNT(*) From #myTempTable  -- or whatever you need here

declare @next_id int

execute @next_id = dbo.AP_GET_NEXTID_function   @type = 'LO', @increment = 10

select @next_id

Insert dbo.LT_TABLE(id, description, some_other_value)

Select @next_id - 1 ROW_NUMBER() Over (Order by a_unique_identifier_column),

      description,

      some_other_value

From #myTempTable

The next_id returned is always the first ID in the set.  So if you get back a next_id of 100, you have reserved 100-109 for use.  Get a count of how many rows, reserve that number of Id’s (keys), then use the Row_number() function to increment the id  in a single insert select query.

+Ryan Creps

+Tessitura Network

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Steven Bras
Sent: Monday, December 12, 2011 5:18 PM
To: Ryan Creps
Subject: Re: [Tessitura Technical Forum] Clever Code or Foolish Functions?

Just to confirm: be sure to always use the ap_get_nextid_function; in version 11, there's a change in how nextid's are generated, and this function will be updated and any code using it will continue to function properly.

The version 11 change, in case I've piqued your curiosity: the next_id column will now contain the true "next id", ie, the one that should be assigned to the next object of a particular type. The ap_get_nextid_function will return that value, and then increment it in the t_next_id table, rather than the reverse as done in version 10 and prior.