IDENTITY column?

I'm using an IDENTITY column (datatype = integer) for the first time and
am not quite sure it's what I want. I'm deleting rows from its table
quite often. I'd like a way so that when my stored procedure inserts
rows I can first issue a separate command that tells the column to
restart to a value of 1, and then the INSERTs will actually insert the
1, then 2, then 3...
I looked at setting IDENTITY_INSERT *on* but I see this simply allows
you to manually set the value right in the INSERT statement which is not
what I want. I want to be able to have two separate processes:
-- restart the numbering to 1 on this column
-- ok, now here's my INSERT statements that will actually insert 1, then
2, then 3...
Is this possible? Thanks.Lookup DBCC CHECKINDENT in Books On Line
DBCC CHECKIDENT (TAbleNAme, RESEED, 1)
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Rick Charnes wrote:
> I'm using an IDENTITY column (datatype = integer) for the first time and
> am not quite sure it's what I want. I'm deleting rows from its table
> quite often. I'd like a way so that when my stored procedure inserts
> rows I can first issue a separate command that tells the column to
> restart to a value of 1, and then the INSERTs will actually insert the
> 1, then 2, then 3...
> I looked at setting IDENTITY_INSERT *on* but I see this simply allows
> you to manually set the value right in the INSERT statement which is not
> what I want. I want to be able to have two separate processes:
> -- restart the numbering to 1 on this column
> -- ok, now here's my INSERT statements that will actually insert 1, then
> 2, then 3...
> Is this possible? Thanks.|||Thanks much. Is there another way to do what I'm trying to do? I'm not
sure my DBA will be happy with me using DBCC -- the user running the
proc may not have permissions.
In article <1149690037.334461.34780@.i40g2000cwc.googlegroups.com>,
denis.gobo@.gmail.com says...
> Lookup DBCC CHECKINDENT in Books On Line
> DBCC CHECKIDENT (TAbleNAme, RESEED, 1)
>|||Yes use TRUNCATE TableName instead of DELETE TableName
It is faster and will also reset the identity, however you need to have
db_owner or db_ddladmin privileges to use TRUNCATE
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Rick Charnes wrote:
> Thanks much. Is there another way to do what I'm trying to do? I'm not
> sure my DBA will be happy with me using DBCC -- the user running the
> proc may not have permissions.
> In article <1149690037.334461.34780@.i40g2000cwc.googlegroups.com>,
> denis.gobo@.gmail.com says...|||DBCC requires a lower level of permissions than TRUNCATE, then? I think
of DBCC as something run by DBA's and not so commonly used in daily
business-related stored procedures run by users -- am I wrong?
I guess I was wondering if there's another way to do this other than
using IDENTITY -- a way to automatically insert (and increment) a value
into a table without having to include it in your INSERT statement.
Your help is much appreciated.
In article <1149691117.373637.158280@.i39g2000cwa.googlegroups.com>,
denis.gobo@.gmail.com says...
> Yes use TRUNCATE TableName instead of DELETE TableName
> It is faster and will also reset the identity, however you need to have
> db_owner or db_ddladmin privileges to use TRUNCATE
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/|||> I guess I was wondering if there's another way to do this other than
> using IDENTITY -- a way to automatically insert (and increment) a value
> into a table without having to include it in your INSERT statement.
If you want the system to generate a number for you, why do you care so much
what it is, or where it starts at?
Anyway, there are ways to do this, like using a central sequence table,
wrapping a function around it, and setting the default value in the column
in your table to be the result of the function. But you will have to lock
the central table, which will serialize inserts, and may have serious
performance implications. Have a look at these conversations for some
ideas:
http://tinyurl.com/lnm5n|||> DBCC requires a lower level of permissions than TRUNCATE, then?
No, same same, see below two quotes from Books Online:
DBCC CHECKIDENT permissions default to the table owner, members of the sy
min fixed server role,
and the db_owner and db_ddladmin fixed database role, and are not transferab
le.
TRUNCATE TABLE permissions default to the table owner, members of the sym
in fixed server role,
and the db_owner and db_ddladmin fixed database roles, and are not transfera
ble.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rick Charnes" <rickxyz--nospam.zyxcharnes@.thehartford.com> wrote in message
news:MPG.1ef0b3eff9279e84989940@.msnews.microsoft.com...
> DBCC requires a lower level of permissions than TRUNCATE, then? I think
> of DBCC as something run by DBA's and not so commonly used in daily
> business-related stored procedures run by users -- am I wrong?
> I guess I was wondering if there's another way to do this other than
> using IDENTITY -- a way to automatically insert (and increment) a value
> into a table without having to include it in your INSERT statement.
> Your help is much appreciated.
> In article <1149691117.373637.158280@.i39g2000cwa.googlegroups.com>,
> denis.gobo@.gmail.com says...