Shulou(Shulou.com)05/31 Report--

What are the ways to generate the primary key of sqlserver database? in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and easy way.

There are three main ways to generate primary keys:

one。 Automatic database generation

two。 GUID

three。 Development and creation

Strictly speaking, these three generation methods have a certain intersection, and their positioning methods will be explained below.

In the first way, it is mainly located in the self-growing logo seed: you can set the starting value, and the growth step size. It has the advantage of completely handing over the concurrent tasks to the database engine management when using it, so you don't have to worry about the situation that two identical ID will be generated when multiple users use it. The disadvantage is that most databases do not provide a way to directly obtain the identity ID, and the way in which ID is generated is transparent to developers, and developers can hardly interfere with this. It is also not very convenient for data migration. Due to the above advantages and disadvantages, this self-growing ID is generally used to design the primary key of the basic table (the basic information for the operation of the system, such as the employee table), but rarely (not at all) for the master and foreign keys of the master-slave table, because when the master-slave table data is generated and associated, the ID of the master table must be determined before the associated ID of the slave table can be located.

Example (MsSQL):

The code is as follows:

-- create a test table CREATE TABLE [Identity] (Id INT IDENTITY (1) NOT NULL PRIMARY KEY)

-seed starting value 1, step size 2 Number VARCHAR (20) UNIQUE NOT NULL, Name VARCHAR (20) NOT NULL, Password VARCHAR (20) DEFAULT (123), Description VARCHAR (40) NULL)

