Dear All,
what happens if IDENTITY value reaches the max value?
for example if we decide for a table to have an integer and the counter
exceeded the maximmum value how whould it reacte
Rami,> what happens if IDENTITY value reaches the max value?
You will get an overflow error.
> for example if we decide for a table to have an integer and the counter
> exceeded the maximmum value how whould it reacte
You'll need to create a new table with the desired schema (e.g. bigint for
the IDENTITY column) and insert data into the new table with IDENTITY_INSERT
turned on. For example:
CREATE TABLE OldTable
(
IdentityColumn int NOT NULL IDENTITY(1, 1),
OtherData int NOT NULL
)
INSERT INTO OldTable (OtherData) VALUES(1)
INSERT INTO OldTable (OtherData) VALUES(2)
INSERT INTO OldTable (OtherData) VALUES(3)
GO
CREATE TABLE NewTable
(
IdentityColumn bigint NOT NULL IDENTITY(1, 1),
OtherData int NOT NULL
)
GO
SET IDENTITY_INSERT NewTable ON
GO
INSERT INTO NewTable (IdentityColumn, OtherData)
SELECT IdentityColumn, OtherData FROM OldTable
GO
SET IDENTITY_INSERT NewTable OFF
GO
DROP TABLE OldTable
GO
EXEC sp_rename 'NewTable', 'OldTable'
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Rami" <Rami@.discussions.microsoft.com> wrote in message
news:7D17A95A-3CD0-4786-A718-D34223BE1686@.microsoft.com...
> Dear All,
> what happens if IDENTITY value reaches the max value?
> for example if we decide for a table to have an integer and the counter
> exceeded the maximmum value how whould it reacte
> Rami,|||Dear Dan Guzman,
It is working fine Thanks alot Dan
Best wishes
Rami,
"Dan Guzman" wrote:
> > what happens if IDENTITY value reaches the max value?
> You will get an overflow error.
> > for example if we decide for a table to have an integer and the counter
> > exceeded the maximmum value how whould it reacte
> You'll need to create a new table with the desired schema (e.g. bigint for
> the IDENTITY column) and insert data into the new table with IDENTITY_INSERT
> turned on. For example:
> CREATE TABLE OldTable
> (
> IdentityColumn int NOT NULL IDENTITY(1, 1),
> OtherData int NOT NULL
> )
> INSERT INTO OldTable (OtherData) VALUES(1)
> INSERT INTO OldTable (OtherData) VALUES(2)
> INSERT INTO OldTable (OtherData) VALUES(3)
> GO
> CREATE TABLE NewTable
> (
> IdentityColumn bigint NOT NULL IDENTITY(1, 1),
> OtherData int NOT NULL
> )
> GO
> SET IDENTITY_INSERT NewTable ON
> GO
> INSERT INTO NewTable (IdentityColumn, OtherData)
> SELECT IdentityColumn, OtherData FROM OldTable
> GO
> SET IDENTITY_INSERT NewTable OFF
> GO
> DROP TABLE OldTable
> GO
> EXEC sp_rename 'NewTable', 'OldTable'
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rami" <Rami@.discussions.microsoft.com> wrote in message
> news:7D17A95A-3CD0-4786-A718-D34223BE1686@.microsoft.com...
> > Dear All,
> >
> > what happens if IDENTITY value reaches the max value?
> >
> > for example if we decide for a table to have an integer and the counter
> > exceeded the maximmum value how whould it reacte
> >
> > Rami,
>
>
Showing posts with label integer. Show all posts
Showing posts with label integer. Show all posts
Monday, March 19, 2012
IDENTITY reaches the max value
Dear All,
what happens if IDENTITY value reaches the max value?
for example if we decide for a table to have an integer and the counter
exceeded the maximmum value how whould it reacte
Rami,
> what happens if IDENTITY value reaches the max value?
You will get an overflow error.
> for example if we decide for a table to have an integer and the counter
> exceeded the maximmum value how whould it reacte
You'll need to create a new table with the desired schema (e.g. bigint for
the IDENTITY column) and insert data into the new table with IDENTITY_INSERT
turned on. For example:
CREATE TABLE OldTable
(
IdentityColumn int NOT NULL IDENTITY(1, 1),
OtherData int NOT NULL
)
INSERT INTO OldTable (OtherData) VALUES(1)
INSERT INTO OldTable (OtherData) VALUES(2)
INSERT INTO OldTable (OtherData) VALUES(3)
GO
CREATE TABLE NewTable
(
IdentityColumn bigint NOT NULL IDENTITY(1, 1),
OtherData int NOT NULL
)
GO
SET IDENTITY_INSERT NewTable ON
GO
INSERT INTO NewTable (IdentityColumn, OtherData)
SELECT IdentityColumn, OtherData FROM OldTable
GO
SET IDENTITY_INSERT NewTable OFF
GO
DROP TABLE OldTable
GO
EXEC sp_rename 'NewTable', 'OldTable'
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Rami" <Rami@.discussions.microsoft.com> wrote in message
news:7D17A95A-3CD0-4786-A718-D34223BE1686@.microsoft.com...
> Dear All,
> what happens if IDENTITY value reaches the max value?
> for example if we decide for a table to have an integer and the counter
> exceeded the maximmum value how whould it reacte
> Rami,
|||Dear Dan Guzman,
It is working fine Thanks alot Dan
Best wishes
Rami,
"Dan Guzman" wrote:
> You will get an overflow error.
>
> You'll need to create a new table with the desired schema (e.g. bigint for
> the IDENTITY column) and insert data into the new table with IDENTITY_INSERT
> turned on. For example:
> CREATE TABLE OldTable
> (
> IdentityColumn int NOT NULL IDENTITY(1, 1),
> OtherData int NOT NULL
> )
> INSERT INTO OldTable (OtherData) VALUES(1)
> INSERT INTO OldTable (OtherData) VALUES(2)
> INSERT INTO OldTable (OtherData) VALUES(3)
> GO
> CREATE TABLE NewTable
> (
> IdentityColumn bigint NOT NULL IDENTITY(1, 1),
> OtherData int NOT NULL
> )
> GO
> SET IDENTITY_INSERT NewTable ON
> GO
> INSERT INTO NewTable (IdentityColumn, OtherData)
> SELECT IdentityColumn, OtherData FROM OldTable
> GO
> SET IDENTITY_INSERT NewTable OFF
> GO
> DROP TABLE OldTable
> GO
> EXEC sp_rename 'NewTable', 'OldTable'
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rami" <Rami@.discussions.microsoft.com> wrote in message
> news:7D17A95A-3CD0-4786-A718-D34223BE1686@.microsoft.com...
>
>
what happens if IDENTITY value reaches the max value?
for example if we decide for a table to have an integer and the counter
exceeded the maximmum value how whould it reacte
Rami,
> what happens if IDENTITY value reaches the max value?
You will get an overflow error.
> for example if we decide for a table to have an integer and the counter
> exceeded the maximmum value how whould it reacte
You'll need to create a new table with the desired schema (e.g. bigint for
the IDENTITY column) and insert data into the new table with IDENTITY_INSERT
turned on. For example:
CREATE TABLE OldTable
(
IdentityColumn int NOT NULL IDENTITY(1, 1),
OtherData int NOT NULL
)
INSERT INTO OldTable (OtherData) VALUES(1)
INSERT INTO OldTable (OtherData) VALUES(2)
INSERT INTO OldTable (OtherData) VALUES(3)
GO
CREATE TABLE NewTable
(
IdentityColumn bigint NOT NULL IDENTITY(1, 1),
OtherData int NOT NULL
)
GO
SET IDENTITY_INSERT NewTable ON
GO
INSERT INTO NewTable (IdentityColumn, OtherData)
SELECT IdentityColumn, OtherData FROM OldTable
GO
SET IDENTITY_INSERT NewTable OFF
GO
DROP TABLE OldTable
GO
EXEC sp_rename 'NewTable', 'OldTable'
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Rami" <Rami@.discussions.microsoft.com> wrote in message
news:7D17A95A-3CD0-4786-A718-D34223BE1686@.microsoft.com...
> Dear All,
> what happens if IDENTITY value reaches the max value?
> for example if we decide for a table to have an integer and the counter
> exceeded the maximmum value how whould it reacte
> Rami,
|||Dear Dan Guzman,
It is working fine Thanks alot Dan
Best wishes
Rami,
"Dan Guzman" wrote:
> You will get an overflow error.
>
> You'll need to create a new table with the desired schema (e.g. bigint for
> the IDENTITY column) and insert data into the new table with IDENTITY_INSERT
> turned on. For example:
> CREATE TABLE OldTable
> (
> IdentityColumn int NOT NULL IDENTITY(1, 1),
> OtherData int NOT NULL
> )
> INSERT INTO OldTable (OtherData) VALUES(1)
> INSERT INTO OldTable (OtherData) VALUES(2)
> INSERT INTO OldTable (OtherData) VALUES(3)
> GO
> CREATE TABLE NewTable
> (
> IdentityColumn bigint NOT NULL IDENTITY(1, 1),
> OtherData int NOT NULL
> )
> GO
> SET IDENTITY_INSERT NewTable ON
> GO
> INSERT INTO NewTable (IdentityColumn, OtherData)
> SELECT IdentityColumn, OtherData FROM OldTable
> GO
> SET IDENTITY_INSERT NewTable OFF
> GO
> DROP TABLE OldTable
> GO
> EXEC sp_rename 'NewTable', 'OldTable'
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rami" <Rami@.discussions.microsoft.com> wrote in message
> news:7D17A95A-3CD0-4786-A718-D34223BE1686@.microsoft.com...
>
>
IDENTITY reaches the max value
Dear All,
what happens if IDENTITY value reaches the max value?
for example if we decide for a table to have an integer and the counter
exceeded the maximmum value how whould it reacte
Rami,> what happens if IDENTITY value reaches the max value?
You will get an overflow error.
> for example if we decide for a table to have an integer and the counter
> exceeded the maximmum value how whould it reacte
You'll need to create a new table with the desired schema (e.g. bigint for
the IDENTITY column) and insert data into the new table with IDENTITY_INSERT
turned on. For example:
CREATE TABLE OldTable
(
IdentityColumn int NOT NULL IDENTITY(1, 1),
OtherData int NOT NULL
)
INSERT INTO OldTable (OtherData) VALUES(1)
INSERT INTO OldTable (OtherData) VALUES(2)
INSERT INTO OldTable (OtherData) VALUES(3)
GO
CREATE TABLE NewTable
(
IdentityColumn bigint NOT NULL IDENTITY(1, 1),
OtherData int NOT NULL
)
GO
SET IDENTITY_INSERT NewTable ON
GO
INSERT INTO NewTable (IdentityColumn, OtherData)
SELECT IdentityColumn, OtherData FROM OldTable
GO
SET IDENTITY_INSERT NewTable OFF
GO
DROP TABLE OldTable
GO
EXEC sp_rename 'NewTable', 'OldTable'
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Rami" <Rami@.discussions.microsoft.com> wrote in message
news:7D17A95A-3CD0-4786-A718-D34223BE1686@.microsoft.com...
> Dear All,
> what happens if IDENTITY value reaches the max value?
> for example if we decide for a table to have an integer and the counter
> exceeded the maximmum value how whould it reacte
> Rami,|||Dear Dan Guzman,
It is working fine Thanks alot Dan
Best wishes
Rami,
"Dan Guzman" wrote:
> You will get an overflow error.
>
> You'll need to create a new table with the desired schema (e.g. bigint for
> the IDENTITY column) and insert data into the new table with IDENTITY_INSE
RT
> turned on. For example:
> CREATE TABLE OldTable
> (
> IdentityColumn int NOT NULL IDENTITY(1, 1),
> OtherData int NOT NULL
> )
> INSERT INTO OldTable (OtherData) VALUES(1)
> INSERT INTO OldTable (OtherData) VALUES(2)
> INSERT INTO OldTable (OtherData) VALUES(3)
> GO
> CREATE TABLE NewTable
> (
> IdentityColumn bigint NOT NULL IDENTITY(1, 1),
> OtherData int NOT NULL
> )
> GO
> SET IDENTITY_INSERT NewTable ON
> GO
> INSERT INTO NewTable (IdentityColumn, OtherData)
> SELECT IdentityColumn, OtherData FROM OldTable
> GO
> SET IDENTITY_INSERT NewTable OFF
> GO
> DROP TABLE OldTable
> GO
> EXEC sp_rename 'NewTable', 'OldTable'
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rami" <Rami@.discussions.microsoft.com> wrote in message
> news:7D17A95A-3CD0-4786-A718-D34223BE1686@.microsoft.com...
>
>
what happens if IDENTITY value reaches the max value?
for example if we decide for a table to have an integer and the counter
exceeded the maximmum value how whould it reacte
Rami,> what happens if IDENTITY value reaches the max value?
You will get an overflow error.
> for example if we decide for a table to have an integer and the counter
> exceeded the maximmum value how whould it reacte
You'll need to create a new table with the desired schema (e.g. bigint for
the IDENTITY column) and insert data into the new table with IDENTITY_INSERT
turned on. For example:
CREATE TABLE OldTable
(
IdentityColumn int NOT NULL IDENTITY(1, 1),
OtherData int NOT NULL
)
INSERT INTO OldTable (OtherData) VALUES(1)
INSERT INTO OldTable (OtherData) VALUES(2)
INSERT INTO OldTable (OtherData) VALUES(3)
GO
CREATE TABLE NewTable
(
IdentityColumn bigint NOT NULL IDENTITY(1, 1),
OtherData int NOT NULL
)
GO
SET IDENTITY_INSERT NewTable ON
GO
INSERT INTO NewTable (IdentityColumn, OtherData)
SELECT IdentityColumn, OtherData FROM OldTable
GO
SET IDENTITY_INSERT NewTable OFF
GO
DROP TABLE OldTable
GO
EXEC sp_rename 'NewTable', 'OldTable'
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Rami" <Rami@.discussions.microsoft.com> wrote in message
news:7D17A95A-3CD0-4786-A718-D34223BE1686@.microsoft.com...
> Dear All,
> what happens if IDENTITY value reaches the max value?
> for example if we decide for a table to have an integer and the counter
> exceeded the maximmum value how whould it reacte
> Rami,|||Dear Dan Guzman,
It is working fine Thanks alot Dan
Best wishes
Rami,
"Dan Guzman" wrote:
> You will get an overflow error.
>
> You'll need to create a new table with the desired schema (e.g. bigint for
> the IDENTITY column) and insert data into the new table with IDENTITY_INSE
RT
> turned on. For example:
> CREATE TABLE OldTable
> (
> IdentityColumn int NOT NULL IDENTITY(1, 1),
> OtherData int NOT NULL
> )
> INSERT INTO OldTable (OtherData) VALUES(1)
> INSERT INTO OldTable (OtherData) VALUES(2)
> INSERT INTO OldTable (OtherData) VALUES(3)
> GO
> CREATE TABLE NewTable
> (
> IdentityColumn bigint NOT NULL IDENTITY(1, 1),
> OtherData int NOT NULL
> )
> GO
> SET IDENTITY_INSERT NewTable ON
> GO
> INSERT INTO NewTable (IdentityColumn, OtherData)
> SELECT IdentityColumn, OtherData FROM OldTable
> GO
> SET IDENTITY_INSERT NewTable OFF
> GO
> DROP TABLE OldTable
> GO
> EXEC sp_rename 'NewTable', 'OldTable'
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rami" <Rami@.discussions.microsoft.com> wrote in message
> news:7D17A95A-3CD0-4786-A718-D34223BE1686@.microsoft.com...
>
>
Identity Ranges
I'm using Merge replication on a database that was designed using integer identity columns for primary keys. When I create a publisher it's great because Sql Server will create rowguid columns for me on most of the tables; actually all but one table.
The problem comes when I try and use identity ranges for the subscribers. As a test I set up a range on one table, it only allowed for 10 in the range with an 80% threshold. I wanted to see what would happen when say my publisher db inserts 11 new rows. Well, I found out that after the 8th new row it wouldn't insert anymore as the range was exceeded and it gave me an error message saying
The identity range managed by replication is full and must be updated by a replication agent.
The question is; which agent needs to run in order for the new range to be assigned to the publisher? I have seen some people talk about an sp_ that can be run, but in a production environment I wont this to be automatic.
My altenative to ranged identies is using guid uniqueidentifiers. See my other post on this!!
regards
GrahamI assume this is SQL Server 2000.
To answer your questions:
Maybe the table on which merge is not adding the rowguid col is because the table already has a rowguid column?
And since you have 80% threashold, it is failing after the 8th row and I assume you have pub_idrange and range values each set to 10.
You can get by this situation by increasing the numbers, so that the probablity of them running out of numbers is less. Say like 10,000 or 100,000.
And the agent to be run when the id range is full is the merge agent. Running the merge agent will refresh the ranges on publisher and subscriber.
On the publisher, you can also run the sp_adjustpublisheridentityrange to refresh the range and that way you dont need to run the merge agent.
But typically in a production scenario, it is recommended that:
1. You have a decent sized value for the publisher and subscriber id range values
2. Merge agent to run frequently. That way the ranges will be refreshed (if needed) when merge agent runs.
Monday, March 12, 2012
Identity Primary Field in Merge Replication
Hello I currently have a merge replication set up with 4 subscribers. A primary field for one of my tables is set to a integer indetity.
What Ive noticed is that depending on which database I enter data into, the indentity field (primary key) is set within a certain range I.e
On Server 1 - Values start from 1 then 2,3,4 etc etc
Server 2 - 24001, 24002, 24003 etc etc
Server 3 - 46001, 46002, 46003 etc etc
Server 4 - 68001, 68002, 68003
My question is what happens when these ranges eventually conflict? Do they automatically gain a different range such as 142 001, 142 002 etc etc?
Ive tried looking in SQL Help, and a quick search here, Im just after some confirmation before I implement this to my app.
cheers
I wouldn;t advice you use only this for your primary key.
Check out this post:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1423523&SiteID=1
Identity Primary Field in Merge Replication
Hello I currently have a merge replication set up with 4 subscribers. A primary field for one of my tables is set to a integer indetity.
What Ive noticed is that depending on which database I enter data into, the indentity field (primary key) is set within a certain range I.e
On Server 1 - Values start from 1 then 2,3,4 etc etc
Server 2 - 24001, 24002, 24003 etc etc
Server 3 - 46001, 46002, 46003 etc etc
Server 4 - 68001, 68002, 68003
My question is what happens when these ranges eventually conflict? Do they automatically gain a different range such as 142 001, 142 002 etc etc?
Ive tried looking in SQL Help, and a quick search here, Im just after some confirmation before I implement this to my app.
cheers
I wouldn;t advice you use only this for your primary key.
Check out this post:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1423523&SiteID=1
IDENTITY limit
I defined a primary field using integer 4 bytes as IDENTITY.
What will be the limit of this number?
I am not sure the number is big enough in 10 years of time after my
application have been running."Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
>I defined a primary field using integer 4 bytes as IDENTITY.
> What will be the limit of this number?
> I am not sure the number is big enough in 10 years of time after my
> application have been running.
>
2^31-1 (2,147,483,647)
Keep in mind that all numbers are used, even if not allocated.
So if someone begins a transaction, inserts 1000 rows and rolls it back,
then 1000 IDENTITY values are "used" up.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||2 billion. If that's not enough, you could start the seed at -2 billion and
double the capacity that way. If that's still not enough, you could use
BIGINT (and start at a negative number here, too), and if your app is going
to find a way to exceed that limit, then you should probably consider not
using an integer at all.
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
>I defined a primary field using integer 4 bytes as IDENTITY.
> What will be the limit of this number?
> I am not sure the number is big enough in 10 years of time after my
> application have been running.
>|||Hello,
To add to Greg, the limitation wil be based on the usage. Incase if you feel
the INT is not big enough then go ahead and use the BIGINT data type
which will allow 2^63-1 (9,223,372,036,854,775,807) and the storage will be
8 bytes.
Thanks
Hari
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:ufoLBFdZHHA.2316@.TK2MSFTNGP04.phx.gbl...
> "Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
>>I defined a primary field using integer 4 bytes as IDENTITY.
>> What will be the limit of this number?
>> I am not sure the number is big enough in 10 years of time after my
>> application have been running.
> 2^31-1 (2,147,483,647)
> Keep in mind that all numbers are used, even if not allocated.
> So if someone begins a transaction, inserts 1000 rows and rolls it back,
> then 1000 IDENTITY values are "used" up.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> Email: sql (at) greenms.com http://www.greenms.com
>
What will be the limit of this number?
I am not sure the number is big enough in 10 years of time after my
application have been running."Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
>I defined a primary field using integer 4 bytes as IDENTITY.
> What will be the limit of this number?
> I am not sure the number is big enough in 10 years of time after my
> application have been running.
>
2^31-1 (2,147,483,647)
Keep in mind that all numbers are used, even if not allocated.
So if someone begins a transaction, inserts 1000 rows and rolls it back,
then 1000 IDENTITY values are "used" up.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||2 billion. If that's not enough, you could start the seed at -2 billion and
double the capacity that way. If that's still not enough, you could use
BIGINT (and start at a negative number here, too), and if your app is going
to find a way to exceed that limit, then you should probably consider not
using an integer at all.
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
>I defined a primary field using integer 4 bytes as IDENTITY.
> What will be the limit of this number?
> I am not sure the number is big enough in 10 years of time after my
> application have been running.
>|||Hello,
To add to Greg, the limitation wil be based on the usage. Incase if you feel
the INT is not big enough then go ahead and use the BIGINT data type
which will allow 2^63-1 (9,223,372,036,854,775,807) and the storage will be
8 bytes.
Thanks
Hari
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:ufoLBFdZHHA.2316@.TK2MSFTNGP04.phx.gbl...
> "Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
>>I defined a primary field using integer 4 bytes as IDENTITY.
>> What will be the limit of this number?
>> I am not sure the number is big enough in 10 years of time after my
>> application have been running.
> 2^31-1 (2,147,483,647)
> Keep in mind that all numbers are used, even if not allocated.
> So if someone begins a transaction, inserts 1000 rows and rolls it back,
> then 1000 IDENTITY values are "used" up.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> Email: sql (at) greenms.com http://www.greenms.com
>
IDENTITY limit
I defined a primary field using integer 4 bytes as IDENTITY.
What will be the limit of this number?
I am not sure the number is big enough in 10 years of time after my
application have been running."Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
>I defined a primary field using integer 4 bytes as IDENTITY.
> What will be the limit of this number?
> I am not sure the number is big enough in 10 years of time after my
> application have been running.
>
2^31-1 (2,147,483,647)
Keep in mind that all numbers are used, even if not allocated.
So if someone begins a transaction, inserts 1000 rows and rolls it back,
then 1000 IDENTITY values are "used" up.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||2 billion. If that's not enough, you could start the seed at -2 billion and
double the capacity that way. If that's still not enough, you could use
BIGINT (and start at a negative number here, too), and if your app is going
to find a way to exceed that limit, then you should probably consider not
using an integer at all.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
>I defined a primary field using integer 4 bytes as IDENTITY.
> What will be the limit of this number?
> I am not sure the number is big enough in 10 years of time after my
> application have been running.
>|||Hello,
To add to Greg, the limitation wil be based on the usage. Incase if you feel
the INT is not big enough then go ahead and use the BIGINT data type
which will allow 2^63-1 (9,223,372,036,854,775,807) and the storage will be
8 bytes.
Thanks
Hari
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:ufoLBFdZHHA.2316@.TK2MSFTNGP04.phx.gbl...
> "Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
> 2^31-1 (2,147,483,647)
> Keep in mind that all numbers are used, even if not allocated.
> So if someone begins a transaction, inserts 1000 rows and rolls it back,
> then 1000 IDENTITY values are "used" up.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> Email: sql (at) greenms.com http://www.greenms.com
>
What will be the limit of this number?
I am not sure the number is big enough in 10 years of time after my
application have been running."Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
>I defined a primary field using integer 4 bytes as IDENTITY.
> What will be the limit of this number?
> I am not sure the number is big enough in 10 years of time after my
> application have been running.
>
2^31-1 (2,147,483,647)
Keep in mind that all numbers are used, even if not allocated.
So if someone begins a transaction, inserts 1000 rows and rolls it back,
then 1000 IDENTITY values are "used" up.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||2 billion. If that's not enough, you could start the seed at -2 billion and
double the capacity that way. If that's still not enough, you could use
BIGINT (and start at a negative number here, too), and if your app is going
to find a way to exceed that limit, then you should probably consider not
using an integer at all.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
>I defined a primary field using integer 4 bytes as IDENTITY.
> What will be the limit of this number?
> I am not sure the number is big enough in 10 years of time after my
> application have been running.
>|||Hello,
To add to Greg, the limitation wil be based on the usage. Incase if you feel
the INT is not big enough then go ahead and use the BIGINT data type
which will allow 2^63-1 (9,223,372,036,854,775,807) and the storage will be
8 bytes.
Thanks
Hari
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:ufoLBFdZHHA.2316@.TK2MSFTNGP04.phx.gbl...
> "Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
> 2^31-1 (2,147,483,647)
> Keep in mind that all numbers are used, even if not allocated.
> So if someone begins a transaction, inserts 1000 rows and rolls it back,
> then 1000 IDENTITY values are "used" up.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> Email: sql (at) greenms.com http://www.greenms.com
>
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 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...
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...
Friday, February 24, 2012
Identity Column
Hi,
How can I disable an integer column from being IDENTITY? I need to do it
with TSQL statements, not SSMS or EM.
Thanks in advance,
LeilaAdd a column to the table, copy the data from the old column (UPDATE), drop the old column, rename
the new column to the old column name. Column order will of course not be preserved.
Or create a new table instead.
You can not disable the identity property for an existing column. The tools does this by creating a
new table (etc.).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <Leilas@.hotpop.com> wrote in message news:e2p8moWDHHA.4928@.TK2MSFTNGP02.phx.gbl...
> Hi,
> How can I disable an integer column from being IDENTITY? I need to do it with TSQL statements, not
> SSMS or EM.
> Thanks in advance,
> Leila
>|||Check out SET IDENTITY_INSERT in BOL|||On Tue, 21 Nov 2006 13:55:37 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>You can not disable the identity property for an existing column.
Which is a rather odd limitation. I wonder if there is a reason
behind this, besides "they could not be bothered"?
Roy|||> Which is a rather odd limitation. I wonder if there is a reason
> behind this, besides "they could not be bothered"?
I agree! Perhaps there something at the physical level, possibly combined with transaction logging,
which makes this a non-trivial thing to implement?
OTOH, perhaps there haven't been enough wishes at Connect (used to be sqlwish@.microsoft.com) to
warrant the work?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:4hu5m211073rqv7888n6mlvp5e8elrmbb4@.4ax.com...
> On Tue, 21 Nov 2006 13:55:37 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>>You can not disable the identity property for an existing column.
> Which is a rather odd limitation. I wonder if there is a reason
> behind this, besides "they could not be bothered"?
> Roy|||No, but you can override it with SET IDENTITY_INSERT ON. So, I'm not sure
why someone would want to "disable" it.
Wouldn't a disabled IDENTITY attribute just be an INT (or variant) column?
Doesn't an IDENTITY have other special characteristics?
Anthony Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23qQau9WDHHA.840@.TK2MSFTNGP02.phx.gbl...
> > Which is a rather odd limitation. I wonder if there is a reason
> > behind this, besides "they could not be bothered"?
> I agree! Perhaps there something at the physical level, possibly combined
with transaction logging,
> which makes this a non-trivial thing to implement?
> OTOH, perhaps there haven't been enough wishes at Connect (used to be
sqlwish@.microsoft.com) to
> warrant the work?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:4hu5m211073rqv7888n6mlvp5e8elrmbb4@.4ax.com...
> > On Tue, 21 Nov 2006 13:55:37 +0100, "Tibor Karaszi"
> > <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
> >
> >>You can not disable the identity property for an existing column.
> >
> > Which is a rather odd limitation. I wonder if there is a reason
> > behind this, besides "they could not be bothered"?
> >
> > Roy
>|||On Tue, 21 Nov 2006 07:45:04 -0600, "Anthony Thomas"
<ALThomas@.kc.rr.com> wrote:
>No, but you can override it with SET IDENTITY_INSERT ON. So, I'm not sure
>why someone would want to "disable" it.
Consider this information from the Books On Line:
The Transact-SQL programming language provides several SET statements
that change the current session handling of specific information.
It then goes on to list SET IDENTITY_INSERT ON as one of these
statements that ONLY APPLY TO THE CURRENT SESSION.
And elsewhere in BOL:
At any time, only one table in a session can have the IDENTITY_INSERT
property set to ON. If a table already has this property set to ON,
and a SET IDENTITY_INSERT ON statement is issued for another table,
Microsoft® SQL Server? returns an error message that states SET
IDENTITY_INSERT is already ON and reports the table it is set ON for.
So disabling the identity property this way doesn't seem very
practical.
>Wouldn't a disabled IDENTITY attribute just be an INT (or variant) column?
>Doesn't an IDENTITY have other special characteristics?
Yes, it would just be whatever numeric type it was defined as.
Roy Harvey
Beacon Falls, CT|||Roy Harvey (roy_harvey@.snet.net) writes:
> Which is a rather odd limitation. I wonder if there is a reason
> behind this, besides "they could not be bothered"?
What is really funny is that in SQL Server Compact/Mobile/Everywhere Edition
you can use ALTER TABLE to add/remove the IDENTITY property from a column.
That makes me suspect that when SQL Server CE originally was developed
they worked from some preliminary spec of features to be added in SQL 7,
which included this piece of DDL, but which was later was cut from the
mainstream product without the CE team being informed.
Of course, since SQL Whatever-it's-called-this-week Edition is an entirely
different architecture from SQL Server, one can not draw any conclusion
how simple/difficult it would be to add DDL to play with IDENTITY in the
mainstream product.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
How can I disable an integer column from being IDENTITY? I need to do it
with TSQL statements, not SSMS or EM.
Thanks in advance,
LeilaAdd a column to the table, copy the data from the old column (UPDATE), drop the old column, rename
the new column to the old column name. Column order will of course not be preserved.
Or create a new table instead.
You can not disable the identity property for an existing column. The tools does this by creating a
new table (etc.).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <Leilas@.hotpop.com> wrote in message news:e2p8moWDHHA.4928@.TK2MSFTNGP02.phx.gbl...
> Hi,
> How can I disable an integer column from being IDENTITY? I need to do it with TSQL statements, not
> SSMS or EM.
> Thanks in advance,
> Leila
>|||Check out SET IDENTITY_INSERT in BOL|||On Tue, 21 Nov 2006 13:55:37 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>You can not disable the identity property for an existing column.
Which is a rather odd limitation. I wonder if there is a reason
behind this, besides "they could not be bothered"?
Roy|||> Which is a rather odd limitation. I wonder if there is a reason
> behind this, besides "they could not be bothered"?
I agree! Perhaps there something at the physical level, possibly combined with transaction logging,
which makes this a non-trivial thing to implement?
OTOH, perhaps there haven't been enough wishes at Connect (used to be sqlwish@.microsoft.com) to
warrant the work?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:4hu5m211073rqv7888n6mlvp5e8elrmbb4@.4ax.com...
> On Tue, 21 Nov 2006 13:55:37 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>>You can not disable the identity property for an existing column.
> Which is a rather odd limitation. I wonder if there is a reason
> behind this, besides "they could not be bothered"?
> Roy|||No, but you can override it with SET IDENTITY_INSERT ON. So, I'm not sure
why someone would want to "disable" it.
Wouldn't a disabled IDENTITY attribute just be an INT (or variant) column?
Doesn't an IDENTITY have other special characteristics?
Anthony Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23qQau9WDHHA.840@.TK2MSFTNGP02.phx.gbl...
> > Which is a rather odd limitation. I wonder if there is a reason
> > behind this, besides "they could not be bothered"?
> I agree! Perhaps there something at the physical level, possibly combined
with transaction logging,
> which makes this a non-trivial thing to implement?
> OTOH, perhaps there haven't been enough wishes at Connect (used to be
sqlwish@.microsoft.com) to
> warrant the work?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:4hu5m211073rqv7888n6mlvp5e8elrmbb4@.4ax.com...
> > On Tue, 21 Nov 2006 13:55:37 +0100, "Tibor Karaszi"
> > <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
> >
> >>You can not disable the identity property for an existing column.
> >
> > Which is a rather odd limitation. I wonder if there is a reason
> > behind this, besides "they could not be bothered"?
> >
> > Roy
>|||On Tue, 21 Nov 2006 07:45:04 -0600, "Anthony Thomas"
<ALThomas@.kc.rr.com> wrote:
>No, but you can override it with SET IDENTITY_INSERT ON. So, I'm not sure
>why someone would want to "disable" it.
Consider this information from the Books On Line:
The Transact-SQL programming language provides several SET statements
that change the current session handling of specific information.
It then goes on to list SET IDENTITY_INSERT ON as one of these
statements that ONLY APPLY TO THE CURRENT SESSION.
And elsewhere in BOL:
At any time, only one table in a session can have the IDENTITY_INSERT
property set to ON. If a table already has this property set to ON,
and a SET IDENTITY_INSERT ON statement is issued for another table,
Microsoft® SQL Server? returns an error message that states SET
IDENTITY_INSERT is already ON and reports the table it is set ON for.
So disabling the identity property this way doesn't seem very
practical.
>Wouldn't a disabled IDENTITY attribute just be an INT (or variant) column?
>Doesn't an IDENTITY have other special characteristics?
Yes, it would just be whatever numeric type it was defined as.
Roy Harvey
Beacon Falls, CT|||Roy Harvey (roy_harvey@.snet.net) writes:
> Which is a rather odd limitation. I wonder if there is a reason
> behind this, besides "they could not be bothered"?
What is really funny is that in SQL Server Compact/Mobile/Everywhere Edition
you can use ALTER TABLE to add/remove the IDENTITY property from a column.
That makes me suspect that when SQL Server CE originally was developed
they worked from some preliminary spec of features to be added in SQL 7,
which included this piece of DDL, but which was later was cut from the
mainstream product without the CE team being informed.
Of course, since SQL Whatever-it's-called-this-week Edition is an entirely
different architecture from SQL Server, one can not draw any conclusion
how simple/difficult it would be to add DDL to play with IDENTITY in the
mainstream product.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Identity Column
Hi,
How can I disable an integer column from being IDENTITY? I need to do it
with TSQL statements, not SSMS or EM.
Thanks in advance,
Leila
Check out SET IDENTITY_INSERT in BOL
|||On Tue, 21 Nov 2006 13:55:37 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>You can not disable the identity property for an existing column.
Which is a rather odd limitation. I wonder if there is a reason
behind this, besides "they could not be bothered"?
Roy
|||No, but you can override it with SET IDENTITY_INSERT ON. So, I'm not sure
why someone would want to "disable" it.
Wouldn't a disabled IDENTITY attribute just be an INT (or variant) column?
Doesn't an IDENTITY have other special characteristics?
Anthony Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23qQau9WDHHA.840@.TK2MSFTNGP02.phx.gbl...
> I agree! Perhaps there something at the physical level, possibly combined
with transaction logging,
> which makes this a non-trivial thing to implement?
> OTOH, perhaps there haven't been enough wishes at Connect (used to be
sqlwish@.microsoft.com) to
> warrant the work?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:4hu5m211073rqv7888n6mlvp5e8elrmbb4@.4ax.com...
>
|||On Tue, 21 Nov 2006 07:45:04 -0600, "Anthony Thomas"
<ALThomas@.kc.rr.com> wrote:
>No, but you can override it with SET IDENTITY_INSERT ON. So, I'm not sure
>why someone would want to "disable" it.
Consider this information from the Books On Line:
The Transact-SQL programming language provides several SET statements
that change the current session handling of specific information.
It then goes on to list SET IDENTITY_INSERT ON as one of these
statements that ONLY APPLY TO THE CURRENT SESSION.
And elsewhere in BOL:
At any time, only one table in a session can have the IDENTITY_INSERT
property set to ON. If a table already has this property set to ON,
and a SET IDENTITY_INSERT ON statement is issued for another table,
Microsoft SQL Server returns an error message that states SET
IDENTITY_INSERT is already ON and reports the table it is set ON for.
So disabling the identity property this way doesn't seem very
practical.
>Wouldn't a disabled IDENTITY attribute just be an INT (or variant) column?
>Doesn't an IDENTITY have other special characteristics?
Yes, it would just be whatever numeric type it was defined as.
Roy Harvey
Beacon Falls, CT
|||Roy Harvey (roy_harvey@.snet.net) writes:
> Which is a rather odd limitation. I wonder if there is a reason
> behind this, besides "they could not be bothered"?
What is really funny is that in SQL Server Compact/Mobile/Everywhere Edition
you can use ALTER TABLE to add/remove the IDENTITY property from a column.
That makes me suspect that when SQL Server CE originally was developed
they worked from some preliminary spec of features to be added in SQL 7,
which included this piece of DDL, but which was later was cut from the
mainstream product without the CE team being informed.
Of course, since SQL Whatever-it's-called-this-week Edition is an entirely
different architecture from SQL Server, one can not draw any conclusion
how simple/difficult it would be to add DDL to play with IDENTITY in the
mainstream product.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
How can I disable an integer column from being IDENTITY? I need to do it
with TSQL statements, not SSMS or EM.
Thanks in advance,
Leila
Check out SET IDENTITY_INSERT in BOL
|||On Tue, 21 Nov 2006 13:55:37 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>You can not disable the identity property for an existing column.
Which is a rather odd limitation. I wonder if there is a reason
behind this, besides "they could not be bothered"?
Roy
|||No, but you can override it with SET IDENTITY_INSERT ON. So, I'm not sure
why someone would want to "disable" it.
Wouldn't a disabled IDENTITY attribute just be an INT (or variant) column?
Doesn't an IDENTITY have other special characteristics?
Anthony Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23qQau9WDHHA.840@.TK2MSFTNGP02.phx.gbl...
> I agree! Perhaps there something at the physical level, possibly combined
with transaction logging,
> which makes this a non-trivial thing to implement?
> OTOH, perhaps there haven't been enough wishes at Connect (used to be
sqlwish@.microsoft.com) to
> warrant the work?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:4hu5m211073rqv7888n6mlvp5e8elrmbb4@.4ax.com...
>
|||On Tue, 21 Nov 2006 07:45:04 -0600, "Anthony Thomas"
<ALThomas@.kc.rr.com> wrote:
>No, but you can override it with SET IDENTITY_INSERT ON. So, I'm not sure
>why someone would want to "disable" it.
Consider this information from the Books On Line:
The Transact-SQL programming language provides several SET statements
that change the current session handling of specific information.
It then goes on to list SET IDENTITY_INSERT ON as one of these
statements that ONLY APPLY TO THE CURRENT SESSION.
And elsewhere in BOL:
At any time, only one table in a session can have the IDENTITY_INSERT
property set to ON. If a table already has this property set to ON,
and a SET IDENTITY_INSERT ON statement is issued for another table,
Microsoft SQL Server returns an error message that states SET
IDENTITY_INSERT is already ON and reports the table it is set ON for.
So disabling the identity property this way doesn't seem very
practical.
>Wouldn't a disabled IDENTITY attribute just be an INT (or variant) column?
>Doesn't an IDENTITY have other special characteristics?
Yes, it would just be whatever numeric type it was defined as.
Roy Harvey
Beacon Falls, CT
|||Roy Harvey (roy_harvey@.snet.net) writes:
> Which is a rather odd limitation. I wonder if there is a reason
> behind this, besides "they could not be bothered"?
What is really funny is that in SQL Server Compact/Mobile/Everywhere Edition
you can use ALTER TABLE to add/remove the IDENTITY property from a column.
That makes me suspect that when SQL Server CE originally was developed
they worked from some preliminary spec of features to be added in SQL 7,
which included this piece of DDL, but which was later was cut from the
mainstream product without the CE team being informed.
Of course, since SQL Whatever-it's-called-this-week Edition is an entirely
different architecture from SQL Server, one can not draw any conclusion
how simple/difficult it would be to add DDL to play with IDENTITY in the
mainstream product.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Identity Column
Hi,
How can I disable an integer column from being IDENTITY? I need to do it
with TSQL statements, not SSMS or EM.
Thanks in advance,
LeilaAdd a column to the table, copy the data from the old column (UPDATE), drop
the old column, rename
the new column to the old column name. Column order will of course not be pr
eserved.
Or create a new table instead.
You can not disable the identity property for an existing column. The tools
does this by creating a
new table (etc.).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <Leilas@.hotpop.com> wrote in message news:e2p8moWDHHA.4928@.TK2MSFTNGP02.phx.gbl...[v
bcol=seagreen]
> Hi,
> How can I disable an integer column from being IDENTITY? I need to do it w
ith TSQL statements, not
> SSMS or EM.
> Thanks in advance,
> Leila
>[/vbcol]|||Check out SET IDENTITY_INSERT in BOL|||On Tue, 21 Nov 2006 13:55:37 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>You can not disable the identity property for an existing column.
Which is a rather odd limitation. I wonder if there is a reason
behind this, besides "they could not be bothered"?
Roy|||> Which is a rather odd limitation. I wonder if there is a reason
> behind this, besides "they could not be bothered"?
I agree! Perhaps there something at the physical level, possibly combined wi
th transaction logging,
which makes this a non-trivial thing to implement?
OTOH, perhaps there haven't been enough wishes at Connect (used to be sqlwis
h@.microsoft.com) to
warrant the work?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:4hu5m211073rqv7888n6mlvp5e8elrmbb4@.
4ax.com...
> On Tue, 21 Nov 2006 13:55:37 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>
> Which is a rather odd limitation. I wonder if there is a reason
> behind this, besides "they could not be bothered"?
> Roy|||No, but you can override it with SET IDENTITY_INSERT ON. So, I'm not sure
why someone would want to "disable" it.
Wouldn't a disabled IDENTITY attribute just be an INT (or variant) column?
Doesn't an IDENTITY have other special characteristics?
Anthony Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23qQau9WDHHA.840@.TK2MSFTNGP02.phx.gbl...
> I agree! Perhaps there something at the physical level, possibly combined
with transaction logging,
> which makes this a non-trivial thing to implement?
> OTOH, perhaps there haven't been enough wishes at Connect (used to be
sqlwish@.microsoft.com) to
> warrant the work?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:4hu5m211073rqv7888n6mlvp5e8elrmbb4@.
4ax.com...
>|||On Tue, 21 Nov 2006 07:45:04 -0600, "Anthony Thomas"
<ALThomas@.kc.rr.com> wrote:
>No, but you can override it with SET IDENTITY_INSERT ON. So, I'm not sure
>why someone would want to "disable" it.
Consider this information from the Books On Line:
The Transact-SQL programming language provides several SET statements
that change the current session handling of specific information.
It then goes on to list SET IDENTITY_INSERT ON as one of these
statements that ONLY APPLY TO THE CURRENT SESSION.
And elsewhere in BOL:
At any time, only one table in a session can have the IDENTITY_INSERT
property set to ON. If a table already has this property set to ON,
and a SET IDENTITY_INSERT ON statement is issued for another table,
Microsoft SQL Server returns an error message that states SET
IDENTITY_INSERT is already ON and reports the table it is set ON for.
So disabling the identity property this way doesn't seem very
practical.
>Wouldn't a disabled IDENTITY attribute just be an INT (or variant) column?
>Doesn't an IDENTITY have other special characteristics?
Yes, it would just be whatever numeric type it was defined as.
Roy Harvey
Beacon Falls, CT|||Roy Harvey (roy_harvey@.snet.net) writes:
> Which is a rather odd limitation. I wonder if there is a reason
> behind this, besides "they could not be bothered"?
What is really funny is that in SQL Server Compact/Mobile/Everywhere Edition
you can use ALTER TABLE to add/remove the IDENTITY property from a column.
That makes me suspect that when SQL Server CE originally was developed
they worked from some preliminary spec of features to be added in SQL 7,
which included this piece of DDL, but which was later was cut from the
mainstream product without the CE team being informed.
Of course, since SQL Whatever-it's-called-this-week Edition is an entirely
different architecture from SQL Server, one can not draw any conclusion
how simple/difficult it would be to add DDL to play with IDENTITY in the
mainstream product.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
How can I disable an integer column from being IDENTITY? I need to do it
with TSQL statements, not SSMS or EM.
Thanks in advance,
LeilaAdd a column to the table, copy the data from the old column (UPDATE), drop
the old column, rename
the new column to the old column name. Column order will of course not be pr
eserved.
Or create a new table instead.
You can not disable the identity property for an existing column. The tools
does this by creating a
new table (etc.).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <Leilas@.hotpop.com> wrote in message news:e2p8moWDHHA.4928@.TK2MSFTNGP02.phx.gbl...[v
bcol=seagreen]
> Hi,
> How can I disable an integer column from being IDENTITY? I need to do it w
ith TSQL statements, not
> SSMS or EM.
> Thanks in advance,
> Leila
>[/vbcol]|||Check out SET IDENTITY_INSERT in BOL|||On Tue, 21 Nov 2006 13:55:37 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>You can not disable the identity property for an existing column.
Which is a rather odd limitation. I wonder if there is a reason
behind this, besides "they could not be bothered"?
Roy|||> Which is a rather odd limitation. I wonder if there is a reason
> behind this, besides "they could not be bothered"?
I agree! Perhaps there something at the physical level, possibly combined wi
th transaction logging,
which makes this a non-trivial thing to implement?
OTOH, perhaps there haven't been enough wishes at Connect (used to be sqlwis
h@.microsoft.com) to
warrant the work?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:4hu5m211073rqv7888n6mlvp5e8elrmbb4@.
4ax.com...
> On Tue, 21 Nov 2006 13:55:37 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>
> Which is a rather odd limitation. I wonder if there is a reason
> behind this, besides "they could not be bothered"?
> Roy|||No, but you can override it with SET IDENTITY_INSERT ON. So, I'm not sure
why someone would want to "disable" it.
Wouldn't a disabled IDENTITY attribute just be an INT (or variant) column?
Doesn't an IDENTITY have other special characteristics?
Anthony Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23qQau9WDHHA.840@.TK2MSFTNGP02.phx.gbl...
> I agree! Perhaps there something at the physical level, possibly combined
with transaction logging,
> which makes this a non-trivial thing to implement?
> OTOH, perhaps there haven't been enough wishes at Connect (used to be
sqlwish@.microsoft.com) to
> warrant the work?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:4hu5m211073rqv7888n6mlvp5e8elrmbb4@.
4ax.com...
>|||On Tue, 21 Nov 2006 07:45:04 -0600, "Anthony Thomas"
<ALThomas@.kc.rr.com> wrote:
>No, but you can override it with SET IDENTITY_INSERT ON. So, I'm not sure
>why someone would want to "disable" it.
Consider this information from the Books On Line:
The Transact-SQL programming language provides several SET statements
that change the current session handling of specific information.
It then goes on to list SET IDENTITY_INSERT ON as one of these
statements that ONLY APPLY TO THE CURRENT SESSION.
And elsewhere in BOL:
At any time, only one table in a session can have the IDENTITY_INSERT
property set to ON. If a table already has this property set to ON,
and a SET IDENTITY_INSERT ON statement is issued for another table,
Microsoft SQL Server returns an error message that states SET
IDENTITY_INSERT is already ON and reports the table it is set ON for.
So disabling the identity property this way doesn't seem very
practical.
>Wouldn't a disabled IDENTITY attribute just be an INT (or variant) column?
>Doesn't an IDENTITY have other special characteristics?
Yes, it would just be whatever numeric type it was defined as.
Roy Harvey
Beacon Falls, CT|||Roy Harvey (roy_harvey@.snet.net) writes:
> Which is a rather odd limitation. I wonder if there is a reason
> behind this, besides "they could not be bothered"?
What is really funny is that in SQL Server Compact/Mobile/Everywhere Edition
you can use ALTER TABLE to add/remove the IDENTITY property from a column.
That makes me suspect that when SQL Server CE originally was developed
they worked from some preliminary spec of features to be added in SQL 7,
which included this piece of DDL, but which was later was cut from the
mainstream product without the CE team being informed.
Of course, since SQL Whatever-it's-called-this-week Edition is an entirely
different architecture from SQL Server, one can not draw any conclusion
how simple/difficult it would be to add DDL to play with IDENTITY in the
mainstream product.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Subscribe to:
Posts (Atom)