How do I add auto_increment to a column in SQL Server 2008

Question

I am using SQL Server 2008 and a primary key of a database table I am using is not an IDENTITY column (not sure why). I need to change that.

I am in SQL Server Management Studio in design view, under column properties and for some reason I can't change the identity specifications to Yes.

Is there something that I am missing.. I am new to SQL Server - any ideas on what I am missing??

Here is the create table

CREATE TABLE [dbo].[AR_Transactions](
       [Trans_ID] [bigint] NOT NULL,
       [DateTime] [datetime] NOT NULL,
       [Cashier_ID] [nvarchar](50) NULL,
       [CustNum] [nvarchar](12) NOT NULL,
       [Trans_Type] [nvarchar](2) NOT NULL,
       [Prev_Cust_Balance] [money] NULL,
       [Prev_Inv_Balance] [money] NULL,
       [Trans_Amount] [money] NOT NULL,
       [Payment_Method] [nvarchar](4) NULL,
       [Payment_Info] [nvarchar](20) NULL,
       [Description] [nvarchar](38) NULL,
       [Invoice_Number] [bigint] NOT NULL,
       [Store_ID] [nvarchar](10) NOT NULL,
       [Dirty] [bit] NOT NULL,
       [Station_ID] [nvarchar](5) NULL,
       [Payment_Type] [smallint] NULL,

CONSTRAINT [pkAR_Transactions] 
       PRIMARY KEY CLUSTERED([Store_ID] ASC, [Trans_ID] ASC)
           WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
                 ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[AR_Transactions] 
    ADD CONSTRAINT [DF_AR_Transactions_Trans_ID_AR_Transactions] 
    DEFAULT ((0)) FOR [Trans_ID]

ALTER TABLE [dbo].[AR_Transactions] 
    ADD CONSTRAINT [DF_AR_Transactions_Invoice_Number_AR_Transactions] 
    DEFAULT ((0)) FOR [Invoice_Number]

Here is the query that I need to run... its a complete hack to try to auto-increment my inserts myself

BEGIN TRANSACTION 

INSERT INTO 
        [cresql].[dbo].[AR_Transactions](Trans_ID, DateTime , Dirty, Store_ID, Trans_Type,  
            Cashier_ID, CustNum, Trans_Amount, Prev_Cust_Balance) 
        SELECT  
            (SELECT MAX(Trans_ID ) + 1 FROM [cresql].[dbo].[AR_Transactions]), 
            DATEADD(MINUTE, -30, Getdate()), 1, 1001, 'C', 100199, CustNum,
            -Acct_Balance, Acct_Balance 
    FROM  [cresql].[dbo].[Customer] 
        WHERE Acct_Balance <> 0  

UPDATE [cresql].[dbo].[Customer] 
    SET Acct_Balance = 0 
WHERE Acct_Balance <> 0  

COMMIT TRANSACTION

What do you mean by can't change it. Does it give you an error? Is it greyed out? — James Hill, May 24 '11 at 16:44
Thw problem is the Is Identity is grayed out to no and i cant change it — Matt Elhotiby, May 24 '11 at 17:03
On a side-note: I would *never* call a column something like `[DateTime]`..... and I also would recommend *not* making columns of length less than 5 `varchar` or `nvarchar` - the 2-byte overhead is just killing you. If the column is 5 chars or less, make it `CHAR(x)` or `NCHAR(x)`. And I'm sure that you're current method of increasing the `Trans_ID` is not safe in a busy environment and will sooner or later lead to duplicate `Trans_ID` values.... — marc_s, May 24 '11 at 19:01
@marc_s – When you modified the ddl **statements** you removed the reason for his problems. You can't have a default constraint on a column with identity. I believe that is the problem and that is the reason he can't use MSSMS to change the column to be an identity column. See my answer for pretty pictures. — Mikael Eriksson, May 24 '11 at 20:32
@Mikael Eriksson: ooops, sorry about that - you're totally right. With a default constraint in place, the `Identity Specification` is indeed grayed out - removing the default constraint solves this - good catch !! — marc_s, May 24 '11 at 20:35

marc_s · Answer 1 · 2011-05-24T20:37:47.733

13

To illustrate Martin's point:

enter image description here

And PS: - as Mikael Eriksson rightfully mentions (and documents nicely), this Identity Specification remains grayed out as long as that column you're working on has a default constraint.

edited May 24 '11 at 20:37

answered May 24 '11 at 16:50

marc_s

732,580
175
1,330
1,459

Thw problem is the Is Identity is grayed out to no and i cant change it – Matt Elhotiby May 24 '11 at 17:06
2

Exactly my point: you cannot add an identity property to a column after the fact. Nor can you get rid of it without dropping the column. The identy property **must** be defined when the column is created. Hence the 7-step tango in my answer below. – Nicholas Carey May 24 '11 at 18:10

score 5 · Answer 2 · edited May 23 '17 at 12:23

5

You need to expand the "Identity Specification" node to change it via the (Is Identity) property.

This will rebuild the table so you might also need to go into Tools -> Options -> Designers -> Prevent saving changes that require table re-creation.

This can be an extremely time consuming operation on large tables as well as entailing a lot of logging and locking. To perform this operation on a large table see my answer here.

edited May 23 '17 at 12:23

Community

1
1

answered May 24 '11 at 16:44

Martin Smith

438,706
87
741
845

+1 it's a bit tricky and not very intuitive at first - just clicking on the "Identity Specification" line doesn't do anything - one has to expand it first, and then changes can be made... – marc_s May 24 '11 at 16:45
Thw problem is the Is Identity is grayed out to no and i cant change it – Matt Elhotiby May 24 '11 at 17:03
@Tamer - What is the datatype of the column? – Martin Smith May 24 '11 at 17:08
1

You can only have one identity column per table. That's the only other thing that springs to mind. Otherwise close down SSMS and reopen it and see if you still get the problem. – Martin Smith May 24 '11 at 17:18
yes i tried setting it and nothing...let me take a screenshot..one sec – Matt Elhotiby May 24 '11 at 17:19
@Tamer - Can you right click the table and choose "Script Table As -> Create" and post that into your question? – Martin Smith May 24 '11 at 17:22
so my main reason i want to add this auto-increment is that i need to run this query and its failing...I pasted in my original question – Matt Elhotiby May 24 '11 at 17:38
I updated my original question with more relevant information – Matt Elhotiby May 24 '11 at 17:40
@Martin...any idea what is happening – Matt Elhotiby May 24 '11 at 17:58
@Tamer - Was offline but looks like Mikael's answer probably has it nailed. – Martin Smith May 24 '11 at 20:59

Mikael Eriksson · Answer 3 · 2011-05-24T18:58:35.647

5

Remove the default constraint of column Trans_ID first. Then you can set Is Identity to Yes in the designer.

This is properties for column Trans_ID in your table AR_Transactions. (Is Identity) is disabled:

enter image description here

Remove the default constraint and (Is Identity) is no longer disabled:

enter image description here

Set to yes and save. Default Value or Binding is disabled instead:

enter image description here

edited May 24 '11 at 18:58

answered May 24 '11 at 18:43

Mikael Eriksson

136,425
22
210
281

2

+1 nailed it on the head - not sure why anyone would **downvote** this absolutely spot-on response..... – marc_s May 24 '11 at 20:36

score 1 · Answer 4 · answered May 24 '11 at 16:49

1

You can't use ALTER TABLE ... ALTER COLUMN to modify a column to have an identity property. You'll need to

drop the primary key constraint and any foreign key constraints referencing the column in question in your table.
add a new column with the identity property. It should have the same type (int, I presume) as the existing column.
update the table to seed the new column with the values of the existing column.
alter the new column to make it non-nullable.
drop the old/existing column.
rename the new column so that its name is the same as that of the old column.
Recreate the primary key and foreign key references you dropped in the 1st step.

Simple! Or something.

answered May 24 '11 at 16:49

Nicholas Carey

71,308
16
93
135

1

[There is a way of doing this as a metadata only change](http://stackoverflow.com/questions/5181304/set-identity-on-the-column/6094907#6094907) (BTW `identity` columns are not updatable so step 3 can't be done in your plan anyway) – Martin Smith May 24 '11 at 16:52
@Martin identity columns can in fact be updated in a conversion process using Set Identity_insert ON – HLGEM May 24 '11 at 18:46
1

`set identity_insert on` allows the **insertion** of rows with a column value in identity columns; it does not allow the **update** of the value of the identity column of existing rows. – Nicholas Carey May 24 '11 at 18:49

score 0 · Answer 5 · answered Jul 31 '14 at 10:35

CREATE TABLE [dbo].[AR_Transactions](
       [Trans_ID] [bigint] IDENTITY(1,1) NOT NULL,
       [DateTime] [datetime] NOT NULL,
       [Cashier_ID] [nvarchar](50) NULL,
       [CustNum] [nvarchar](12) NOT NULL,
       [Trans_Type] [nvarchar](2) NOT NULL,
       [Prev_Cust_Balance] [money] NULL,
       [Prev_Inv_Balance] [money] NULL,
       [Trans_Amount] [money] NOT NULL,
       [Payment_Method] [nvarchar](4) NULL,
       [Payment_Info] [nvarchar](20) NULL,
       [Description] [nvarchar](38) NULL,
       [Invoice_Number] [bigint] NOT NULL,
       [Store_ID] [nvarchar](10) NOT NULL,
       [Dirty] [bit] NOT NULL,
       [Station_ID] [nvarchar](5) NULL,
       [Payment_Type] [smallint] NULL,

CONSTRAINT [pkAR_Transactions] 
       PRIMARY KEY CLUSTERED([Store_ID] ASC, [Trans_ID] ASC)
           WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
                 ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[AR_Transactions] 
    ADD CONSTRAINT [DF_AR_Transactions_Trans_ID_AR_Transactions] 
    DEFAULT ((0)) FOR [Trans_ID]

ALTER TABLE [dbo].[AR_Transactions] 
    ADD CONSTRAINT [DF_AR_Transactions_Invoice_Number_AR_Transactions] 
    DEFAULT ((0)) FOR [Invoice_Number]

How do I add auto_increment to a column in SQL Server 2008

5 Answers5

Linked

Related