sql - 如何向 SQL Server 2008 中的列添加自动增量

Question

我使用的是 SQL Server 2008 并且我使用的数据库表的主键不是 IDENTITY列(不知道为什么)。我需要改变这一点。

我在 SQL Server Management Studio 的设计 View 中，在列属性下，出于某种原因，我无法将标识规范更改为 Yes .

有什么我遗漏的吗..我是 SQL Server 的新手 - 关于我遗漏的任何想法？

这是创建表

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]

这是我需要运行的查询......它是一个完整的技巧，试图自己自动增加我的插入

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: - 正如 Mikael Eriksson 正确地提到(并且很好地记录)，只要您正在处理的该列具有默认约束，此身份规范就会保持灰色。