How to change column IDENTITY property from Yes to No using SQL.

Posted on 2007-10-08
Last Modified: 2008-01-09
I create a column using CREATE TABLE ... ColumnName INTEGER IDENTITY(1,1) NOTNULL so that it is intialized to sequential numbers when I insert records from a select query. I want to alter the column to INTEGER so that I can update numbers in the column to arbitrary values. ALTER COLUMN ColumnName INTEGER does not do it.
Question by:mcglincym
    LVL 75

    Accepted Solution

    U can't direclty do this thru an 'ALTER' statement, the other option is
    - Create another integer column and drop your original identity column and finally rename the newly created column with your original name
    LVL 22

    Assisted Solution

    Do you want the column to be an IDENTITY or not? With an IDENTITY column you must accept that you can't easily control the sequence or update the values. If that's a problem then IDENTITY is the wrong thing for you to use.

    You can't add or remove the IDENTITY property to/from an an existing column. Create the table either with or without it and then leave it alone.
    LVL 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]
    the only way to INSERT values outside the identity is to set the following:

    SET IDENTITY_INSERT ON (yourtable)
    insert into yourtable ( your_id, your_field ) values ( 342, 'some_data')

    to "update", you would need to insert a new row and delete the old one.

    and as dbportas indicated: either you want an identity field or not. you cannot have both.

    Author Comment

    Your answers confirm what I suspected even though I can change the Identity property for a column manually in table design view. I try to create SQL statements in Access applications that work for both SQL Server and JET databases with minimal syntax changes. Using ALTER COLUMN works fine for JET. I try to avoid creating, copying, then dropping columns because of performance issues (3 queries to run instead of 1) and because of uncompacted space left in JET databases.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now