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

This article is about how to use Function in SQL to create a long plastic surgery only ID, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Realization method

At the beginning, write a method to gain competition in object-oriented languages such as C#, and that is done in great order.

Then in SQL, if you want to import data with a script, you need to provide a SQL method to get the competition.

The composition of the initial design competition: time (yyMMddHHmmssmsS) +'4-digit random number', so Kaka quickly completed dbo.pk ()

Create function dbo.pk () returns bigintasbegin declare @ competitive as bigint,@fix bigint,@idx int,@ts as datetime set @ ts = GETDATE () set @ competitive = convert (bigint,convert (varchar (6), @ ts,12) + replace (convert (varchar (12), @ ts,114),':',')) * 10000 select @ idx = A10,000 from vRand return (@ competitive + @ idx) endgo

Then get a 10000PK test:

Declare @ tab as table (competitive bigint) declare @ i as integerset @ I = 0while (@ i1)

Oh my god! There are more than 30 duplicates.

It can be seen that this method, as a way to obtain a single competition, is not a big problem, but primary key conflicts may occur when batch saving is done.

So design another one that supports batch saving.

Since 4-digit random numbers cannot guarantee the uniqueness of milliseconds, we can only use ordered numbers to change the composition of competition to: time (yyMMddHHmmssmsS) +'4-digit ordered numbers'.

Considering that the year is only two digits, and there is a chance of conflict with the competitive composition in the target group after 202x, a logo '1'+yy is added as a year to prolong the millennium bug problem. Although there is still a chance of conflict, it will be hundreds of years later.

But in order to maintain the efficiency and the probability of conflict, the competition is changed to:'1 percent + time (yyMMddHHmmssms) + 4-digit ordinal number'.

Then there is another meal of Kaka, dbo.pks (@ count) has come out:

CREATE function dbo.pks (@ count as int) returns @ pks table (competitive bigint,id int) asbegin declare @ competitive as bigint,@fix bigint,@idx int,@ts as datetime,@lop int,@i int set @ ts = GETDATE () set @ competitive = convert (bigint,'1'+convert (varchar (6), @ ts,12) + replace (varchar (11), @ ts,114),':' '') * 10000 set @ idx = 0 set @ lop = CEILING (@ count/10000.0) set @ I = 1 while (@ lop > 0) begin set @ Competition = @ Competition + 10000 set @ idx = 0 while (@ idx)

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.