sql - 如何让SQL Server在不使用order by的情况下首先返回最后一行

Question

我有一个包含 350 000 行的表。当我搜索前 50 名时，我得到了最旧的 50 行。有什么方法可以让 SQL Server 从底部开始，这样我就可以得到 bin 插入的最后 50 行？

我们有一些性能问题，所以我不能使用任何遍历所有记录的语法。

如果我按 Id 添加订单，则需要 14 秒而不是 1 秒。我使用 Dynamic SQL 来解决动态表结构的搜索功能。

ALTER Procedure [CS.Core].[spDynamicSearchInsuranceDemoCar]

@ChassisNumber AS NVARCHAR(50) = NULL, @FirstOwner AS BIT = NULL, @GroupId AS INT = NULL, @InsuranceNumber AS INT = NULL, @InsuredId AS INT = NULL, @Model AS NVARCHAR(50) = NULL, @OwnerYearOfBirth AS INT = NULL, @RegistrationNumber AS NVARCHAR(12) = NULL, @StakeholderId AS INT = NULL, @StatusId AS INT = NULL, @UserId as int, @LanguageId as int AS SET NOCOUNT ON; BEGIN DECLARE @sql nvarchar(MAX),@paramlist nvarchar(4000) Select @sql ='SELECT TOP 50 Insurance.InsuranceId, ISNULL(ProductCaption.CaptionText, [CS.Core].Entity.Name) AS Product_462, Stakeholder.RefStakeholderName AS Stakeholder_1093, Insurance.ValidFrom as ValidFrom_925, Insurance.InsuranceNumber, Insurance.GroupId, Insurance.StatusId, Insurance.ValidFrom, DemoCar.RegistrationNumber, DemoCar.Year, DemoCar.ChassisNumber, DemoCar.FirstOwner, DemoCar.Model From [CS.Entity].Insurance INNER JOIN [CS.Core].Entity on Insurance.EntityId = Entity.EntityId LEFT JOIN [CS.Entity].Stakeholder ON Insurance.StakeholderId = Stakeholder.StakeholderId LEFT JOIN [CS.Core].Caption AS ProductCaption ON (ProductCaption.CaptionCode = Entity.TextId AND ProductCaption.LanguageID = @LanguageId) INNER JOIN [CS.Entity].[DemoCar] ON [CS.Entity].[DemoCar].[RootId] = [CS.Entity].[Insurance].[InsuranceId] WHERE [CS.Entity].Insurance.GroupId in (Select SecGroupID from [CS.Security].SecRelation Where SecUserID = @UserId)' IF @ChassisNumber IS NOT NULL IF CHARINDEX('%',@ChassisNumber) > 0 SELECT @sql = @sql + ' AND ([CS.Entity].[DemoCar].[ChassisNumber] Like @ChassisNumber)' ELSE SELECT @sql = @sql + ' AND ([CS.Entity].[DemoCar].[ChassisNumber] = @ChassisNumber)' IF @FirstOwner IS NOT NULL SELECT @sql = @sql + ' AND ([CS.Entity].[DemoCar].[FirstOwner] = @FirstOwner)' IF @GroupId IS NOT NULL SELECT @sql = @sql + ' AND ([CS.Entity].[Insurance].[GroupId] = @GroupId)' IF @InsuranceNumber IS NOT NULL SELECT @sql = @sql + ' AND ([CS.Entity].[Insurance].[InsuranceNumber] = @InsuranceNumber)' IF @InsuredId IS NOT NULL SELECT @sql = @sql + ' AND ([CS.Entity].[Insurance].[InsuredId] = @InsuredId)' IF @Model IS NOT NULL IF CHARINDEX('%',@Model) > 0 SELECT @sql = @sql + ' AND ([CS.Entity].[DemoCar].[Model] Like @Model)' ELSE SELECT @sql = @sql + ' AND ([CS.Entity].[DemoCar].[Model] = @Model)' IF @OwnerYearOfBirth IS NOT NULL SELECT @sql = @sql + ' AND ([CS.Entity].[DemoCar].[Year] = @OwnerYearOfBirth)' IF @RegistrationNumber IS NOT NULL IF CHARINDEX('%',@RegistrationNumber)

0 SELECT @sql = @sql + ' AND ([CS.Entity].[DemoCar].[RegistrationNumber] Like @RegistrationNumber)' ELSE SELECT @sql = @sql + ' AND ([CS.Entity].[DemoCar].[RegistrationNumber] = @RegistrationNumber)' IF @StakeholderId IS NOT NULL SELECT @sql = @sql + ' AND ([CS.Entity].[Insurance].[StakeholderId] = @StakeholderId)' IF @StatusId IS NOT NULL SELECT @sql = @sql + ' AND ([CS.Entity].[Insurance].[StatusId] = @StatusId)' SELECT @sql = @sql + ''

SELECT @paramlist = '@ChassisNumber AS NVARCHAR(50) = NULL, @FirstOwner AS BIT = NULL，@GroupId AS INT = NULL，@InsuranceNumber AS INT = NULL，@InsuredId AS INT = NULL，@Model AS NVARCHAR(50) = NULL，@OwnerYearOfBirth AS INT = NULL，@RegistrationNumber AS NVARCHAR(12) = NULL，@StakeholderId 为 INT = NULL，@StatusId 为 INT = NULL, @UserId as int, @LanguageId as int' EXEC sp_executesql @sql, @paramlist,@ChassisNumber,@FirstOwner,@GroupId,@InsuranceNumber,@InsuredId,@Model,@OwnerYearOfBirth,@RegistrationNumber,@StakeholderId,@StatusId ，@用户身份， @LanguageId 结束

Answer 1

"When i search top 50 i get the 50 oldest rows."

不，您得到 50 行。时期。您的 DBMS 选择返回的 50 行。 50 个或多或少的随机行。

如果您想要最后插入的 50 行，则必须有一个存储此类信息的列 (InsertionDateTime)。然后索引它。然后你可以运行:

SELECT TOP (50)
    * 
FROM 
    mytable 
ORDER BY
    InsertionDateTime DESC ;