how to get 10 records from table in sql?

If you would like to achieve something similar to pagination, please have a look at example:

DECLARE @tmp TABLE (ID INT IDENTITY(1,1), SomeText VARCHAR(10))

INSERT INTO @tmp (SomeText)
VALUES('A'),('B'),('C'),('D'),('E'),
        ('F'),('G'),('H'),('I'),('J'),
        ('K'),('L'),('M'),('N'),('O'),
        ('P'),('Q'),('R'),('S'),('T'),
        ('U'),('V'),('X'),('Y'),('Z')


DECLARE @counter INT=1

SELECT A.*
FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY ID) AS RowNo, *
    FROM @tmp
    ) AS A
WHERE RowNo BETWEEN @counter*10-9 AND @counter *10

SET @counter =2

SELECT A.*
FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY ID) AS RowNo, *
    FROM @tmp
    ) AS A
WHERE RowNo BETWEEN @counter*10-9 AND @counter *10

This is only an idea, but i hope this is what you're looking for.

As you can see, first query returns 10 records (1-10) and second one (11-20).