How to add auto_increment to a SQL Server 2008 column

Question

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

I work in SQL Server Management Studio in design view, in column properties, and for some reason I cannot change the identity specifications to Yes.

Is there something that I am missing. I'm new to SQL Server - any ideas on what I'm 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 I need to run ... its a complete hack to try to automatically increase my inserts

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

Answer 1

:

PS: - ( ), , , , .

Answer 2

" " node, (Is Identity).

, Tools -> Options -> Designers -> Prevent saving changes that require table re-creation.

, . . .

Answer 3

Trans_ID. Is Identity "" .

Trans_ID AR_Transactions. (Is Identity) :

, (Is Identity) :

yes . Default Value or Binding :

Answer 4

ALTER TABLE ... ALTER COLUMN , .

• , .
• identity. (int, ), .
• , .
• , .
• / .
• , , .
• , .

Simple! -.

Answer 5

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]