How to make SQL server return last rows first in select not using order by

2
votes

I have a table with 350 000 rows. When i search top 50 i get the 50 oldest rows. Is there any way to make SQL server to start from the bottom so i get the last 50 rows that bin inserted?

We have some performance issue so i cant use any syntax that loop through all records.

If I add the order by Id it takes 14 sec instead of 1 sec. I use Dynamic SQL to solve the search functionality for dynamic table structure.

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 AS INT = NULL, @StatusId AS INT = NULL, @UserId as int, @LanguageId as int' EXEC sp_executesql @sql, @paramlist,@ChassisNumber,@FirstOwner,@GroupId,@InsuranceNumber,@InsuredId,@Model,@OwnerYearOfBirth,@RegistrationNumber,@StakeholderId,@StatusId,@UserId, @LanguageId END

4
sql
Use an index to improve performance – ilanco
If you don't specify an ORDER BY, you'll get 50 random rows; unless you specify a ORDER BY, there's no guarantee of any ordering and ordering might change between calls to the query... – marc_s
Performance issues when using ORDER BY indicate a lack of appropriate index. If you want to search out a specific set of 50 records (the 50 most recently inserted) you should ensure that you have the appropriate index to complete that search/ordering. – MatBailie
"My eyes" yes the sp is generated by C# code dynamically. When I pasted it all my NewLine was lost. – user1444174

4 Answers

5
votes

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

No, you get 50 rows. Period. 50 rows that your DBMS chooses to return. 50 more or less random rows.

If you want the 50 rows that were inserted last, you'll have to have a column that stores such information (InsertionDateTime). Then index it. Then you can run:

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

If you have an IDENTITY primary key column, then you can order by that column descending (because that's the order rows are inserted, and the id is monotonically increasing)

SELECT TOP 50 *  
FROM MyTable
ORDER BY IdentityColId DESC

Note: the fact SQL Server by default creates the clustered index on a table's Identity primary key column is not sufficient to guarantee ordering.

1
votes

considering you have a IDENTITY ID column

select * 
from YOUR_TABLE
where id > (select max(id) from YOUR_TABLE) - 50

EDIT:

If you don't have an ID, you will need to do something with row_number but them you will need an order

0
votes

Add an "ORDER BY insertime DESC" (or whatever your yardstick for "old" is, sequential ID etc.) on the end of your select. But yes, also check that you have decent indices set up. Use explain plan if you're not sure.