Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
abhijitheie
Starting Member
2 Posts |
Posted - 2013-01-29 : 02:36:59
|
Hi,I have a column with name "CustomerNumber". I want to get increment the max value of this column by 1 and then return me the incremented number. I know we have to do this through stored procedures and sequence generators. but im not sure how exactly. please explain. |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-29 : 02:46:15
|
the best way to deal with this is to make the column an identity one so that values will get incremented automaticallyIf you want to simulate it,useDECLARE @MaxID intSELECT @MaxID=MAX(CustomerNumber)FROM CustomersSELECT COALESCE(@MaxID,0) + 1 AS NextIDValue please be aware that for batch insertions you've to generate bunch of new id values and above method wont work.For that you need to make use of a temporary table with identity column itself.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2013-01-29 : 21:24:30
|
It's highly likely you really want to use an "Identity" column for this, or, in SQL 2012, an actual SEQUENCE object which is even better. If you intend to use the number and write it back then be aware that Visakah's method only really works reliably for single user. With multiple users (connections actually) you either end up gummed up serialised behind locks or reusing numbers depending on your isolation model. |
|
|
abhijitheie
Starting Member
2 Posts |
Posted - 2013-01-30 : 00:23:17
|
LoztInSpace,Can u explain how to do it step by step? |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2013-01-30 : 07:06:16
|
http://blogs.msdn.com/b/askjay/archive/2012/10/08/sequence-objects-in-sql-2012-and-sql-2008.aspxHow to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
|
|