Question regarding Identitiy field

Guys,
I have a identity flag set on a column. When i am testing the system,
inserting records the identity goes all the way to 60 and so on. I need to
put the table in production system now. However i need to reset the identity
field to start from 1. How can i clear the identity field and make it start
from the begining.
Thanks
MannyUse this
DBCC CheckIdent('TableName')
"Manny Chohan" wrote:

> Guys,
> I have a identity flag set on a column. When i am testing the system,
> inserting records the identity goes all the way to 60 and so on. I need to
> put the table in production system now. However i need to reset the identi
ty
> field to start from 1. How can i clear the identity field and make it star
t
> from the begining.
> Thanks
> Manny|||I'm not sure why you care about the starting value but you can either
truncate the table or execute DBCC CHECKIDENT with the RESEED option.
Hope this helps.
Dan Guzman
SQL Server MVP
"Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
news:6C4D0EBE-7AA5-4F18-9EAA-ED2547B1BD23@.microsoft.com...
> Guys,
> I have a identity flag set on a column. When i am testing the system,
> inserting records the identity goes all the way to 60 and so on. I need to
> put the table in production system now. However i need to reset the
> identity
> field to start from 1. How can i clear the identity field and make it
> start
> from the begining.
> Thanks
> Manny|||Dan,
Just fyi, dbcc checkident defaults to reseed... so,
checkident ('TableName')
is equivilent to
checkident ('TableName', RESEED)
"Dan Guzman" wrote:

> I'm not sure why you care about the starting value but you can either
> truncate the table or execute DBCC CHECKIDENT with the RESEED option.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
> news:6C4D0EBE-7AA5-4F18-9EAA-ED2547B1BD23@.microsoft.com...
>
>|||Thanks All
"CBretana" wrote:
> Dan,
> Just fyi, dbcc checkident defaults to reseed... so,
> checkident ('TableName')
> is equivilent to
> checkident ('TableName', RESEED)
> "Dan Guzman" wrote:
>|||I am with Dan all the way on this one. Why do you care about the value of
the identity? I admit that I would probably want to reset it myself when
going into production, just because it looks more "tidy." It is however,
always concerning when someone states that they want to know how to do this
because it often means they are using these values in some manner where the
user will care about the value, and identities are pretty bad for this sort
of thing (users HATE gaps, and gaps are just generally part of the identity
experience, since row with the identity property cannot be updated (ever).)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
news:6C4D0EBE-7AA5-4F18-9EAA-ED2547B1BD23@.microsoft.com...
> Guys,
> I have a identity flag set on a column. When i am testing the system,
> inserting records the identity goes all the way to 60 and so on. I need to
> put the table in production system now. However i need to reset the
> identity
> field to start from 1. How can i clear the identity field and make it
> start
> from the begining.
> Thanks
> Manny|||In theory, I agree 100% with you and Dan on this... I do not believe the
value of any surrogate key, much less an Identoty, should be significant to
anyone...
However, in practice, simply out ofa sense of esthetics, I find myself doing
exactly the same thing this gentleman asked about...
In theory, if we really didn't care about the actual values, we wouldn't
always set the seed value for Identity columns = 1, we'd set it to the lowes
t
legal value for the underlying datatype, (TinyInt, SmallInt, Int).
(Either zero, (0), -32,768, or -2,147,483,648, respectively)
But practically, we do care what the value is, because in coding, and
debugging, and manipulating the data in Query Analyzer, we use these values,
and so they do matter.
"Louis Davidson" wrote:

> I am with Dan all the way on this one. Why do you care about the value of
> the identity? I admit that I would probably want to reset it myself when
> going into production, just because it looks more "tidy." It is however,
> always concerning when someone states that they want to know how to do thi
s
> because it often means they are using these values in some manner where th
e
> user will care about the value, and identities are pretty bad for this sor
t
> of thing (users HATE gaps, and gaps are just generally part of the identit
y
> experience, since row with the identity property cannot be updated (ever).
)
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
> news:6C4D0EBE-7AA5-4F18-9EAA-ED2547B1BD23@.microsoft.com...
>
>|||That was pretty much what I meant too. It is really a weird thing too.
Sure when we start out programming it is easier to have the value start out
at 1, since it is easier to type while doing initial programming, but by all
logic if we really mean that we don't care about the value then starting
at -minvalue would be better (if we are lucky our values will get that big
anyhow!)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:7EF6C6ED-EE40-4AA2-8173-8A99226E0CEA@.microsoft.com...
> In theory, I agree 100% with you and Dan on this... I do not believe the
> value of any surrogate key, much less an Identoty, should be significant
> to
> anyone...
> However, in practice, simply out ofa sense of esthetics, I find myself
> doing
> exactly the same thing this gentleman asked about...
> In theory, if we really didn't care about the actual values, we wouldn't
> always set the seed value for Identity columns = 1, we'd set it to the
> lowest
> legal value for the underlying datatype, (TinyInt, SmallInt, Int).
> (Either zero, (0), -32,768, or -2,147,483,648, respectively)
> But practically, we do care what the value is, because in coding, and
> debugging, and manipulating the data in Query Analyzer, we use these
> values,
> and so they do matter.
>
> "Louis Davidson" wrote:
>|||Manny,
you have two options:
1. use DBCC CHECKIDENT statement with RESEED option (see Books OnLine),
2. use TRUNCATE TABLE on your table (however THIS WILL DELETE ALL ROWS IN A
TABLE WITHOUT ROLLBACK OPTION!)
Pawel
"Manny Chohan" wrote:

> Guys,
> I have a identity flag set on a column. When i am testing the system,
> inserting records the identity goes all the way to 60 and so on. I need to
> put the table in production system now. However i need to reset the identi
ty
> field to start from 1. How can i clear the identity field and make it star
t
> from the begining.
> Thanks
> Manny|||> 2. use TRUNCATE TABLE on your table (however THIS WILL DELETE ALL ROWS IN
> A
> TABLE WITHOUT ROLLBACK OPTION!)
TRUNCATE does allow a ROLLBACK. However, like other SQL data modification
statements, the transaction will need to be started explicitly unless
IMPLICIT_TRANSACTIONS is on.
CREATE TABLE MyTable(Col1 int NOT NULL)
INSERT INTO MyTable VALUES(1)
BEGIN TRAN
TRUNCATE TABLE MyTable
SELECT Col1 FROM MyTable
ROLLBACK
SELECT Col1 FROM MyTable
Hope this helps.
Dan Guzman
SQL Server MVP
"Pawel Potasinski" <PawePotasiski@.discussions.microsoft.com> wrote in
message news:11BF931F-D7BC-41A3-B0FD-E2F85943B14B@.microsoft.com...
> Manny,
> you have two options:
> 1. use DBCC CHECKIDENT statement with RESEED option (see Books OnLine),
> 2. use TRUNCATE TABLE on your table (however THIS WILL DELETE ALL ROWS IN
> A
> TABLE WITHOUT ROLLBACK OPTION!)
> Pawel
> "Manny Chohan" wrote:
>