Select a specific number of rows in a loop when the key is not number but an alphanumeric value

Question

I at trying to select 1000 rows from some tables in SQL Server and in some cases is easy because I do have a key which is a bigint. Thus, I store the last number of a key that I have fetched and then I add to it 100. See below:

--Get the last loaded record
DECLARE @StartIndex BIGINT

SET @StartIndex = 1 + (SELECT [StoredIndex]
                       FROM [DB].[dbo].[MasterData]
                       WHERE [Status] = 'LastLoad')

--Declare and set the last record that was loaded
DECLARE @EndIndex BIGINT

--Retrieve the next @Step+1 or less records and store them in a temporary table
SELECT T1.*
INTO #TempResults
FROM
    --Get the next @Step+1 or less records
    (SELECT *
     FROM ANOTHERDB.[TableName]
     WHERE [Tables_ID] BETWEEN @StartIndex AND @StartIndex + 1000) T1

--Set the index of the last record inserted
SET @EndIndex = (SELECT MAX([Tables_ID])--The next record fetched with the largest ID
                 FROM #TempResults)

However how to do that when the key is an alphanumeric value?

What is the equivalent for

WHERE [Tables_ID] BETWEEN @StartIndex AND @StartIndex + 1000

If the @StartIndex is a nvarchar, for example 123g7_56y7f ?

Thank yoU!

is your `[Tables_ID]` can contains NVARCHAR data?? I don't think so — Jaydip Jadhav, Jul 04 '16 at 14:03
The Table_ID (thats a dummy name) is varchar(64) in the second case that i am interested in — aragorn, Jul 04 '16 at 14:08

Vahid Farahmandian · Accepted Answer · 2016-07-04T16:16:59.547

Method 1:

If you are using SQL Server 2012 or higher you can use offset fetch as below:

  SELECT    *
  FROM      ANOTHERDB.[TableName]
  ORDER BY  Col1 
  OFFSET XX ROWS FETCH NEXT YY ROWS ONLY;

Col1 = The column which you are going to sort the data

XX = number of rows to be skipped

YY = number of rows to be fetched

Method 2:

You can make use of Row_Number function(SQL Server 2008 or higher), as below:

  SELECT    *
  FROM      ( SELECT    * ,
                    ROW_NUMBER() OVER ( ORDER BY COL1) AS RN
          FROM      ANOTHERDB.[TableName]
        ) AS K
  WHERE     K.RN BETWEEN XX AND YY;

score 2 · Answer 2 · edited May 23 '17 at 11:44

SQL tables don't have a default order. So unless you can define one using the ORDER BY clause there is no first 1,000 or last 1,000 records.

If you are trying to return the last 1,000 records added to a table you will need to capture the date and time each record is created. You cannot rely on an identify column, as these can be reseeded or updated.

Combine TOP and ORDER BY to limit the number of records returned. An ascending order returns the first n results. Descending the last n results.

-- Limits results to the last 1,000 records.
SELECT TOP 1000
    *
FROM
    YourTable
ORDER BY
    YourColumn DESC
;

Replace YourTable and YourColumn as needed.

Correct that's why I used Vahid's solution. Thank you all for your answers. — aragorn, Jul 05 '16 at 14:47

Select a specific number of rows in a loop when the key is not number but an alphanumeric value

2 Answers2