Auto Increment a Non-Identity Column in Sql-Server

Auto Increment a non-identity Column in sql-server

Well, you can use SEQUENCE statement introduced in SQL Server 2012 brings the method of generating IDs

To use it in insert statement, you need to first create sequence like this -

CREATE SEQUENCE dbo.Id_Sequence
    AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 0
    NO MAXVALUE

Now use it in your insert statement like this -

INSERT  INTO dbo.Test1
        ( orderid ,
          custid ,
          empid
        )
        SELECT NEXT VALUE FOR dbo.Id_Sequence,
                @custid ,
                @empid

That's it.

How to Auto-Increment Non-Primary Key? - SQL Server

You can't have more than one identity column per table. I think your best bet would be to pull the PO data into a separate table, then relate the two with a FK column.

SupplierQuote
-------------
supplierQuoteID (PK/identity)
purchaseOrderID (FK to PurchaseOrder.purchaseOrderID)
otherColumn1

PurchaseOrder
-------------
purchaseOrderID (PK/identity)
otherColumn1

MS SQL Auto Increment non Identity Column

This is after Update as discussed in the comments. It simply increments Version of this row.

ALTER TRIGGER triggerIncrementUpdate
ON Table1
AFTER UPDATE

AS
BEGIN

UPDATE Table1 SET Version += 1 FROM Table1 INNER JOIN INSERTED As I ON Table1.ID = I.ID

END

non-identity column auto increment

To get row_number() to start at 55, you could just add 54 (or whatever number) to your row_number() calculation:

(row_number() over (partition by Y order by X)) + 54

Auto-incrementing a non-identity field

I think we can create a table as given below for your purpose

CREATE TABLE [LUName]
(
id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
Name CHAR(25),
EmpID AS id + 100,
InsertTS DATETIME
)

The INSERT script will be as given below.

INSERT INTO [LUName] VALUES
('Bless',GETDATE()),('Sus',GETDATE()),('Sani',GETDATE()),('Sumi',GETDATE())

The resulting Data will be as given below.

SELECT * FROM [LUName]

How can I auto-increment a column without using IDENTITY?

I'm not sure if this is what @Stephen Wrighton had in mind, but I think you could have an insert trigger make use of the IDENTITY value being generated for AnswerRowId:

CREATE TRIGGER [dbo].[AnswerRowInsertTrigger]
   ON  [dbo].[AnswerRow]
   AFTER INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    UPDATE a SET a.SortOrder = a.AnswerRowId
    FROM AnswerRow a JOIN inserted i ON a.AnswerRowId = i.AnswerRowId

END

SQL Server auto increment a column without primary key

Yes. There is no requirement that IDENTITY columns be made a primary key.

CREATE TABLE T
(
X INT PRIMARY KEY,
Y INT IDENTITY(1,1)
)

Though I'm not sure when this would be useful. If you have a natural key that you want to use as the PK then you would probably want to put a unique constraint on the surrogate alternate key anyway.

For purposes of setting up FK relationships SQL Server doesn't care if the column(s) is the PK or not it just requires a unique index on it/them.

Sql server not unique auto-increment column

A simple sequence can provide the auto-incrementing functionallity:

CREATE SEQUENCE seq_numberInMonth as int START WITH 1 INCREMENT BY 1;

CREATE TABLE [dbo].[Order] (
    [Id]                INT             IDENTITY (1, 1) NOT NULL,
    [NumberInMonth]     INT             NOT NULL DEFAULT(next value for seq_numberInMonth),
    [Amount]            DECIMAL (18, 2) NOT NULL,
    CONSTRAINT [PK_dbo.Order] PRIMARY KEY CLUSTERED ([Id] ASC)
);

Test:

INSERT INTO [Order] (Amount) VALUES (12.0), (13.0), (14.0)

SELECT *
FROM [Order]

results:

Id      NumberInMonth   Amount
1       1               12,00
2       2               13,00
3       3               14,00

You can quite easily create a scheduled job to run every 1st of the month and reset the sequence:

ALTER SEQUENCE seq_numberInMonth RESTART WITH 1 ;  

Creating the job in t-sql can be done like this: (didn't test it, but it should work according to the following links):

How to: Create a SQL Server Agent Job

Create a Job

USE msdb ;  
GO  
EXEC dbo.sp_add_job  
    @job_name = N'seq_numberInMonth reset' ;  
GO  
EXEC sp_add_jobstep  
    @job_name = N'seq_numberInMonth reset',  
    @step_name = N'1st',  
    @subsystem = N'TSQL',  
    @command = N'ALTER SEQUENCE seq_numberInMonth RESTART WITH 1 ;',   
    @retry_attempts = 5,  
    @retry_interval = 5 ;  
GO  
EXEC sp_add_schedule @schedule_name =  'every 1st of the month' 
    , @enabled = 1
    , @freq_type = 16
    , @freq_interval = 1

GO  
EXEC sp_attach_schedule  
   @job_name = N'seq_numberInMonth reset',  
   @schedule_name = N'every 1st of the month';  
GO  
EXEC dbo.sp_add_jobserver  
    @job_name = N'seq_numberInMonth reset';  
GO

---

Related Topics