February 14, 2017 at 9:24 am
There are several tables that were created using a lookup Table to get the Primary Key as opposed to defining the column as an Identity column.
The following SELECT Statement is used to get the Max ID Number and it is incremented by one on a table insert.
Any suggestions would be greatly appreciated.
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 14, 2017 at 10:05 am
Welsh Corgi - Tuesday, February 14, 2017 9:24 AMThere are several tables that were created using a lookup Table to get the Primary Key as opposed to defining the column as an Identity column.
The following SELECT Statement is used to get the Max ID Number and it is incremented by one on a table insert.
SELECT LID_ID_NB from LastIDNumber WERE LID_KET_NM = 'TableName'Any suggestions would be greatly appreciated.
Thanks.
Suggestion #1: Ask a question.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 14, 2017 at 10:55 am
2 Possible options ...
DECLARE @SeedVal INT = ISNULL((SELECT LID_ID_NB from LastIDNumber WHERE LID_KET_NM = 'TableName'), 0) +1;
DBCC CHECKIDENT ( TableName, RESEED, @SeedVal ) WITH NO_INFOMSGS;
DECLARE @SeedVal INT = ISNULL((SELECT MAX(IntVal) from TableName), 0) +1;
DBCC CHECKIDENT ( TableName, RESEED, @SeedVal ) WITH NO_INFOMSGS;
February 14, 2017 at 11:29 am
John Mitchell-245523 - Tuesday, February 14, 2017 9:31 AMNot sure what your question actually is, but why don't you use a sequence?John
I did not create the LastNumber approach.
It was implanted 20 years ago.
I have to convert the Primary Indexes that are generated with an Identity colum on 44 TABLES,
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 14, 2017 at 12:21 pm
Welsh Corgi - Tuesday, February 14, 2017 11:29 AMJohn Mitchell-245523 - Tuesday, February 14, 2017 9:31 AMNot sure what your question actually is, but why don't you use a sequence?John
I did not create the LastNumber approach.
It was implanted 20 years ago.
I have to convert the Primary Indexes that are generated with an Identity colum on 44 TABLES,
It sounds like you've already answered your question; alter your tables to use an IDENTITY column.
Selecting the next sequence ID from a lookup table is cumbersome to code. The problem is that you must wrap a multi statement transaction around each insert, the TableName specified must be correct, and there is always that one guy on the development team who goofs it up.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 15, 2017 at 2:04 am
Eric M Russell - Tuesday, February 14, 2017 12:21 PMSelecting the next sequence ID from a lookup table is cumbersome to code. The problem is that you must wrap a multi statement transaction around each insert, the TableName specified must be correct, and there is always that one guy on the development team who goofs it up.
I agree. It looks as if my guess on what the question actually was was wrong. I thought he was looking for a way to make the central lookup table work.
John
February 15, 2017 at 7:42 am
John Mitchell-245523 - Wednesday, February 15, 2017 2:04 AMEric M Russell - Tuesday, February 14, 2017 12:21 PMSelecting the next sequence ID from a lookup table is cumbersome to code. The problem is that you must wrap a multi statement transaction around each insert, the TableName specified must be correct, and there is always that one guy on the development team who goofs it up.I agree. It looks as if my guess on what the question actually was was wrong. I thought he was looking for a way to make the central lookup table work.
John
Sequences are better than lookup tables, but it still relies on the developer to fetch the next ID from the correct sequence object, and then use it correctly. For sequential integer based identifiers, I rarely resort to anything other than an identity.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 15, 2017 at 9:35 am
Eric M Russell - Wednesday, February 15, 2017 7:42 AMJohn Mitchell-245523 - Wednesday, February 15, 2017 2:04 AMEric M Russell - Tuesday, February 14, 2017 12:21 PMSelecting the next sequence ID from a lookup table is cumbersome to code. The problem is that you must wrap a multi statement transaction around each insert, the TableName specified must be correct, and there is always that one guy on the development team who goofs it up.I agree. It looks as if my guess on what the question actually was was wrong. I thought he was looking for a way to make the central lookup table work.
John
Sequences are better than lookup tables, but it still relies on the developer to fetch the next ID from the correct sequence object, and then use it correctly. For sequential integer based identifiers, I rarely resort to anything other than an identity.
Yes, I agree. I'm trying to get rid of the mess.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 16, 2017 at 7:21 am
Eric M Russell - Tuesday, February 14, 2017 12:21 PMEric M Russell - Wednesday, February 15, 2017 7:42 AMJohn Mitchell-245523 - Wednesday, February 15, 2017 2:04 AMEric M Russell - Tuesday, February 14, 2017 12:21 PMSelecting the next sequence ID from a lookup table is cumbersome to code. The problem is that you must wrap a multi statement transaction around each insert, the TableName specified must be correct, and there is always that one guy on the development team who goofs it up.I agree. It looks as if my guess on what the question actually was was wrong. I thought he was looking for a way to make the central lookup table work.
John
Sequences are better than lookup tables, but it still relies on the developer to fetch the next ID from the correct sequence object, and then use it correctly. For sequential integer based identifiers, I rarely resort to anything other than an identity.
Sequences are better than lookup tables, but it still relies on the developer to fetch the next ID from the correct sequence object, and then use it correctly. For sequential integer based identifiers, I rarely resort to anything other than an identity.
One can just set the ID column default to (NEXT VALUE FOR <SequenceName>), which doesn't require developer to explicitly get/set ID in inserts -- same convenience as an identity.
But sequences are somewhat more of a pain to implement, & probably overkill here, since pool of IDs doesn't need to be shared among multiple tables.
Advantages of sequences, especially in a "dynamic" environment or with questionable data, is that the IDs can be updated, and removing, adding, or replacing sequence doesn't require table rebuild.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply