How to increament value

Question

1.00/5 (3 votes)

See more:

SQL

Declare @UID varchar(30)
Declare @UserId varchar(30)
Set @UID='0'
Set @UID= @UserId
SET @UserId = @UID+1;
Select @UserId as u

What I have tried:

i want to know how to do on every execute value increase

like
1
2
3
i do not want to use identity i want this above query through increment
so please help me

Posted 1-Apr-17 6:27am

Member 12183079

Updated 4-Apr-17 2:37am

PIEBALDconsult

v2

Add a Solution

Comments

PIEBALDconsult 1-Apr-17 12:54pm

If Oracle or a recent version of SQL Server, use a Sequence.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2017-04-01T06:33:00

Solution 1

Don't do it yourself, use IDENTITY. I know, I know, you don't want to - but it makes life a whole lot easier and safer. The problem is that SQL server and MySql are both multiuser systems, and it you try to "do it yourself" you have to bear in mind at at any time a different user could also be trying to increment the same value - if you don't then you start to get really nasty intermittent errors which are next to impossible to spot, let alone fix!

Using IDENTITY means that SQL works it out for you, and handles all the multiuser stuff transparently behind the scenes.

And you should never SELECT a "next value" because it may not be valid when the user INSERTs the row - pre-selecting ID's is a dangerous thing to do, and can really mess up your DB if you aren't really, really careful.

Posted 1-Apr-17 6:33am

OriginalGriff

Comments

Member 12183079 1-Apr-17 12:37pm

can you please without using identity how to do increment so please help

Dave Kreskowiak 1-Apr-17 13:30pm

It can be done, but isn't done because it doesn't scale well. It's not a simple "oh just do this in your SP and you're done". This takes a lot of consideration of what you're doing, how you're doing it, what happens in the event of a rollback, multiple transactions and locks, and configuration of the database to support.

Doing this involves locking the entire table. That means only ONE user can do anything with the table at a time, that includes running a SELECT on it. While the table is locked for this update nobody else can read it!

If you've got 1,000 simultaneous users, only one gets to touch the table at a time. The rest have to wait until the transaction is committed and locks are released. This is what is meant by "it doesn't scale well".

PIEBALDconsult 1-Apr-17 12:59pm

Identity is filth. Never use them.
Microsoft has since learned from Oracle and given us Sequences.