-- insert the record INSERT INTO [Identity] (Number,Name,Description) VALUES ('001' because the starting value is 1')

INSERT INTO [Identity] (Number,Name,Description) VALUES (because the starting value is 1 and the step size is 2')

INSERT INTO [Identity] (Number,Name,Description) VALUES (because the character length is too long and the error insertion failed, this Id is abandoned after it is generated)

INSERT INTO [Identity] (Number,Name,Description) VALUES ('004 record 7 not 5 because the third record insertion failed')

-- retrieve the record and view the result SELECT * FROM [Identity]

The result: (1 line is affected) (1 line is affected) message 8152, level 16, status 14, line 3 truncates string or binary data. Statement terminated. (1 line affected) (3 lines affected) Id Number Name Password Description 1 001 1st 123 Id=1, because the starting value is 1 3002 2nd 123 Id=3, because the starting value 1, step size 2 7004 4th 123 Id=7 not 5, because the third record insertion failed the second way, GUID is Globally Unique Identifier, also known as UUID (Universally Unique IDentifier), globally unique identifier, GUID generally consists of 32-bit hexadecimal values, which contain the address, time and other information of the network card. No two computers will produce the same GUID. Its advantage lies in its uniqueness. When database integration is needed, it can save a lot of labor. For example, the head office and branch companies run their own systems independently, and all branch data need to be submitted to the headquarters on a regular basis, which can avoid the problem of primary key conflicts when merging data. At the same time, GUID also has the characteristics of self-growing identification seed, which does not need too much attention from developers. However, GUID has a large amount of information and takes up a lot of space, and its estimation efficiency is not very high in association retrieval, and its readability is poor for 32-bit hexadecimal. Although the primary key is meaningless to users, it is very inconvenient to design or debug communication. In the long run, in order to ensure the portability of the data, you will generally choose to use GUID as the primary key.

Example (MsSQL):

The code is as follows:

-- create a test table CREATE TABLE GUID (Id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY

Of course, you can also use strings to save Number VARCHAR (20) UNIQUE NOT NULL, Name VARCHAR (20) NOT NULL, Password VARCHAR (20) DEFAULT (123))

-- insert the record INSERT INTO GUID (Id,Number,Name) VALUES (NewID (), '001records record1st')

INSERT INTO GUID (Id,Number,Name) VALUES (NewID (), '002')

INSERT INTO GUID (Id,Number,Name) VALUES (NewID (), '003d`)

-- retrieve the record and view the result SELECT * FROM GUID

Results: Id Number Name Password 8E194F55-B4D3-4C85-8667-33BC6CD33BBC 001 1st 123 7141F202-7D0E-4992-9164-5043EC9FC6F6 002 2nd 123 E0E365A0-8748-4656-AF24-5D0B216D2095 003 3rd 123 was developed and created in the third way, its convenience lies in controllability, which refers to its component form, which can be plastic or character type, and you can give various forms of composition and generation according to the actual situation. At this point, some friends may think of automatically generating order numbers, such as 20120716001 or PI-201207-0001, etc., yes, self-creation is also applicable to these similar applications. When it comes to self-creation, most of the first thing that comes to mind is to take Max (Id) + 1, which is convenient, but actually for customization (there may be such a need for meaningful information such as the production order number, the primary key is not necessary) and concurrent processing is not very good. For example, the maximum number in the current table is 1000, and when C1 and C2 users take this Id at the same time, they both get 1001, resulting in a save failure. The conventional practice is to add locks when taking values, but when multi-users operate frequently, performance is a big problem, one of the main reasons is the direct manipulation of business data tables. In view of this situation, the solution is to use the key-value table to save the table name, current or next Id and other information. If multiple tables Id in the system use this way, then there will be multiple corresponding rule records in the key-value table; of course, you can also make the Id of all tables in the entire database be generated from a source according to the same rule, then only one rule is needed in the key-value table.

Let's take a look at the evolution of such an example of using a key-value table (MsSQL):

The code is as follows:

-- create a key-value table CREATE TABLE KeyTable (ID INT IDENTITY (1) PRIMARY KEY NOT NULL)

TCode VARCHAR (20) UNIQUE NOT NULL

TName VARCHAR (50) NOT NULL

TKey INT NOT NULL,)

GO

-- insert the test record INSERT INTO KeyTable (TCode,TName,TKey) VALUES ('T001recording, recording, testing, recording, writing, and writing, etc.).) GO

Create a stored procedure that gets the specified table ID, or modify it to the function CREATE PROCEDURE UP_NewTableID @ TCode VARCHAR (20), @ NextID INT OUTPUT AS DECLARE @ CurTKey INT

@ NextTKey INT BEGIN TRAN TransID SELECT @ CurTKey=TKey FROM KeyTable

WHERE TCode = @ TCode IF @ @ ROWCOUNT = 0 BEGIN ROLLBACK TRAN TransID RAISERROR ('Warning: No such row is exists',16,1) RETURN END SET

@ NextTKey = @ CurTKey + 1-- WAITFOR DELAY'00 UPDATE KeyTable SET TKey 05' UPDATE KeyTable SET TKey = @ NextTKey

WHERE TCode = @ TCode IF @ @ ROWCOUNT = 0 BEGIN ROLLBACK TRAN TransID RAISERROR ('Warning: No such row is updated',16,1) RETURN END COMMIT TRAN TransID SET @ NextID = @ NextTKey GO

Execute stored procedure UP_NewTableID

The code is as follows:

DECLARE @ NextID INT EXEC UP_NewTableID 'T001'

@ NextID OUTPUT PRINT

When @ NextID runs, you will find that it is normal and get the correct results. However, in the case of high concurrency, multiple users may get the same ID, and if the acquired ID is used to save the records in the corresponding table, then at most one user can save it successfully.

Let's simulate the concurrency situation, remove the comment of the statement WAITFOR DELAY '00virtual 05' in the above stored procedure UP_NewTableID, open the forms of the three query parsers, and execute the above statements in turn. I expect to get 1, 2, 2, 3 respectively, but you may find that multiple forms run as follows: 1. This means that before the update statement is executed, everyone gets an ID of 0, so the next value is 1. (the actual value is related to the interval between the parameters of DELAY and the running time.) if you analyze it from this point of view, some friends may wonder whether it is possible to judge whether ID is the original ID when the update statement is executed.

Modification process:

The code is: ALTER PROCEDURE UP_NewTableID @ TCode VARCHAR (20)

@ NextID INT OUTPUT AS DECLARE @ CurTKey INT

@ NextTKey INT BEGIN TRAN TransID SELECT

@ CurTKey=TKey FROM KeyTable WHERE TCode=@TCode IF @

@ ROWCOUNT=0BEGIN ROLLBACK TRAN TransID RAISERROR ('Warning: No such row is exists',16,1) RETURN END SET @ NextTKey=@CurTKey+1 WAITFOR DELAY' 0015 05' UPDATE KeyTable SET TKey=@NextTKey WHERE TCode=@TCode AND TKey=@CurTKey-- add the TKey check IF @ @ ROWCOUNT=0BEGIN ROLLBACK TRAN TransID RAISERROR ('Warning: No such row is updated',16,1) RETURN END COMMIT TRAN TransID SET @ NextID=@NextTKey GO

If you open three execution procedures to simulate concurrency, two forms will appear: message 50000, level 16, state 1, procedure UP_NewTableID, and line 28 Warning: No such row is updated will see that user operations will still fail due to concurrency, but at least in advance of the previous point in time. So is there a better way, from the query to the update end of the transaction, there will not be any other transactions inserted into it to disturb it, the answer is very clear, yes, use locks! You need to select the appropriate lock, otherwise the effect will be the same as above.

The code is as follows:

ALTER PROCEDURE UP_NewTableID

@ TCode VARCHAR (20), @ NextID INT OUTPUT AS DECLARE

@ CurTKey INT,@NextTKey INT BEGIN TRAN TransID SELECT

@ CurTKey=TKey FROM KeyTable WITH (UPDLOCK)

-- adopt an update lock and hold it until the transaction completes WHERE TCode=@TCode IF @ @ ROWCOUNT=0BEGIN ROLLBACK TRAN TransID RAISERROR ('Warning: No such row is exists',16,1) RETURN END SET

@ NextTKey=@CurTKey+1 WAITFOR DELAY '00VOUR 05'

UPDATE KeyTable SET TKey=@NextTKey WHERE TCode=@TCode

-- there is no need to verify whether TKey is the same COMMIT TRAN TransID SET @ NextID=@NextTKey GO as SELECT.

You can open N (N > = 2) forms to test, and you will see that all operations are serialized, and the result is exactly what we want. This can be done by commenting or removing the statement that mimics the concurrency of WAITFOR DELAY '00VOG 05'.

As mentioned earlier, this is also suitable for the generation form of document numbering similar coding, as long as the previous code and key value table can be slightly modified, interested friends can have a try. If you get this number from the front end and apply it to each record, there may be the possibility of a jump mark. If you want to ensure that there is no hopping, one solution is to use a hopping table that periodically scans the hopping record and applies it to other records. Another solution is to place the record saving operation in the numbering process to form a serialized transaction.

This is the answer to the question about how to generate the primary key of sqlserver database. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.