have a table set one column as
data type = int
identity to yes
identity seed = 1 and identity increment = 1
after deleting some rows (e.g. 5), the number 5 will never be re used.
when the number has grown to the limit, can I still insert new rows?when you mention a limit, I am assuming you are talking about a check
constraint.
Regardless, identity column values are not necessary inserted in the order
of sequence, however the seed will always be no lower than the last
committed value.
BR,
Mark Broadbent mcdba,mcse+i
_________________________
"Music Lover" <music@.my-heart.org> wrote in message
news:OTtGn5$VDHA.1680@.tk2msftngp13.phx.gbl...
> have a table set one column as
> data type = int
> identity to yes
> identity seed = 1 and identity increment = 1
> after deleting some rows (e.g. 5), the number 5 will never be re used.
> when the number has grown to the limit, can I still insert new rows?
>
>|||No he means what happens when the INT field gets to 0x7FFFFFFF (2147483647)
"Mark Broadbent" <nospamplease_mark.broadbent@.virgin.net> wrote in message
news:VnpWa.582$k4.11506@.news2.nokia.com...
> when you mention a limit, I am assuming you are talking about a check
> constraint.
> Regardless, identity column values are not necessary inserted in the order
> of sequence, however the seed will always be no lower than the last
> committed value.
>
> --
> BR,
> Mark Broadbent mcdba,mcse+i
> _________________________
> "Music Lover" <music@.my-heart.org> wrote in message
> news:OTtGn5$VDHA.1680@.tk2msftngp13.phx.gbl...
> > have a table set one column as
> > data type = int
> > identity to yes
> > identity seed = 1 and identity increment = 1
> >
> > after deleting some rows (e.g. 5), the number 5 will never be re used.
> >
> > when the number has grown to the limit, can I still insert new rows?|||Little Test...
USE PUBS
CREATE TABLE dbo.tblIdentity ( ID INT IDENTITY(2147483640,1) CONSTRAINT
PK_tblIdentity PRIMARY KEY CLUSTERED, DATA CHAR(1) )
GO
INSERT INTO tblIdentity ( DATA )
SELECT 'A'
UNION ALL SELECT 'B'
UNION ALL SELECT 'C'
UNION ALL SELECT 'D'
UNION ALL SELECT 'E'
UNION ALL SELECT 'F'
UNION ALL SELECT 'G'
UNION ALL SELECT 'H'
UNION ALL SELECT 'I'
UNION ALL SELECT 'J'
GO
SELECT * FROM tblIdentity
GO
DROP TABLE dbo.tblIdentity
The Insert query generates the following Error:-
Server: Msg 8115, Level 16, State 1, Line 1
Arithmetic overflow error converting IDENTITY to data type int.
Arithmetic overflow occurred.
So to answer your question.
No you can't insert records when the INT field reaches its limit
(2147483647)
When this happens you will have to reseed the field to -2147483648.
This will give you a few million more records...
When you run out of numbers after going through the negative values it'll be
time to a) archive some data b) change the number to a GUID or c) change the
number to a BIGINT. But 2147million records is enough for most people ;)
DBCC CHECKIDENT ( tblIdentity , RESEED , -2147483648 )
"Music Lover" <music@.my-heart.org> wrote:
> have a table set one column as
> data type = int
> identity to yes
> identity seed = 1 and identity increment = 1
> after deleting some rows (e.g. 5), the number 5 will never be re used.
> when the number has grown to the limit, can I still insert new rows?
>
>|||you can reset the identity seed to start incrementing from
your chosen number...
>--Original Message--
>have a table set one column as
>data type = int
> identity to yes
>identity seed = 1 and identity increment = 1
>after deleting some rows (e.g. 5), the number 5 will
never be re used.
>when the number has grown to the limit, can I still
insert new rows?
>
>.
>|||And bigint can store -9,223,372,036,854,775,808 through
9,223,372,036,854,775,807.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Tony Wilton" <tony@.scruffytiger.co.uk.NOSPAM> wrote in message
news:3f2a3824$0$12152$7b0f0fd3@.mistral.news.newnet.co.uk...
> If you use an INT field yes.
> Maximum values for data types are:-
> TINYINT 255
> SMALLINT 32767
> INT 2147483647
>
> "Music Lover" <music@.my-heart.org> wrote:
> > Thanks for your reply.
> > Want to double confirm
> > I can insert 2147million records withou any problem?
>|||thanks
how?
"jano" <janobermudes@.microsoft.com> wrote in message
news:09a801c35812$d7540810$a401280a@.phx.gbl...
> you can reset the identity seed to start incrementing from
> your chosen number...
>
> >--Original Message--
> >have a table set one column as
> >data type = int
> > identity to yes
> >identity seed = 1 and identity increment = 1
> >
> >after deleting some rows (e.g. 5), the number 5 will
> never be re used.
> >
> >when the number has grown to the limit, can I still
> insert new rows?
> >
> >
> >
> >.
> >
Showing posts with label deleting. Show all posts
Showing posts with label deleting. Show all posts
Monday, March 19, 2012
Wednesday, March 7, 2012
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 sy
m
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...
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 sy
min 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...
Subscribe to:
Posts (Atom)