Original: T-SQL Development--id processing article
The difference between identity, Timestamp and uniqueidentifier in the database self-increment ID function: Problem phenomenon:
The generation of general serial numbers is implemented using T-SQL commands for general programmers. It is quite dangerous to read the largest requirement in the table first, then add one, and then plug it back into the database. Because if the transaction mechanism is not handled well, the same sequence number will appear at the same time. The results are conceivable. In order to avoid this situation, SQL Server has provided a mechanism within the organization to assist in processing.
Description: In SQL Server, there are several mechanisms that automatically generate serial numbers. The first is to automatically generate serial numbers based on the insertion of data to identify each row of data. Called "Identity." function at the same table level. The second is the role at the database level, called the timestamp data type, called rowversion. This allows different data columns in the same database to generate unique identifiers. The third is the data type that appears to produce uniqueidentifier with newid () or newsequentialid (). This type is a unique identifier for the global level. It is claimed that it will not repeat within 3,000 years. Reasonable use of the above three-clock way, can alleviate the burden of the application.
The following is a detailed explanation of each situation: 1, data table level identification--identity:This recognition method is only appropriate at the table level. You only need to match the INSERT statement with the name of the column without specifying it. In addition, it will automatically increment, such as deleting a row in the DELETE statement, and subsequent data will still be added from the nearest line ordinal. Instead of starting from the original definition start again. An example is given below:
Use tempdbgo--creating a data table for test CREATE TABLE employee (en int NOT null identity,--self-increment idename varchar (),--Employee name Keydt datetime--creation date);--insert data, do not specify column name insert INTO employeevalues (' Lewis ', ' 2012/6/23 ');--insert data, specify column name, but do not specify self-increment insert into Employee (ENAME,KEYDT) VALUES (' Ada ', ' 2012/6/24 ') goselect * from Employee
The results are as follows:
For identity, there are some tricks: 1, Identity (n,m): N is the self-increment start value, M is the increment quantity, can realize (n,n+m,n+2m,n+3m ...) Such data. 2. @ @identity System functions: This is used in the execution phase to capture the self-increment number resulting from the most recent insertion of data. Swim very well in the application, such as adding a new data, then getting the ID and then using it for the query display. 3, ident_current (' data table name '): You can find the current maximum self-increment number of the specified table, can replace the Select Max statement, speed up the query. Especially in large concurrency, if you use select Max, you may get an incorrect ordinal, and select Max takes a long time when the table is very large. 4. Scope_identity () function: The self-increment number during the execution of a stored procedure or trigger. But unlike @ @identity, the @ @identity returns the current value in the entire transaction, and this function only returns the number of the new data table in the stored procedure, trigger program. @ @identity the Indentity property in which a transaction has an extension or call to another table is a difference, and this function is mainly used to deal with this problem. The differences between @ @identity and scope_identity () are shown below:
Use tempdbgo--creating a data table for the test CREATE TABLE T1 (XID INT not NULL identity,xname VARCHAR (10)); Gocreate TABLE T2 (Yid INT not NULL identity,yname VARCHAR (10)); go--Insert 3 data into the T2 table insert into T2 (yname) VALUES (' name1 '), (' Name2 '), (' Name3 '); go--build T1 Insert trigger to automatically add T1 data to T2 data table create TRIGGER tri_t1 on T1after insertas insert into T2 (yname) Select XName from I nsertedgo--writing a stored procedure adds data to the T1 data table automatically returns the value of Scope_identity () and @ @Identity create PROC usptest (@name varchar) as insert into T1 VALUES (@name) SELECT @ @IDENTITY ' @ @identity ', scope_identity () ' scope_identity ', ' Proc ' as ' SCOPE ' go--using stored procedure test: When Scope_identity () is 1 o'clock, @ @identity is 4EXEC usptest ' Ada '
Note: The identity as a self-increment, even if the same event does not produce the same sequence number, so can but not be forced as the table's primary index key.
2. Database level identification--timestamp:This function mainly uses the time stamp generated by the database counter to generate recognition for each data. The attribute of this data is timestamp, also known as rowversion. Produces a unique stamp value for any data table of the specified database. The stamp value is a binary data type with a length equal to varbinary (8). In addition, this type will change the original timestamp value based on subsequent modifications to this line of data. Because of its dynamic nature, it is evaluated when it is selected as the index value.This value can be obtained using the @ @DBTS system function.The following is the sample code:
Use tempdbgo--create a data table for Southern Employees creation table employee_s (en timestamp not null,--self-increment binary idename varchar (),--Employee name Keydt datetime--creation time)--Create a data table for the Middle Employee Creation table Employee_c (en timestamp not null,--self-increment binary idename varchar (),--Employee name Keydt datetime--creation time)--Creating a data table for the North Employee CREATE TABLE employee_n (en timestamp not null,--self-increment binary idename varchar (),--Employee name Keydt datetime--creation time)--insert data: INSERT INTO employee_s (ENAME,KEYDT) VALUES (' Sname ', GETDATE ()) insert into Employee_c (ename, KEYDT) VALUES (' Cname ', GETDATE ()) insert into Employee_n (ENAME,KEYDT) VALUES (' Nname ', GETDATE ())--Display data select ' South ', * From Employee_sunion allselect ' central ', * from Employee_cunion allselect ' North ', * from Employee_n
The results are as follows:
The date that you see the data after executing the script is the same, but the en column is not the same, and this effect is not the identity.
3. Use NEWID () with uniqueidentifier data to generate globally unique identifiers: This value generates a globally unique identifier by randomly collocation multiple configuration information. The following is a sample code:
Use tempdbgo--Create data table for Southern Employees creation table Employee_guid (en uniqueidentifier not null,--self-increment binary idename varchar (50) – Employee name)-- Insert data: INSERT INTO Employee_guid (en,ename) VALUES (NEWID (), ' Sname '), (newid (), ' Cname '), (newid (), ' nname ')--display data, To prove not unique, you can use GROUP by to verify:--source data Select *from employee_guid--test Data Select COUNT (1) ' Total ', Enfrom employee_guidgroup by Enhaving count (1) >1
In addition, as mentioned earlier, you can use NEWID () and newsequentialid () to produce, taking into account the difference between the use of newid () and newsequentialid ():
After execution you can see: note that each machine value will be different
As you can see from the diagram, Newsequentialid () produces a sequential GUID value (the first part of the observation value), which can be useful in comparison. The NEWID () is a value with no order. Note: 1. When using identity as the identity of a row, the next used number cannot be retained in conjunction with the use of the transaction. That is, when the transaction occurs rollback, will still go out a number, and will not be released, will cause the phenomenon of jumping. 2. Use truncate to reset the identity's last recognized value. The delete calculation deletes all data, and the next row of data will still start from the previous one and will not start again. 3. When using the timestamp type, it is only suitable for those data that will not update the operation. Because the timestamp value is updated.
Custom generation via Stored procedures: Problem phenomenon: In many cases, because of the use of the need, often can not only rely on the above mentioned in the 3 way to generate serial number. And to combine them into meaningful numbers. However, this situation makes it difficult to ensure that data is not duplicated when inserting a database.
Description: This situation is easily seen when multiple people invoke the program. You can start with a front-end application, or you can develop some functionality from the database to produce the serial number uniformly. Either way, you have to do the following 3 points to solve the problem: 1, to the number of the process, according to can not occur duplication. 2, give the speed of the shorter the better. 3, some application requirements, all give out the serial number. There can be no jumping number case.In this case, it is recommended to mix the front and back end procedures to ensure that when using the stored procedure year, the output parameter is recommended for the release of the sequence number. Avoid callbacks using datasets, because using output parameter outputs can reduce resource usage and speed up operations.In addition, with the SET XACT_ABORT on option of the database, and the begin Transaction/commit TRANSACTION expression, the lost Updae of the transaction is guaranteed not to occur during each sequence. Here are some sample code:
Use tempdbgo--CREATE TABLE TABSN (SN int,sndt datetime) go--CREATE TABLE tabsnhist (SN int,sndt datetime) go--create proc USPSN (@sn char) output as--start transaction set XACT_ABORT on BEGIN TRANSACTION--Determine if the list has data, if not, add a new data if (select Count (1) from TABSN) =0begininsert into TABSN values (000000,getdate ()) end--Remove date declare @sndt datetimeset @sndt = ( Select Sndt from TABSN);---determine if the cross-day situation occurs, if you move to the history table if CONVERT (char (Ten), @sndt, 111) <>convert (char (Ten), GETDATE (), 111) Begininsert to tabsnhist select * from tabsn;truncate table Tabsn;insert into TABSN values (000000,getdate ()) end--adds 1 to the number as the last operating time update TABSN set sn=sn+1, Sndt=getdate ()--go out the serial number, convert to Yyyymmddnnnnnnselect @sn =convert (VARCHAR (10), sndt,112) +right (' 000000 ' +convert (VARCHAR (6), SN), 6) from TABSN; COMMIT transactiongo--uses stored procedure to generate ordinal declare @SN CHAR EXEC uspsn @SN outputselect @SN ' SN '
A simple stress test can be done to verify that this type of writing produces duplicates:
--stress test--create table to store test results in the CREATE TABLE test (sn char, SDT datetime, Scomm varchar (100)-who executed the stored procedure)
The following code is executed at the same time in 4 windows:
DECLARE @cnt intset @cnt =1while @cnt <=100begin--Execute stored procedure declare @sn char exec uspsn @sn output--add results to test data table insert Into Testselect @sn, GETDATE (), ' SPID ' +convert (varchar (5), @ @spid) set @[email protected]+1waitfor delay ' 00:00:01 ' Endgo
You can use the following statement to test for duplicates:
Select COUNT (1), SN from test GROUP by SN have count (1) >1
Of course, the result is no repetition. You can also check if there is a jump number condition:
--Check whether the jump number occurs: SET NOCOUNT on DECLARE @T TABLE (TID INT) DECLARE @MAX INT, @MIN intset @MIN = (SELECT CONVERT (Int,right (MIN (SN), 6 ) from test) SET @MAX = (SELECT CONVERT (Int,right (MAX (SN), 6)) from Test and while @MIN <[email Protected]begininsert to @T VALUES (@MIN) SET @[email protected]+1endselect TID ' discontinuous number ' from @T EXCEPT SELECT CONVERT (Int,right (sn,6)) from TEST SET NOC Ount OFF
By checking that there is no jump number. And the end result:
SELECT * FROM Test ORDER by SN
There are no duplicate and hop numbers for the data.
By instead of the trigger, the implementation of the custom sequence number: The problem: When the need to support large-scale data insertion, but also has the ability to produce independent dedicated serial number and so on.
DescriptionIf you want to have a feature that automatically generates a sequence number or a custom complex ordinal in a stored procedure, you can use the instead of trigger in the new case because it replaces the new action and changes the way the insert operates by its own special definition.However, if a statement such as Insert/update/delete does not appear after instead, the trigger will be invalid.
Workaround: The following code uses the instead of trigger to implement batch additions, and numbering starts at 000001 based on the total number of orders per day. The format is yyyymmdd.nnnnnn.
Use tempdbgo--Create order table, order number is primary index key cannot be duplicated--creation time using GETDATE () value CREATE TABLE Fruitorderlist (Orderidvarchar () NOT NULL primary Key,prodidint,qtyint,regionvarchar (Ten), keyindtdatetime Default (GETDATE ())); go--creating instead OF trigger create TRIGGER tri_int_fruitorderlist on Fruitorderlistinstead of INSERT as SET NOCOUNT on declare @oSN varchar (20)--Generate new sequence Number rule = date + (total number of strokes +1) SELECT @oSN =convert (VARCHAR), GETDATE (), 112) + '. ' +right (' 000000 ' +convert (VARCHAR (6), COUNT (1) +1), 6) from Fruitorderlistwhere CONVERT (char (Ten), keyindt,111) =convert ( CHAR (Ten), GETDATE (), 111)--re-add the data insert into Fruitorderlistselect @oSN, Prodid,qty,region,keyindtfrom insertedset NOCOUNT Offgo
You can then try to do a BULK insert:
--Test action:--Add data, note that the order number is automatically generated: INSERT INTO Fruitorderlist VALUES (null,3,30, ' A ', GETDATE ()) insert INTO Fruitorderlist VALUES (null,6,10, ' B ', GETDATE ()) insert into fruitorderlist values (null,9,20, ' C ', GETDATE ()) insert INTO Fruitorderlist VALUES (null,12,40, ' D ', GETDATE ()) SELECT * from Fruitorderlistgo
As you can see from the results: the desired effect is really achieved.
Note: 1, INSTEAD of the trigger execution time, will be before the condition constraint primary key. 2, the execution process, you can use inserted record the new data, the latter modified data, so that the Delte record deleted data or the data before modification. 3. Avoid using the cursor in the definition process, you can use inserted or deleted to get the data directly. Achieve the loop effect.
Automatically add an ordinal when the front-end application outputs: issue: When the front-end application presents the data, it is expected to be automatically numbered. WORKAROUND: You can use the Row_number () function, using the method: Row_number () over ([split clause]< sort clause >) use Row_number () to resolve the automatic generation of the sequence number, you need to specify which data to sort.
Use adventureworksgo--uses FirstName to sequence the output of the ordinal select Row_number () over (ORDER by FirstName), Firstname,jobtitle, Emailaddressfrom humanresources.vemployeewhere jobtitle like '%engineer% ' GO
Note: the Row_number () function's order by and select's order by inconsistencies affect the input results
T-SQL Development--id processing article
