Auto-increment of numbers

How to Set an entire line field, which starts from the initial value of 0000001 and increases by 1 for each record.

How do I set the formula in the Enterprise Manager?

Or how to write the code?

How can we use stored procedures and transactions to prevent users from obtaining an ID number at the same time on the client?

Dying. Thank you for your help.

Can it be implemented as "05040001" id?

That is, 05 indicates the Year, and 04 indicates the month. 0001 indicates the serial number. 0001 this will increase.

It also uses stored procedures and transactions to prevent users from simultaneously obtaining an ID number on the client.

Please help me. Please give me some examples. Thank you.

-- The following code shows how to generate a date number. The total length of the number is 8, and the first five digits are the year and month information. The format is yymm, and the last four digits are the serial numbers.

-- Create a view for the current date

Create view v_getdate

As

Select dt = convert (char (4), getdate (), 12)

Go

-- Get the function with the new number

Create Function f_nextbh ()

Returns char (8)

As

Begin

Declare @ DT char (4)

Select @ dt = dt from v_getdate

Return (

Select @ DT + right (10001 + isnull (right (max (BH), 4), 0), 4)

From TB with (xlock, paglock)

Where BH like @ DT + '% ')

End

Go

-- Apply a function to a table

Create Table Tb (

BH char (8) primary key default DBO. f_nextbh (),

Col INT)

-- Insert data

Insert Tb (COL) values (1)

Insert Tb (COL) values (2)

Insert Tb (COL) values (3)

Delete TB where Col = 3

Insert Tb (COL) values (4)

Insert Tb (BH, col) values (DBO. f_nextbh (), 14)

-- Display Results

Select * from TB

/* -- Result

BH col

-------------------

05040001 1

05040002 2

05040003 4

05040004 14

(The number of affected rows is 4)

--*/

Go

Drop table TB

Drop function f_nextbh

Drop view v_getdate

In this way, auto-increment is implemented. But how can we use transactions and locks to prevent different users from obtaining the same ID number at the same time on the client?

The lock mechanism has been applied to the function. As long as you enable the transaction when inserting data, you can prevent different users from obtaining the same ID number at the same time on the client.

-- That is to say, for insert such write

Begin tran

Insert Tb (COL) values (1)

Insert Tb (COL) values (2)

Insert Tb (COL) values (3)

Delete TB where Col = 3

Insert Tb (COL) values (4)

Insert Tb (BH, col) values (DBO. f_nextbh (), 14)

Commit tran

Sorry. How can I change the value of 05 to 2005?

Four-digit date, changed:

-- Create a view for the current date

Create view v_getdate

As

Select dt = convert (char (4), getdate (), 112)

Go

Four years, change:

-- The following code shows how to generate a date number. The total length of the number is 8, and the first five digits are the year and month information. The format is yymm, and the last four digits are the serial numbers.

-- Create a view for the current date

Create view v_getdate

As

Select dt = convert (char (6), getdate (), 112)

Go

-- Get the function with the new number

Create Function f_nextbh ()

Returns char (10)

As

Begin

Declare @ DT char (6)

Select @ dt = dt from v_getdate

Return (

Select @ DT + right (10001 + isnull (right (max (BH), 4), 0), 4)

From TB with (xlock, paglock)

Where BH like @ DT + '% ')

End

Go

-- Apply a function to a table

Create Table Tb (

BH char (10) primary key default DBO. f_nextbh (),

Col INT)

-- Insert data

Insert Tb (COL) values (1)

Insert Tb (COL) values (2)

Insert Tb (COL) values (3)

Delete TB where Col = 3

Insert Tb (COL) values (4)

Insert Tb (BH, col) values (DBO. f_nextbh (), 14)

-- Display Results

Select * from TB

/* -- Result

BH col

-------------------

05040001 1

05040002 2

05040003 4

05040004 14

(The number of affected rows is 4)

--*/

Go

Drop table TB

Drop function f_nextbh

Drop view v_getdate

-- The following code shows how to generate a date number. The total length of the number is 8, and the first five digits are the year and month information. The format is yymm, and the last four digits are the serial numbers.

-- Create a view for the current date

Create view v_getdate

As

Select dt = convert (char (6), getdate (), 112)

Go

-- Get the function with the new number

Create Function f_nextbh ()

Returns char (10)

As

Begin

Declare @ DT char (6)

Select @ dt = dt from v_getdate

Return (

Select @ DT + right (10001 + isnull (right (max (BH), 4), 0), 4)

From TB with (xlock, paglock)

Where BH like @ DT + '% ')

End

Go

-- Apply a function to a table

Create Table Tb (

BH char (10) primary key default DBO. f_nextbh (),

Col INT)

-- Insert data

Insert Tb (COL) values (1)

Insert Tb (COL) values (2)

Insert Tb (COL) values (3)

Delete TB where Col = 3

Insert Tb (COL) values (4)

Insert Tb (BH, col) values (DBO. f_nextbh (), 14)

-- Display Results

Select * from TB

/* -- Result

BH col

-------------------

05040001 1

05040002 2

05040003 4

05040004 14

(The number of affected rows is 4)

--*/

Go

Drop table TB

Drop function f_nextbh

Drop view v_getdate