[Database design] Notes for "Max plus one" Primary Key Generation

ArticleDirectory

• 1. efficiency problems
• 2. Concurrent Insertion

The primary keys of SQL Server databases are designed in a variety of ways, such as classic auto-growth, happy guids, and IDs generated by time (throughProgramGenerate a meaningful id based on the combination of the business model. Recently, the project encountered a method to simulate auto-increment primary keys, that is, "Max plus one ".

The principle of Max plus 1 seems to be similar to that of auto-growth. The unique primary key of a table is also designed to be numeric (for example, bigint), but the automatic growth is removed (in the table designer logo specification column, select No for the "yes" option ). During the insert record operation, the general process is as follows: Read the max primary key value of the current table, add one, and then follow the passed parameters,Insert primary key explicitlyAnd other column values. One of the most significant advantages of this primary key generation method is that you can generate a primary key according to your own rules. For example, the User-Defined Stored Procedure usp_getnewid that generates the primary key is as follows:

 If   Exists ( Select * From DBO. sysobjects Where Id = object_id (n '[DBO]. usp_getnewid' ) And Objectproperty (ID, n 'Isprocedure' ) = 1) Drop   Procedure [DBO]. usp_getnewid Go  Create   Procedure [DBO]. usp_getnewid @ tablename nvarchar (30), -- table name @ columnname nvarchar (30), -- field name @ newid Int   Output -- Max (ID) generated new ID As  Begin  Declare @ Maxid bigint Declare @ Maxidtemp bigintDeclare @ SQL Nvarchar (500) Declare @ Sqldbid nvarchar (8) Set @ Sqldbid = '10' -- Get the original maximum ID Set @ SQL = 'Select @ mymaxid = isnull (max (' + @ Columnname + '), 101) from' + @ Tablename; -- Select @ maxid  If @ Error = 0 Begin      Exec Sp_executesql @ SQL , N'@ Mymaxid bigint output' , @ Maxid Output  End -- Generate a new ID If @ Error = 0 Begin       Select @ Maxidtemp = Substring ( Cast (@ Maxid As Nvarchar), 3,100) + 1; End  If @ Error = 0 Begin      Set @ Newid = @ sqldbid + Cast (@ Maxidtemp As Nvarchar)End  Else  Begin      Set @ Newid =-1 End End

By combining @ sqldbid = '10' and set @ newid = @ sqldbid + Cast (@ maxidtemp as nvarchar, we can control the primary keys generated by different database servers (or different databases). For example, the IDS generated by the first Server start with 10 and those generated by the second Server start with 20, so far, this is conducive to the distributed management of databases.

The following describes two major defects of this method:

1. efficiency problems

Although the primary key has a clustered index, when the data in our data table reaches an order of magnitude (for example, 10 million), the max value of the aggregate function will certainly have a large cost, this will obviously affect the efficiency. But what is the comparison between efficiency and self-growth performance? I really do not have any test data in this area. If you have experience in this area, please kindly advise and beg for help.

【Update]: According to today's performance test, data is inserted on the basis of 1 million data records in the Table. Each time 10000 records are inserted, the difference between auto increment and Max increment is less than one second, in general, the self-growth method is a little faster, but not obvious, within the acceptable range. For test results, see:

2. Concurrent Insertion

This problem certainly does not occur when we insert data sequentially in the program. However, in most applications, some data is often processed concurrently. In this case, using the max plus method will cause the insert concurrency problem. Because if two or more insert requests read the same max value plus one at the same time, an error will occur when inserting duplicate primary keys.

We can perform a simple test:

(1) Add the user's stored procedure usp_adduser

 If   Exists (Select * From DBO. sysobjects Where Id = object_id (n '[DBO]. usp_adduser' ) And Objectproperty (ID, n 'Isprocedure' ) = 1) Drop   Procedure [DBO]. usp_adduser Go  Create   Procedure [DBO]. usp_adduser As  Begin  Declare @ ID bigint Exec Usp_getnewid'Users' , 'Id' , @ ID Out   Select @ ID If @ Error = 0 Begin Insert Into Users (ID, name) Values (@ ID, 'Jeff Wong' ) End End

The User table is designed to have two fields: ID and name. during insertion, the name value is invariably set to "Jeff Wong" without any interference ".

(2), and then call the following in the application:

        Static   Void Main ( String [] ARGs ){Int Counter= 2000; Action action = Null ; For ( Int I = 0; I <counter; I ++) {Action = adduser; action. begininvoke (a) =>{ action method = A. asyncstate As Action; method. endinvoke (a) ;}, action) ;}console. Read ();} Private   Static   Void Adduser (){ Try { Using (VAR conn = New Sqlconnection (sqlconnstring) {sqlcommand cmd = New Sqlcommand ( "Usp_adduser" , Conn); cmd. commandtype = commandtype. storedprocedure; Conn. open (); Int Result = cmd. executenonquery ();}} Catch (Exception ex) {console. writeline (ex. tostring ());}}

During the local tests tonight, an exception occurs when duplicate primary keys are inserted. This problem was not found in a data synchronization program in the past few days, because the database had no or few qualified data to be synchronized at that time. Of course, all the synchronization operations have now been changed to Using cursor sequence processing in the stored procedure, so as to reasonably solve the Concurrent Insertion problem.

Finally, I feel that there are still a lot of things to mine for primary key generation and some knowledge to discuss in depth, such as how auto-growth controls concurrent inserts, and so on, your comments and suggestions are welcome.