Get 50 pieces of data from the database every time without repetition

Question

Teams,Please Help me..
How to get 50 pieces of data from the database every time without repetition.There are probably several rows of data in the database.
I want to get 50 pieces of data from the database at a time without repeating each time. How to do this?Also consider the problem that the database has multiple connections and fetches data at the same time

Answer 1

Hi,@newbie

Welcome to Microsoft T-SQL Q&A Forum!

It sounds like it could be done using cluster sampling . By getting the first 50 rows we use:

SELECT TOP 50 * FROM Yourtable  

but the results are not returned in the specified order , which is not a guarantee of heap behavior . Now let's try to use tablesample for a random problem like this:

select * from table TABLESAMPLE ( 50 ROWS )  

you might think the 50 ROWS option will return 50 rows , but the reality is that this number before execution is based on the number you specify and The approximate number of rows in the table is converted to a percentage . This way you cannot guarantee the exact number of rows.

In order to ensure his certainty as much as possible again, plus the Top statement, the following is a case, I hope it can be helpful to you, the DDL design is to randomly create 300 rows of data, and use our above method to execute.

//DDL:  
 CREATE TABLE dbo.RandomData (   
    RowId INT IDENTITY(1,1) NOT NULL,   
    SomeInt INT,  
    SomeBit BIT,   
    SomeVarchar VARCHAR(10),   
    SomeDateTime DATETIME,   
    SomeNumeric NUMERIC(16,2) )   
GO  
--select * from dbo.RandomData   
DECLARE @count INT   
SET @count = 1  
  
WHILE @count <= 300  
BEGIN  
INSERT INTO dbo.RandomData   
    SELECT    @count,   
            CASE WHEN DATEPART(MILLISECOND, GETDATE()) >= 500 THEN 0 ELSE 1 END [SomeBit],   
            CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) +   
            CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) +   
            CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) +   
            CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) +   
            CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) [SomeVarchar],   
            DATEADD(MILLISECOND, (ABS(CHECKSUM(NEWID())) % 6000) * -1,   
                DATEADD(MINUTE, (ABS(CHECKSUM(NEWID())) % 1000000) * -1, GETDATE())) [SomeDateTime],   
            (ABS(CHECKSUM(NEWID())) % 100001) + ((ABS(CHECKSUM(NEWID())) % 100001) * 0.00001) [SomeNumeric]  
    SET @count += 1  
END  
//Query  
SELECT Top 100.* FROM dbo.RandomData TABLESAMPLE ( 100 ROWS )  

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

There are various approaches to get random data from the database, but there is no guarantee of the uniqueness of each set unless you add "already got" rows into another table and check it on each selection. Then it's a question of how "random" you want your set to be.