Identity property

Hello friends,

I am using sql server 2005. In some tables to create the column Autoincrement I had set the 'Idetity Specification' property to 'Yes'. I want to know that how can we do it through sql scripts i.e. by writing query.

Please let me know

Thanks & Regards
Girish Nehte

CREATE TABLE your_table( id_numint IDENTITY(1,1), fnamevarchar (20), minitchar(1), lnamevarchar(30))

PS: IDENTITY(seed, increment)|||

Thanks Addie.

Actually I have already build table and its identity field for id column is already set to "Yes". Now what I want to do is create a script which when executed will first set identity field to "No" and then again to "Yes", i.e. I want to alter that table.

How it can be done?

Thanks & Regards
Girish Nehte

|||T-SQL's ALTER TABLE statement doesn't support dropping the IDENTITY property in SQL Server 2000 or 7.0. Your only option for deleting an IDENTITY column is to create a new table structure without the IDENTITY column, then copy the data into this structure.

Just curious - why would you like to do that?

|||

I suspect what you want to do is to turn off the auto increment on the identity column so you can insert your own values. To do that, use

SETIDENTITY_INSERT tablenameOFF

then populate the table and issue

SETIDENTITY_INSERT tablenameON

|||

Actually in my project I want to create a script after running that script all the data from the data will be deleted and columns with identity "YES" will be reset to 0. Thats why and I think that it can be done by setting and resetting the identity field.

|||

Try:

1. using the truncate statement instead of delete (ex: TRUNCATE TABLE theNameOfYourTable)
2. EXEC ('DBCC CHECKIDENT(theNameOfYourTable,RESEED,0)')

Identity order

Hi,

I have some tables in a database with a identity (autoincrement) column (PK).

After several operations (INSERT, UPDATE and DELETE), some holes appeared in the identity column, like this:

ContactId Contact

1 John

2 Mary

5 Sam

9 David

where ContactId is the identity column.

Can I order the ContactId column, by removing the empty spaces, in order to the table appears like this?:

ContactId Contact

1 John

2 Mary

3 Sam

4 David

(I'm using SQL Server 2005.)

Thank you in advance.

Identity values are meant to be unique (but not technically guaranteed) but they won't necessary be sequential or without any gaps. An identity is probably not going to work well for this scenario. You can create another table to manage this. And this may give you another option:

http://blogs.msdn.com/sqlcat/archive/2006/04/10/sql-server-sequence-number.aspx

However, you will still have issues with deletes - those won't be easy to manage if this is your requirement.

-Sue

|||

Sue:

Thank you for your answer.

I understand what you mean. But, after the table is filled with data, can I apply any command in order to put the identity values in sequential order? (Maybe 'ALTER INDEX' or 'DBCC' commands.)

I ask this, because I think identity column value must have a limit (maybe integers maximum limit in C language), and after long time with too much database operations, perhaps that limit be reached and several empty spaces (talking of auto generated values) remains in the table.

I beg your pardon for my silly question, but I'm newbie with SQL Server.

Anyway, thanks a lot.

--

Adrián

|||

You can change the number it's seeded at - for example if the next contact id number will be 100 but your last contact id in the table is 50 - using DBCC CHECKIDENT but it won't do anything about the gaps. For that, you would need to do something along the lines of creating a new table, populate the exiting data in the old table into the new table, drop the old table and rename the new table.

If the issue with gaps is that you want to use the number and are afraid you will run out of numbers, if the data type is an int, you can go up to 2,147,483,647 and then after exhausting positive values it will start using negatives through the value -2,147,483,648. So you have over 4 billion to work with there. If you double your storage space and use bigints the range is larger. It would handle thousands of ids generated per second over the course of over 100 years. I can remember the details but I really doubt that you would run out of numbers. You would of course want to use an appropriate data type due to the difference in storage required.

-Sue

|||

Thank you very much.

Your answer was very clear.

Identity order

Hi,

I have some tables in a database with a identity (autoincrement) column (PK).

After several operations (INSERT, UPDATE and DELETE), some holes appeared in the identity column, like this:

ContactId Contact

1 John

2 Mary

5 Sam

9 David

where ContactId is the identity column.

Can I order the ContactId column, by removing the empty spaces, in order to the table appears like this?:

ContactId Contact

1 John

2 Mary

3 Sam

4 David

(I'm using SQL Server 2005.)

Thank you in advance.

Identity values are meant to be unique (but not technically guaranteed) but they won't necessary be sequential or without any gaps. An identity is probably not going to work well for this scenario. You can create another table to manage this. And this may give you another option:

http://blogs.msdn.com/sqlcat/archive/2006/04/10/sql-server-sequence-number.aspx

However, you will still have issues with deletes - those won't be easy to manage if this is your requirement.

-Sue

|||

Sue:

Thank you for your answer.

I understand what you mean. But, after the table is filled with data, can I apply any command in order to put the identity values in sequential order? (Maybe 'ALTER INDEX' or 'DBCC' commands.)

I ask this, because I think identity column value must have a limit (maybe integers maximum limit in C language), and after long time with too much database operations, perhaps that limit be reached and several empty spaces (talking of auto generated values) remains in the table.

I beg your pardon for my silly question, but I'm newbie with SQL Server.

Anyway, thanks a lot.

--

Adrián

|||

You can change the number it's seeded at - for example if the next contact id number will be 100 but your last contact id in the table is 50 - using DBCC CHECKIDENT but it won't do anything about the gaps. For that, you would need to do something along the lines of creating a new table, populate the exiting data in the old table into the new table, drop the old table and rename the new table.

If the issue with gaps is that you want to use the number and are afraid you will run out of numbers, if the data type is an int, you can go up to 2,147,483,647 and then after exhausting positive values it will start using negatives through the value -2,147,483,648. So you have over 4 billion to work with there. If you double your storage space and use bigints the range is larger. It would handle thousands of ids generated per second over the course of over 100 years. I can remember the details but I really doubt that you would run out of numbers. You would of course want to use an appropriate data type due to the difference in storage required.

-Sue

|||

Thank you very much.

Your answer was very clear.