June 26, 2003 at 12:13 pm
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
June 26, 2003 at 12:37 pm
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.
June 27, 2003 at 6:51 am
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.
June 27, 2003 at 7:04 am
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]
June 27, 2003 at 7:50 am
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
June 27, 2003 at 10:03 pm
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/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply