Identity Column

Question

Identity Column

subhashkvsql

Old Hand

Points: 372
More actions
June 26, 2003 at 12:13 pm

#81925

How can i modify an existing column as IDENTITY, similarly how can i modify IDENTITY property of a column to NULL
please help
thanks in advance
subhashkvsql

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

Use Enterprise Manager to do that. Start Profiler to trace and you will find out exactly SQL statements that SQL Server actually recreate the table with identity columns when you alter it through EM.

Edwin1608 SSC Journeyman Points: 99 More actions · Answer 2

Short of changing the table definition through EM, there is no scriptive way of doing what you are trying.

You cannot ALTER TABLE to make a column an IDENTITY, but you can change the table design via EM - and you will lose all data that exists in that column.

You can though ADD a column as an IDENTITY with

ALTER TABLE table_name

ADD new_column

data-type IDENTITY(seed, increment)

This is probably your best bet if you want a column to have an IDENTITY column - and you can always delete any redundant column from your table.

Also, by definition, IDENTITY columns can never hold NULL, as the point of it is to be a unique, auto-incremental data value. If you want NULLS in a numerical and incremental field, data-type as INT, and either do MAX(col) + 1 for each new insert, or run with a single column/row table to hold the next value to use.

Another useful bit of code:

If you ever want to reseed a tables' identity [as a DELETE TABLE statement will not re-set the IDENTITY seed values] the use:

DBCC CHECKIDENT(table_name, REDSEED, seed_value)

E.g.

DBCC CHECKIDENT(Employees, RESEED, 100)

NB: Will make the next insert in to the Employees table have an IDENTITY value of 100 + INCREMENT for the IDENTITY column.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Hi subhashkvsql,

quote:
How can i modify an existing column as IDENTITY, similarly how can i modify IDENTITY property of a column to NULL

if you know for certain that there are no duplicates in that column, simply make it an identity column via EM as pointed out before. As for the second question I'm not sure to understand correctly. Are you asking how to reverse a column from identity to non identity (if so, simply set the yes in Identity to no via EM) or are you asking how to allow NULL in an identity column. I don't think that this is possible.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Philip Kelley SSCertifiable Points: 6351 More actions · Answer 4

Just to spell it out, EM will "convert" the table by creating a new table, copying over the data, dropping the old table, and renaming the new table to the old table's name.

In the "design table" EM interface there's a toolbar button (third from the left in my current configuration) that's title "Save Change Script"; make your changes in the interface, click on this, and you get the script. (Profiler works too, but then you get all the EM connection setup and communications chaff as well.) Note also that this script tends not to be particularly efficient, and you might want to review it for large databases. (I personally suspect it's backwards compatible with SQL Server 6.5)

Philip

G.R.Prithiviraj Kulasingham SSCarpal Tunnel Points: 4105 More actions · Answer 5

1. identity columns can't have null values

2. To change an identity column into another one add another column with same data type, update the new columns value and remove the identity column

3. You can do a similar thing to convert an int column into identity with out the update, since indentity columns cannot be updated

Cheers,
Prithiviraj Kulasingham

http://preethiviraj.blogspot.com/