Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Create a table of student IDs. Fill it up with IDs between X and Y. Every time you want to use an ID, remove it from the table.</p> <pre><code>create table [FreeIDs] ( [ID] int, [order] uniqueidentifier not null default newid() primary key); insert into [FreeIDs] ([ID]) values (11111),(11112),...,(99999); to get a free ID: with cte as ( select top(1) [ID] from [FreeIDs] order by [order]) delete cte output deleted.ID; </code></pre> <p>The persisted predeterminer order speeds up generating new IDs.</p> <p>BTW, if you're tempted to 'optimize' the table and go by a numbers table:</p> <pre><code>with Digits as ( select Digit from ( values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) as t(Digit)), Numbers as ( select u.Digit + t.Digit*10 +h.Digit*100 + m.Digit*1000+tm.Digit*10000 as Number from Digits u cross join Digits t cross join Digits h cross join Digits m cross join Digits tm) select top(1) Number from Numbers where Number between 11111 and 99999 and Number not in ( select ID from Students) order by (newid()); </code></pre> <p>just don't. The requirement to randomize the set is a performance killer and the join to eliminate existing (used) IDs is also problematic. But most importantly the solution fails under concurrency, as multiple requests can get the same ID (and this increases as the number of free IDs is reduced). And of course, the semantically equivalent naive row-by-painfully-slow-row processing, like your original code or Kaf's answer, have exactly the same problem but are also just plain slow. It really worth testing the solution when all but one of the IDs are taken, watch the light dim as you wait for the random number generator to hit the jackpot...</p>
    singulars
    1. This table or related slice is empty.
    1. POHow To Create A Stored Procedure That return a Random Number If Not Exist In The Table
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USRemus Rusanu
    1. USRemus Rusanu
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTDownMod
    1. CONice Idea , But I don't prefer that . cause may be in the future i increase number to the random number For Example (make it 6 Digits Instead of 5)
      singulars
      1. PO
      1. USLoai
    2. COwhen you want to add more IDs just insert them into the FreeIDs table. 6 digits, 10 digits, it really doesn't matter.
      singulars
      1. PO
      1. USRemus Rusanu
    3. COSo much Thanks This really convinced me , I Will apply this As soon as possible :)
      singulars
      1. PO
      1. USLoai
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload