Abandon the auto-increment ID of the database and expose the solution I used)

During Normal project development, I believe that a large batch of people are using this database's auto-incremental ID, which has both advantages and disadvantages.

Advantage: saves time and does not need to consider how to identify unique records and writeProgramIt's also easy. The database helps us maintain this batch of idnumbers.

Disadvantage: for example: When data synchronization is required for distributed databases, this auto-increment ID will cause serious problems because you cannot use this ID to uniquely identify records. At the same time, a variety of problems may occur during database migration. In short, problems may occur when auto-increment IDs are dependent. I absolutely believe that many people in the garden have been hurt by this "useful thing!

I usually do not use the auto-increment ID of the database when developing projects, so I would like to share my solutions.

Solution:

1: Define a table to store the names of all tables with unique IDs and the IDS currently used by the table.
2: Write a stored procedure to obtain the id value from the table in the previous step.

This idea is very simple. I will not explain it. Let's look at my implementation method directly:

 

 

Code

Step 1: Create a table
Create   Table Table_key
(
Table_name Varchar ( 50 ) Not   Null   Primary   Key ,
Key_value Int Not   Null
)

Step 2: Create a stored procedure to retrieve the incremental ID
Create   Procedure Up_get_table_key
(
@ Table_name Varchar ( 50 ),
@ Key_value Int Output
)
As
Begin
Begin   Tran
Declare   @ Key Int

-- Initialize the key with 1
Set   @ Key = 1
-- Whether the specified table is exist
If   Not   Exists ( Select Table_name From Table_key Where Table_name = @ Table_name )
Begin
Insert   Into Table_key Values ( @ Table_name , @ Key ) -- Default key vlaue: 1
End
-- Step increase
Else
Begin
Select   @ Key = Key_value From Table_key With (Nolock) Where Table_name = @ Table_name
Set   @ Key = @ Key + 1
-- Update the key value by table name
Update Table_key Set Key_value = @ Key   Where Table_name = @ Table_name
End
-- Set ouput Value
Set   @ Key_value = @ Key

-- commit Tran
commit Tran
If @@ error > 0
rollback Tran
end

 

 

If no record exists in the table, a key value with the default value of 1 is directly returned, and the record is inserted into the table_key table. For existing records, the key value is directly added to the original key.

 

To sum up, this method is very simple. Let me talk about its advantages and disadvantages.

Advantages:

1: The id value is controllable. You can assign ID values from a specified segment, which is very convenient for Distributed Data Synchronization and solves the problem of duplicate IDs.

2: in programming, the ID value is visible. For example, when you insert an associated record, this method does not need to get the auto-increment id value after inserting a database record, and then use this ID value to insert the associated record. We can use transactions to insert associated records at one time.

3: When data needs to be inserted in batches, we can rewrite the above stored procedure, return the start ID of a segment, and then note when updating the table, instead of simply increasing 1, instead, increase the total number of records you want to insert.

Disadvantages:

1: efficiency problem. Every time you get the id value, you need to call the stored procedure to retrieve it from the database. In this case, I think the efficiency is not a big problem, because SQL server will cache the stored procedures that we often call. Another point, the data in this table should not be very large, there can be a maximum of thousands of tables in a project ). Therefore, retrieval is not a problem, not to mention Retrieval Based on the table name (the table name is already the primary key ).

2: concurrency issues.

Many people have mentioned this! However, everything is a double-edged sword. This is like optimization. You can either change time for space or time for space. There is no perfect thing in the world!

For personal accounts, see! That's all!

 

Forwarded from: http://www.cnblogs.com/repository/archive/2011/01/17/1937265.html