Showing posts with label decide. Show all posts
Showing posts with label decide. 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:
> > 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,
>
>

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...
>
>

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...
>
>