For this tutorial, we use SQL Server Express with SQL Server Management Studio, and the Wide World Importers test database.
The most common thing when we make a query in a database is that we want to obtain all the records that meet a certain condition.
For example, in the WideWorldImporters database, to get all the people who are allowed to LogOn, with results in alphabetical order by email address, the query would be this:
SELECT Application.People.FullName, Application.People.LogonName FROM Application.People WHERE (Application.People.IsPermittedToLogon = 1) ORDER BY Application.People.EmailAddress ASC
And the result is what you see in this screenshot:
As you can see, it returns 155 results (shown on the bottom right) and gives you exactly what you needed. This is the most common.
If you want to get only the first 10 names, ordered alphabetically, just use the TOP keyword in front of the query indicating the number of records we need.
The query would be like this:
SELECT TOP(10) Application.People.FullName, Application.People.LogonName FROM Application.People WHERE (Application.People.IsPermittedToLogon = 1) ORDER BY Application.People.EmailAddress ASC
So far, you are somehow forcing SQL to give very specific records, however, what about if what you need is for it to return a series of random records?
That is, instead of ordering them by a criterion and returning 10 chosen at random from all the results.
This can be useful for many things: to send notifications to 10 random people in a database or to choose 10 random emails in a survey, for example.
To achieve this in SQL Server you have several options, but the most common and simplest is to use the NEWID function.
Random registrations using NEWID
SQL Server has a function called NEWID that generates a new unique identifier of the GUID type (uniqueidentifier), generally to insert it as a field value and be able to uniquely identify a record.
The usual appearance of one of these IDs is as follows:
537CC0BA-95B6-4E35-BD3A-468BAB878D96
That is, strings with very long letters, numbers and hyphens that are random and that are guaranteed to be unique, hence its name: GUID, which stands for Globally Unique Identifier.
They are based on the combination of a pseudo-random number generated on the local computer together with the MAC of the first network card on it (which also has a unique identifier a priori).
You can include this NEWID function in the ordering clause, like this (Look is highlighted in red):
SELECT TOP (10) Application.People.FullName, Application.People.LogonName FROM Application.People WHERE (Application.People.IsPermittedToLogon = 1) ORDER BY NEWID()
What this will cause is that, for each record, a new unique identifier will be generated in the style of the one that was shown before, and then it will be sorted using said identifier.
Being this completely random value, and having placed a TOP X to the query (where X is the number of records we need) what we will achieve in practice is to obtain the first X random records of the table or tables we are querying:
You can try it: each time you execute the query you will get a new set of records, chosen completely randomly.