In order to randomly sort, or return randomly selected X row data, you can use the RAND function in the SELECT statement. However, the RAND function is only calculated once in the entire query, so all rows have the same value. You can use the Order By clause to sort line according to the results returned from the NewID function, as shown by the following code:
Select * from northwind..roDERS ORDER BY NEWID ()
Select Top 10 * from northwind..roDERS ORDER BY newID ()
Here, the most critical is the newID () function
NewID
Create a unique value of the UNIQUEIDentifier type.
grammar
Newid ()
Return type
UniqueIdentifier
Example
A. Use the NewID function for variables
The following example assigns a variable that is declared as a UniqueIndifier data type using NewID. Before testing this value, you will first print the value of the UniqueIdentifier data type variable.
- CREANG A LOCAL VARIABLE with DECLARE / SET SYNTAX.
Declare @myid uniqueIndifier
Set @myid = newid ()
Print 'Value of @myid IS:' Convert (varchar (255), @myid)
The following is the result set:
Value of @MYID IS: 6F9619FF-8B86-D011-B42D-00c04FC964FF
Description For each computer, the value returned by NewID is different. The displayed numbers only explain the role of explanation.
B. Use newid in the CREATE TABLE statement
The following example creates a CUST table with UNIQUEIDENTIFIER data type and pops the default value into the table using NEWID. Each new row and existing rows have a unique value of the Cust_ID column when it is imparted by NEWID ().
- Creating a Table Using Newid for UNIQUEIDENTIFIER DATA TYPE.
CREATE TABLE CUST
(
Cust_id UniqueIdentifier NOT NULL
DEFAULT newid (),
Company VARCHAR (30) Not NULL,
Contact_name varchar (60) Not null,
Address Varchar (30) Not Null,
City varchar (30) Not null,
State_Province Varchar (10) NULL,
Postal_code varchar (10) Not null,
Country varchar (20) Not null,
Telephone varchar (15) Not null,
Fax varchar (15) NULL
)
Go
- Inserting Data INTO CUST TABLE.
INSERT CUST
(Cust_ID, Company, Contact_name, Address, City, State_Province,
Postal_code, Country, Telephone, Fax)
Values
(NewID (), 'Wartian Herkku', 'Pirkko Koskitalo', 'Torikatu 38', 'Oulu', NULL,
'90110', 'Finland', '981-443655', '981-443655')
Insert Cust (Cust_ID, Company, Contact_name, Address, City, State_Province,
Postal_code, Country, Telephone, Fax)
Values
(NewID (), 'Wellington Importadora', 'Paula Parente', 'Rua Do Mercado, 12', 'Resende', 'SP',
'08737-363', 'Brazil', '(14) 555-8122', '')
INSERT CUST
(Cust_ID, Company, Contact_name, Address, City, State_Province,
Postal_code, Country, Telephone, Fax)
Values
(NewID (), 'Cactus Comidas Para Ilevar', 'Patricio Simpson', 'Cerrito 333', 'Buenos Aires', NULL,
'1010', 'Argentina,' (1) 135-5555 ',' (1) 135-4892 ')
INSERT CUST
(Cust_ID, Company, Contact_name, Address, City, State_Province,
Postal_code, Country, Telephone, Fax)
Values
(NewID (), 'Ernst Handel', 'Roland Mendel', 'Kirchgasse 6', 'Graz', NULL,
'8010', 'Austria', '7675-3425', '7675-3426')
INSERT CUST
(Cust_ID, Company, Contact_name, Address, City, State_Province,
Postal_code, Country, Telephone, Fax)
Values
(NewID (), 'Maison DEWEY', 'Catherine Dewey', 'Rue Joseph-Bens 532', 'Bruxelles', NULL,
'B-1180', 'Belgium', '(02) 201 24 67,' (02) 201 24 68 ')
Go
C. Using UNIQUEIDENTIFIER and variable assignment
The following example declares that local variable @myid is a UNIQUEIDENTIFIER data type. Then use the SET statement to assign the variable.
Declare @myid uniqueIndifier
Set @myid = 'A972C577-DFB0-064E-1189-0154C99310DAAC12'
Go
