The IDENTITY property is not a data type, but an attribute that can be assigned to INT data types (and other data types as well). From EM, you can right-click on the table and go into Design. In the design, click on the field you wish to set to auto-increment. In the area beneath the listing of columns, you will see a list of attributes (Description, Default Value, Precision, Scale, Identity, Identity Seed, Indentity Increment, etc...).
Click on the Identity attribute and set it equal to "Yes".
Alternatively, you may use a script to create the table. Here is a generic script that you may use. Note, however, that you will have issues with trying to do it this way. Obviously, you cannot create a table with the same name over the existing table. Also, if you create a temporary table and push the data from your existing table into it, you will have to enable Identity Insert (see SQL BOL). However, I thought you should see the DDL for creating a table with an Identity Column so that you might better understand what SQL is doing.
CREATE TABLE [dbo].[tbl_MyTable] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[MyColumn1] [varchar] (50) NOT NULL ,
[MyColumn2] [varchar] (3000) NULL ,
[MyColumn3] [varchar] (50) NOT NULL
) ON [PRIMARY]
Also not the following:
1. The syntax for IDENTITY is INDENTITY (Seed, Increment)
2. Seed is a starting value. You may not want to start at 1
3. Increment is a value by which the increment the identity. You may want to increment by a value other than 1.
See SQL BOL for more information on IDENTITY
Originally posted by Plarde
I do not see the field type "identity" in SQL Server ?
My question was, in Access we have the type of field
"automatic number" which is an auto incremental integer field.
(often ID numbers)
After migration, this one become a simple "int" field.
So in my sql statement, I never fullfill this field through
the "insert", because it is automatic in ACCESS.
Now, under SQL Server, it bugs.
So I need to tell to SQL Server that this is an atomatic incremental field.