>>Hi,
>>
>>I have a table in SQL Server that currently has NO rows/records. There is a PK column set to Identity Yes. I want to make this column values to start with 0 (zero). Here is what I did:
>>1. Set Identity of this column to Off
>>2. Added a record/row to this column and set the value to 0 (zero).
>>3. Set Identity of this column back to On
>>
>>Now when I add a new record, SQL Server sets the value to this column as 2.
>>
>>How can I make it start with 1 (after I already added a record of 0)?
>>
>
>Here's what you "can" try...
>
>
http://blog.sqlauthority.com/2007/03/15/sql-server-dbcc-reseed-table-identity-value-reset-table-identity/>
>Having said that, this reminds me of conversations I had with my father. I'd ask, "Dad, can I do such and such?" His reply, "You could....but it would be wrong"
>
>Unless there is some compelling reason, I don't recommend this. Identity values aren't supposed to have meaning. The only exception...sometimes you might have a sale for an undefined cost center. Instead of storing a NULL in the foreign key value in the fact table, I'll have an "undefined/unclassified" cost center in the cost center master, with an identity of -1....and store that in the fact table. That's about the only time I suspend identity generation...just long enough to jam a dummy -1 value into the master.
>
>But beyond that, I leave identity generation and seeding alone.
Thank you again for the link to sqlauthority web site. It works very well. Now my Site Numbers are zero-based. And if DBA asks, I will tell him/her that I am following MS logic which has many entities zero-based.
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham