An implementation method for automatically generating numbering in a database share _mssql

In the use of the database, it is inevitable that the use of the process to delete the operation, if the use of the type of int to grow, this is the simplest way, but the consequences will be some not what you want! Take a look at this demo:

1. Create such a simple table test.

　　

2. Set the field ID to self increase.

3. Table Add Data

Insert into Test (name) VALUES (' TestName ')

Insert into Test (name) VALUES (' TestName ')

Insert into Test (name) VALUES (' TestName ')

4. You will see

　　

5. Here we delete the row with ID 2. Only two rows of data with ID 1 and ID 3 are left. (not on the map)

6. Add another piece of data.

Insert into Test (name) VALUES (' TestName ')

We'll find out that this may not be the result we want.

　　

Why not have an ID of 2? Then let your doom add, also will not have ID 2 of the data line!

This design is convenient, but the devil is in detail, this blog is to solve this problem so that we see the ID 2 row of data (here by the way to improve, so that the result is not just display ID 2 such an int, if one day our households asked us they want a 5-digit ID number, Starting from 00000, OK, that's fine.

1. The protagonist's debut, the stored procedure finally came in handy

Copy Code code as follows:

Create procedure [dbo]. [Insertname]
(@name nvarchar (50))
As
Begin
DECLARE @i int
Set @i=1
while (@i<10000)
Begin
If Exists (select CONVERT (int,id) from numbertest where convert (int,id) =@i)
Begin
Set @i=@i+1
Continue
End
Else
Begin
Insert Numbertest values (right (' 0000 ' +convert (varchar (5), @i), 5), @name)--here's two digits ' 5 ' is the ID length we want to set
Break
End
End
End

2. Call this stored procedure with SQL statement

Execute Insertname Test

You can hit it a few times, dozens of times, hundreds of times, and we're going to add the data,

　　

We can delete the specified ID data row, and when we add it again, the previously deleted ID row will be overwritten by our newly added data so that the ID can be connected.

Oh, yes, haven't said how to show the beginning of ' 0 '? This is simple, set the data type of ID to nvarchar (5), it is so simple!
Summary:
Here we call the stored procedures, stored procedures should not be used, but sometimes it is very convenient to use, this article for the children's shoes just work should be a little help, and study well, life is beautiful!
The feeling of relief ah, finally fix a problem, here thank you for helping my children's shoes! Next time!