[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2007-10-08
Medium Priority
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

Aneesh Retnakaran earned 1200 total points
ID: 20036315
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

dportas earned 400 total points
ID: 20036379
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 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 400 total points
ID: 20036426
the only way to INSERT values outside the identity is to set the following:

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

ID: 20036706
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.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
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 setup several different housekeeping processes for a SQL Server.

872 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