Identitiy Seed and auto increment fields

jjc9809
jjc9809 used Ask the Experts™
on
Hi,I have a question about adding records to the SQL Server 2005 database.  I have an ID field that automatically increments.  When I first add records to the table, it is placing a "0" in this field.  Why doesn't the field start at 1 in lieu of 0?  Other people have added records to the table, but this is my first time in using my name on the login.  When I add the record the record shows the ID as the next incremented number which is 163 when it is saved.I came out and came back in and now enter a record it shows the next incremented number when I add the record as 164.Just want to understand what is happening
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009

Commented:
> Why doesn't the field start at 1 in lieu of 0?  

may be ur startvalue is 0 instead of 1

declare @Tab table ( i int identity (0,1) , name varchar(10)  )  --change 0 to 1 and run again

insert into @Tab select 'abc'

select * from @Tab
AneeshDatabase Consultant
Top Expert 2009

Commented:
identity values keeps on incrementing based on the identity seed

http://www.sqlteam.com/article/understanding-identity-columns

Author

Commented:
No I have set the Identity Seed to 1 in lieu of 0 in the properties section of the ID field in the server design area for this field.  It is set to automtically increment by 1.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

AneeshDatabase Consultant
Top Expert 2009

Commented:
in that case someone explicitly inserted the '0' in that column
$can u generate the Table script and paste it here ?

Author

Commented:
No there has been no one explictly entering anything.  When the "+" Icon is pressed the ID field shows a 0  when a person logged in that has never entered records into the database the first time tries to add a record.  When the drop down boxes insert data into the fields by the user and the save icon is clicked the increment shows the record added and the ID field becomes 164 in lieu of 0 then.and the

There is no problem with this in the application.  The application works fine, I was just curious as why the 0 showed in the ID field when the "+" is clicked and when the drop down boxes insert data into the combo boxes and the save is clciked the 0 becomes 164.  I understand what it is doing here.  The 164 is the record the user is adding now.  The 163 record was added by another user.  But why does the 0 show?  That is my question.  Why does it show this?  

The gridview for that record shows 164 in the ID field also.
AneeshDatabase Consultant
Top Expert 2009

Commented:
>But why does the 0 show?  That is my question.  Why does it show this?  
is it thru the sql server management studio ? or thru the application ? you cannot manually enter a value from the ssms visual tool into an identity column,

Author

Commented:
That is correct.  Let me explain the application.  The application is a help desk tool that keeps track of call issues that come in.  I have setup a database in the SQL Server 2005 that contains a Call Issue Table that has an ID field which is set as a Primary Key field. This field automatically increments each time a new record is added.   I have set the identity seed to increment by 1 each time a record is added.  The application runs and the data entry form pulls up.  The user clicks the "+" sign to add a new record.  The form when it pulls up has a query that runs and pulls records that are less than six days old that are open and not closed based on the date added.  All open records have a 0 status.  All closed records have a 1 status.

Anyway, the Jack M user logs in as technician and takes a call.  He has never been into the database before.  So there are no records coming in for that user because there are no records for that user in the database.  John Doe user is the last one in and enters his record at ID 163.  John Doe has 10 records he has added and they all show pulled based on the date added the last six days.

Jack M has no records shown and he is adding his first record to the database and clicks the "+" sign.  The ID Field shows a 0 there and in the Grid View for the ID field.  Now when Jack M uses the drop down boxes on the combo boxes to insert a call issue, resolution, resolution time, and clicks the save button, the ID field changes from 0 to 164 because the next increment is 164 since John Doe has added 163 before Jack M came in.

The ID also changes from 0 to 164 in the gridview and the record adds fine with call issue, resolution, resolution time, date added and so forth.

I was just puzzled as to why the 0 shows thats all.  There is no problem with the application.  Maybe VB works this way and knows by placing a 0 there.  I do not know.

Can you expalin this?  I hope this clears up the misunderstanding.  The application is running showing the 0 when the "+" is clicked when there are no records for the user present.  When records are present say for john doe, the ID field shows the 163, and not 0.

jjc9809
Database Consultant
Top Expert 2009
Commented:
>I was just puzzled as to why the 0 shows thats all.  There is no problem with the application.  Maybe VB works this way and knows by placing a 0 there.  I do not know.


its the application which is allowing you to enter the value (the default value 0 ) , once it saves the data into sql server, it seems like fetching the value back ans showing properly in the grid. seems like its just a display issue, may be they forget to make that colum read only
 
 
Hi

Looks like the app has code in it that makes the JobId availible for the insert of related data, and the developer didn't want to have null's or blank values during insert so as not to violate integrity rules.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial