Column Name Identity Setting Yes....

Posted by Shoyebaziz123 under Sql Server on 3/3/2011 | Points: 10 | Views : 1715 | Status : [Member] | Replies : 4
Hi All,

i make a table let ABC in this table i am giving first column StudentID(int) setting primary key and also Is Identity =yes , second column Name(varchar),
Now i have inserting some values in the table (i.e only Name and StudentId is Autoincremented).

my problem is that i am deleting all row ...now i am adding new row but StudentId doest not start with 1... it start last value (say 15) ... how can i set when i am deleting all record
it must be start with 1 ...

Thanks.




Responses

Posted by: Prabhakar on: 3/3/2011 [Member] [MVP] Starter | Points: 25

Up
0
Down
Hi Shoyebaziz123

i think it's default setting in sqlserver . they are not changed . . may be try it . .

Best Regard's
Prabhakar

Shoyebaziz123, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: T.saravanan on: 3/3/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi,

Try this..
Using delete record only deleted identity value not set into '1'.
so using Truncate...
Truncate Table TableName

After truncate column start with '1'.
else you set the identity value with out truncate means execute the following code...
DBCC CheckIdent( 'TableName',Reseed,1)


Cheers :)

Thanks,
T.Saravanan

Shoyebaziz123, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Karthikanbarasan on: 3/3/2011 [Member] [Moderator] [Microsoft_MVP] [MVP] Silver | Points: 25

Up
0
Down
Hi,

Check these links... You will get a clear idea on how to reset the identity.

http://blog.sqlauthority.com/2007/03/15/sql-server-dbcc-reseed-table-identity-value-reset-table-identity/

http://geekswithblogs.net/TakeNote/archive/2007/11/30/117258.aspx

Thanks
Karthik
www.f5Debug.net

Shoyebaziz123, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Madhu.b.rokkam on: 3/3/2011 [Member] [MVP] Bronze | Points: 25

Up
0
Down
If you want to the index to start from 11 then

DBCC CHECKIDENT('TABLE NAME', RESEED, 10)

try this..

Thanks and Regards
Madhu

Shoyebaziz123, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response