Guarantee random inserting

  • A+
Category:Languages

I am trying to pregenerate some alphanumeric strings and insert the result into a table. The length of string will be 5. Example: a5r67. Basically I want to generate some readable strings for customers so they can access their orders like www.example.com/order/a5r67. Now I have a select statement:

;WITH      cte1 AS(SELECT * FROM (VALUES('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'),('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('i'),('j'),('k'),('l'),('m'),('n'),('o'),('p'),('q'),('r'),('s'),('t'),('u'),('v'),('w'),('x'),('y'),('z')) AS v(t)),     cte2 AS(SELECT * FROM (VALUES('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'),('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('i'),('j'),('k'),('l'),('m'),('n'),('o'),('p'),('q'),('r'),('s'),('t'),('u'),('v'),('w'),('x'),('y'),('z')) AS v(t)),     cte3 AS(SELECT * FROM (VALUES('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'),('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('i'),('j'),('k'),('l'),('m'),('n'),('o'),('p'),('q'),('r'),('s'),('t'),('u'),('v'),('w'),('x'),('y'),('z')) AS v(t)),     cte4 AS(SELECT * FROM (VALUES('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'),('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('i'),('j'),('k'),('l'),('m'),('n'),('o'),('p'),('q'),('r'),('s'),('t'),('u'),('v'),('w'),('x'),('y'),('z')) AS v(t)),     cte5 AS(SELECT * FROM (VALUES('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'),('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('i'),('j'),('k'),('l'),('m'),('n'),('o'),('p'),('q'),('r'),('s'),('t'),('u'),('v'),('w'),('x'),('y'),('z')) AS v(t)) INSERT INTO ProductHandles(ID, Used) SELECT cte1.t + cte2.t + cte3.t + cte4.t + cte5.t, 0 FROM cte1 CROSS JOIN cte2 CROSS JOIN cte3 CROSS JOIN cte4 CROSS JOIN cte5 

Now the problem is I need to write something like this to get a value from the table:

SELECT TOP 1 ID  FROM ProductHandles WHERE Used = 0 

I will have index on the Used column so it will be fast. The problem with this is that it comes with order:

00000 00001 00002 ... 

I know that I can order by NEWID(), but that will be much slower. I know that there is no guarantee of ordering unless we specify Order By clause. What is needed is opposite. I need guaranteed chaos, but not by ordering by NEWID() each time customer creates order.

I am going to use it like:

WITH cte as (                 SELECT TOP 1 * FROM ProductHandles WHERE Used = 0                 --I don't want to order by newid() here as it will be slow             ) UPDATE cte  SET Used = 1 OUTPUT INSERTED.ID 

 


If you add an identity column to the table, and use order by newid() when inserting the records (that will be slow but it's a one time thing that's being done offline from what I understand) then you can use order by on the identity column to select the records in the order they where inserted to the table.

From the Limitations and Restrictions part of the INSERT page in Microsoft Docs:

INSERT queries that use SELECT with ORDER BY to populate rows guarantees how identity values are computed but not the order in which the rows are inserted.

This means that by doing this you are effectively making the identity column ordered by the same random order the rows where selected in the insert...select statement.

Also, there is no need to repeat the same cte 5 times - you are already repeating the cross apply:

CREATE TABLE ProductHandles(sort int identity(1,1), ID char(5), used bit)   ;WITH      cte AS(SELECT * FROM (VALUES('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'),('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('i'),('j'),('k'),('l'),('m'),('n'),('o'),('p'),('q'),('r'),('s'),('t'),('u'),('v'),('w'),('x'),('y'),('z')) AS v(t))         INSERT INTO ProductHandles(ID, Used) SELECT a.t + b.t + c.t + d.t + e.t, 0 FROM cte a CROSS JOIN cte b CROSS JOIN cte c CROSS JOIN cte d CROSS JOIN cte e ORDER BY NEWID() 

Then the cte can have an order by clause that guarantees the same random order as the rows returned from the select statement populating this table:

WITH cte as (                 SELECT TOP 1 *                  FROM ProductHandles                  WHERE Used = 0                 ORDER BY sort              ) UPDATE cte  SET Used = 1 OUTPUT INSERTED.ID 

You can see a live demo on rextester. (with only digits since it's taking too long otherwise)

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: