Creating Identity Property on existing table column 
Author Message
 Creating Identity Property on existing table column
Hi,

I have a table that has over 1.8 billion records in a
history database. I now need to alter the PK column to
assign it an Identity property, but I do not want it to
check all the existing data for obvious reasons. Can I
accomplish this easily?

Thanks

Rushabh



Tue, 29 Nov 2005 15:20:46 GMT
 Creating Identity Property on existing table column

You can't add/remove the IDENTITY
column property using an ALTER TABLE statement.
Use enterprise manager to do this. Once identity property is enabled for
this column next identity value will be straight away max(ident_col) + 1

--
--Vishal


Quote:
> Hi,

> I have a table that has over 1.8 billion records in a
> history database. I now need to alter the PK column to
> assign it an Identity property, but I do not want it to
> check all the existing data for obvious reasons. Can I
> accomplish this easily?

> Thanks

> Rushabh



Tue, 29 Nov 2005 15:30:37 GMT
 Creating Identity Property on existing table column
Vishal,

I ideally want to aviod that approach because when I do it
that way, SQL will create a new table with the Identity
property set and then physically move all the data to the
new table and then drop the old table and rename the new
table. I want to avoid this due to the very high volume of
data and space constraints.

I was hoping that there was an alternate solution...

Thanks

Rushabh

Quote:
>-----Original Message-----
>You can't add/remove the IDENTITY
>column property using an ALTER TABLE statement.
>Use enterprise manager to do this. Once identity property
is enabled for
>this column next identity value will be straight away max
(ident_col) + 1

>--
>--Vishal



>> Hi,

>> I have a table that has over 1.8 billion records in a
>> history database. I now need to alter the PK column to
>> assign it an Identity property, but I do not want it to
>> check all the existing data for obvious reasons. Can I
>> accomplish this easily?

>> Thanks

>> Rushabh

>.



Tue, 29 Nov 2005 15:53:16 GMT
 Creating Identity Property on existing table column
you may add a new column in the table with the identity property but this
column will be serially generated values. Hence if you have original primary
key column with some gaps in it this solution may not be feasible.
If you are sure you do not have any gaps in the original pk field then add a
new column with identity and proper seed and increment , drop the original
pk column and create newly added identity column as PK

--
--Vishal


Quote:
> Vishal,

> I ideally want to aviod that approach because when I do it
> that way, SQL will create a new table with the Identity
> property set and then physically move all the data to the
> new table and then drop the old table and rename the new
> table. I want to avoid this due to the very high volume of
> data and space constraints.

> I was hoping that there was an alternate solution...

> Thanks

> Rushabh

> >-----Original Message-----
> >You can't add/remove the IDENTITY
> >column property using an ALTER TABLE statement.
> >Use enterprise manager to do this. Once identity property
> is enabled for
> >this column next identity value will be straight away max
> (ident_col) + 1

> >--
> >--Vishal



> >> Hi,

> >> I have a table that has over 1.8 billion records in a
> >> history database. I now need to alter the PK column to
> >> assign it an Identity property, but I do not want it to
> >> check all the existing data for obvious reasons. Can I
> >> accomplish this easily?

> >> Thanks

> >> Rushabh

> >.



Tue, 29 Nov 2005 16:18:21 GMT
 Creating Identity Property on existing table column
You could create a new column with Identity.
Transfer the old PK column into this one using SET IDENTITY_INSERT ON.
Drop the old PK column.
Change the name of the new column and set PK.
But this may give you as many problems.

You could also create a new table with the structure you want and transfer the rows from the old table in chunks.
But even this way, you will eventually have to drop the old table, rename the new one and reset foreign key relationships.


Quote:
> Vishal,

> I ideally want to aviod that approach because when I do it
> that way, SQL will create a new table with the Identity
> property set and then physically move all the data to the
> new table and then drop the old table and rename the new
> table. I want to avoid this due to the very high volume of
> data and space constraints.

> I was hoping that there was an alternate solution...

> Thanks

> Rushabh

> >-----Original Message-----
> >You can't add/remove the IDENTITY
> >column property using an ALTER TABLE statement.
> >Use enterprise manager to do this. Once identity property
> is enabled for
> >this column next identity value will be straight away max
> (ident_col) + 1

> >--
> >--Vishal



> >> Hi,

> >> I have a table that has over 1.8 billion records in a
> >> history database. I now need to alter the PK column to
> >> assign it an Identity property, but I do not want it to
> >> check all the existing data for obvious reasons. Can I
> >> accomplish this easily?

> >> Thanks

> >> Rushabh

> >.



Tue, 29 Nov 2005 16:12:54 GMT
 Creating Identity Property on existing table column
I have done the similar change in a few tables to add a new identity column.
We tested both T-SQL (ALTER TABLE ) and EM. In a test server, it took about
20 hours for EM and 30 for the T-SQL to alter the column for a 30 million
row table. We also tested creating a new table and copy data to this new
table chunk by chunk. It turned out that the latter is much faster and
safer. We have modified the production table of 100 million rows without any
user noticing it. Here is how we did it:

1. Create a New table with the identity field. Put this table in separate
physical drive(s)
2. Setup a job that ran a stored proc every night
3. The SP includes
    DO While 1=1 AND DATEPART(HH,GETDATE())<=5 --Stop after 5AM
        INSERT .. SELECT TOP 10000...

The table had a datetime column which was indexed. We used that column to
select 10,000 rows chunk by chunk. In a week, everything is done. We renamed
both tables (saved the old one instead of dropping it). 0 down time.

1.8 billion records is huge number. I doubt you can it at one shot. And you
need to prepare a huge log space. If there is anything wrong, then wait a
few days just for roll back. :-)

Good luck,

Gary


Quote:
> Hi,

> I have a table that has over 1.8 billion records in a
> history database. I now need to alter the PK column to
> assign it an Identity property, but I do not want it to
> check all the existing data for obvious reasons. Can I
> accomplish this easily?

> Thanks

> Rushabh



Thu, 01 Dec 2005 07:35:20 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. how do I set the identity property on an existing column

2. Modify existed Column get IDENTITY property ?

3. Adding IDENTITY property to existing column

4. Script to copy data from one existing column to a new column in another existing table

5. alter identity column in an existing table

6. adding an identity column to an existing table

7. Creating identity on an existing table

8. Designating an existing column as Identity column

9. Getting rid of the IDENTITY property on a column withou getting rid of the column

10. Designating an existing column as Identity column

11. need to copy existing Access data into existing SQL tables, maintaining identity values

12. need to copy existing Access data into existing SQL tables, maintaining identity values


 
Powered by phpBB® Forum Software