index increment in sql server

Question

1.00/5 (1 vote)

See more:

, +

i am using sql server 2008 for the database. i have to increment index manually whenever record is inserted or updated in table.for this first i search for the index from the table using select after this increment it by 1 and update the record in the table,so i have doubt that when more then 10 pc are connected via LAN or WAN at that time for updating or inserting record this will be work or not ? any other way to do this ?

Posted 12-Nov-12 21:27pm

Jigar Sangoi

Add a Solution

2 solutions

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)

Shanalal Kasim · Answer 1 · 2012-11-12T21:49:00

Solution 2

You can use IDENTITY [ ( seed , increment ) ] Property

Refer: IDENTITY (Property)[^]

Posted 12-Nov-12 21:49pm

Shanalal Kasim

Comments

Jigar Sangoi 13-Nov-12 4:45am

but problem is that i have to count total record inserted or updated for each day so for the next day it starts from previous index not from 0,also this has to be done when record is accepted

Shanalal Kasim 13-Nov-12 4:52am

so you want, every day index start with zero?

OriginalGriff · Answer 2 · 2012-11-12T21:42:00

Solution 1

You can set an automatic increment: Look at the column definition and find the property "Identity Specification"
Expand it, and set "(Is Identity)" to "Yes"

Posted 12-Nov-12 21:42pm

OriginalGriff

Comments

Jigar Sangoi 13-Nov-12 4:40am

but problem is that i have to count total record inserted or updated for each day so for the next day it starts from previous index not from 0

Jigar Sangoi 13-Nov-12 4:43am

also this has to be done when record is accepted

OriginalGriff 13-Nov-12 4:55am

:laugh:
You have to tell people these things!
There is no automatic way to do that, the best thing I can suggest is that you use a stored procedure as this will be performed at the server, and should not be interruptible unlike a "select...check...write" operation which as you rightly suspect will be subject to intermittent problems in a multiuser environment.

Personally, I would try to avoid this: I would be tempted to look at generating it "on the fly" by storing the insert date and time stamp and working it out on a daily basis from that if it was possible.