当键不是数字而是字母数字值时，在循环中选择特定数量的行

Question

我试图从SQL Server中的某些表中选择1000行，在某些情况下很容易，因为我确实有一个bigint键。因此，我存储我获取的密钥的最后一个号码，然后在上面加上100。见下文：

--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)

但是，当键是字母数字值时，如何做到这一点呢？

什么是等价物

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

如果@startindex是NVARCHAR,例如123G7_56Y7F？

谢谢你！

Answer 1

方法一：

如果您使用的是SQL Server 2012或更高版本，则可以使用偏移量提取，如下所示：

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

Col1=要对数据进行排序的列

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