get nextid for non-identity column
-
hi
how do i get the nextid for a non-identity column other then taking the max value and adding 1 to it
any help is appreciated
-
If this is a serious question you need to reword it and give more detail.
There is not enough information here, as it stands you have not stated any context to allow any answer to be given.
Please give some example data.
Regards
Shaun McGuile
'Fix the problem, not the blame!'
Hiding under a desk from SSIS Implemenation Work :crazy:
-
Unless you mean to not use the MAX function
and use something like;
DECLARE @NEXTID
SELECT @NEXTID = (SELECT TOP 1 [id_Column] FROM <table> ORDER BY [id_Column] DESC) + 1
to get the value.
Sounds like a home work question.
If so you failed, otherwise you need to read a good T-SQL book and practice your craft.
Regards
Shaun McGuile
Hiding under a desk from SSIS Implemenation Work :crazy:
-
"Sounds like a home work question.
If so you failed, otherwise you need to read a good T-SQL book and practice your craft":
Shaun,
It is posts like yours that stop newbies from asking for help on these forums. If the question is too trivial for you to answer politely then I suggest you go and look for some harder problems to solve.
Please note your solution does not work in a multi-user environment.
-
andyb
There is a difference between asking for help and having someone do your work for you.
One thing that everyone should learn is how to phrase a question with sufficient detail so that a concise answer can be given.
To be a DBA requires a certain level of ability - even to start on the path - asking for help when under pressure of work because you haven't time to pull the manual off the shelf is fine.
But not to have exaughsted all other avenues first (e.g. reading the manual(s)) is not acceptable.
I am one of the most friendliest, tolerant people on this planet, but laziness irks me somewhat.
What does a multi-user environment have to do with it?
My question still stands about the origional post - is the question about not using the MAX function or something entirely different?
regards,
Shaun McGuile
Hiding under a desk from SSIS Implemenation Work :crazy:
-
Oh I'm just getting started here.
andyb: Whats your solution?
Your comments have been non constructive and have not answered the origional post.
My solution works, no matter if used in a multi-user environment or not.
It gets the record containing the highest value in the column adds one to it and puts it into a variable.
No more, no less.
There is no mention of field type in the question nor what is to be done with the value once obtained - adding one to the field type limit will cause an overflow exception. E.g. for a tinyint max value is 255 adding one would set the value to 256 if the variable was then used as the value for a new record an exception would happen.
Please think about your comments before posting
Hiding under a desk from SSIS Implemenation Work :crazy:
-
I think the OP wants the functionality of an identity column without an identity column. I strongly recommend to convert the table via 'insert into select...' preceeded by 'set identity_insert on table-name'.
An alternative is to use a table with one row and a single int column. Lock it when you read and update this value to get the next sequential value. But I dont' know how this will behave with a 1000 users hitting return simultaneously.
-
Michael M: your latter suggestion is a possibilty.
Wrapping it up in a single transaction i.e. get the value update it and then use it via an insert trigger would surely be the way to go, would this not avoid any concurrent usage issues?
My origional question still stands why mess about like this in the first place?
It would be nice if the Susane actually contributed something more to this thread, don't you think?
Regards,
Shaun McGuile
Hiding under a desk from SSIS Implemenation Work :crazy:
-
Shaun, your solution does not mimic an identity column in a multi-user environment,
i.e. it does not guarantee uniqueness - two users requesting the nextID value at the same time could get the same value. In order to mimic a true identity column you will need to make sure that until the next value is created no-one can get hold of the original value i.e by wrapping it in a transaction and holding a lock until the next id value is created.
For example,
/*create table mytable
(id int primary key not null
)*/
GO
set xact_abort on
DECLARE @newid int
BEGIN TRAN
/*SELECT @newid =COALESCE(MAX(id),0) FROM mytable WITH (UPDLOCK,HOLDLOCK) -- using MAX*/
SELECT TOP 1 @newid = id FROM mytable WITH (UPDLOCK,HOLDLOCK) ORDER BY id DESC -- Not Using MAX
INSERT INTO mytable (id) SELECT COALESCE(@newid,0)+1
COMMIT TRAN
--select * from mytable order by 1
/*drop table mytable*/
Please note however that performance could be a problem with this solution depending upon the number of concurrent users / size of the table. Using an identity column instead would scale better.
-
Just a thought;
Is it that each insert is implicitly wrapped in a transaction thus multiple requests will be qued and not simultaneous?
Thus does -
INSERT INTO mytable ([id]) SELECT ((SELECT TOP 1 [id] FROM mytable] ORDER BY [id] DESC)+1)
not meet the requirements of the OP?
Please explain to me why you used the COALESCE function?
--
Shaun
Hiding under a desk from SSIS Implemenation Work :crazy:
-
You will still need the locking hints in order to avoid duplicates, i.e
INSERT INTO mytable ([id]) SELECT coalesce(((SELECT TOP 1 [id] FROM mytable WITH (UPDLOCK,HOLDLOCK) ORDER BY [id] DESC)+1),1)
The coalesce is used for the initial value i.e. when there is no data in the table.
-
The problem with selecting either the MAX or the TOP 1 DESC, even in the presence of the read locks that Andy included in the code, is that you only need one "smart guy" that slips in a WITH (NOLOCK) or sets the transaction isolation level to "READ UNCOMMITTED" or forgets to establish the read locks and BOOM! You can certainly prevent dupes by making the ID column either the Primary Key or a Unique Key, but if someone makes one of the previously mentioned "mistakes", someone's app is going to go BOOM on the insert. And none of these methods will prevent someone from inserting the maximum value of an INT (or whatever the datatype of the column is) and messing up the whole works.
When it's not an autonumbering IDENTITY column, these types of columns are called "Sequences" and if you don't think they're a bit of a hit on performance, consider that even the almighty ORACLE recommends setting up a "cache" of sequence numbers to improve performance.
If you really can't use an IDENTITY column, then put something like Andy's or Michael's method in an Instead-Of trigger (hate 'em) and you're done (most closely mimics an IDENTITY column). Or, you can make a function using Andy's method and then tell everyone to use the function in all Inserts.
As a side bar, if you do use Michael's method (affectionately known as a "Sequence Table"), then make sure that you use the MS SQL Server proprietary form of UPDATE to ensure that both the write of the new number and the return in the variable occur at the same time without an explicit transaction in a single query to prevent the inevitable deadlocks that will occur using any other method. The general form of that proprietary UPDATE is...
UPDATE yourtable
SET @variable = columnname = formula
The real key is that unless you use an IDENTITY column, you can (and probably will) experience all the same problems that Oracle's sequence.NextVal experiences including slightly slower batch inserts and people forgetting to use the sequencer (unless the trigger method is used).
--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) -
Jeff,
thanks for the clarity.
--
Shaun
Hiding under a desk from SSIS Implemenation Work :crazy:
-
You bet... 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) -
Interesting conversation. I'm in a situation where this applies. Has a solution been decided upon?
I am a .Net developer and do not have the luxury of a dba. I inherited an application where the primary key is a 6 digit number, but is stored as a string.
I need to find the Max(id) + 1 and insert a new row with Max(id) + 1, thus redefining Max(id).
I am worried about 2 users hitting the stored procedure and the sequence getting mixe up.
Ideas?
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply