Stack Exchange
SQL Server issue with SELECT and datetime2
I found a big difference of the query execution under MS SQL Server Standart 2019.
T-SQL
DECLARE @atTime datetime2 = '2022-05-04 13:23:20';
DECLARE @startTime datetime2;
DECLARE @shiftTime datetime2;

SET @startTime = @atTime;   
SET @shiftTime = DATEADD(SECOND, -5, @atTime)  

-- SELECT @shiftTime, @startTime
-- 2022-05-04 13:23:15.0000000  2022-05-04 13:23:20.0000000

-- #1 It takes 7 seconds to complete
SELECT TOP(1) * FROM [TrackerPositions] WITH(NOLOCK) WHERE AtTime BETWEEN @shiftTime AND @startTime 

-- #1 It takes 0 seconds to complete
SELECT TOP(1)   * FROM [TrackerPositions] WITH(NOLOCK) WHERE AtTime BETWEEN '2022-05-04 13:23:15.0000000' AND '2022-05-04 13:23:20.0000000' 

Note: AtTime colum has datetime2
Please, help to get working fast SELECT #1
Thank you!
UPDATE #1
CREATE TABLE [dbo].[TrackerPositions](
    [ID] [uniqueidentifier] NOT NULL,
    [GPSTrackerID] [int] NOT NULL,
    [AtTime] [datetime2](7) NOT NULL,
    [Lat] [decimal](9, 6) NOT NULL,
    [Lng] [decimal](9, 6) NOT NULL,
    [GeoLocation]  AS ([geography]::STGeomFromText(((('POINT('+CONVERT([varchar](20),[Lng],0))+' ')+CONVERT([varchar](20),[Lat],0))+')',(4326))),
    [SignalLevel] [int] NULL,
    [IPAddress] [nvarchar](40) NULL,
    [Port] [int] NULL,
    [Height] [int] NULL,
    [IsMoving] [bit] NULL,
    [Speed] [decimal](18, 4) NULL,
 CONSTRAINT [PK_TrackerPositions] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 50, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TrackerPositions] ADD  CONSTRAINT [DF_TrackerPositions_ID]  DEFAULT (newid()) FOR [ID]
GO

ALTER TABLE [dbo].[TrackerPositions] ADD  CONSTRAINT [DF_TrackerPositions_IsMoving]  DEFAULT ((0)) FOR [IsMoving]
GO

ALTER TABLE [dbo].[TrackerPositions]  WITH CHECK ADD  CONSTRAINT [FK_TrackerPositions_GPSTrackers] FOREIGN KEY([GPSTrackerID])
REFERENCES [dbo].[GPSTrackers] ([ID])
GO

ALTER TABLE [dbo].[TrackerPositions] CHECK CONSTRAINT [FK_TrackerPositions_GPSTrackers]
GO




Out of interest, what are the timings when you reverse the SELECT statements?

@DeanOC It is about 5-10 seconds but it doesn't matter the SELECT's order, indexes and table size, and even with datetime it works the same manner.

Performance questions can seldom be answered without the plan. Please provide us the execution plan here: https://www.brentozar.com/pastetheplan/

The reason I asked is that when you execute (essentially) the same query twice, the second time often takes 0 seconds because SQL Server has cached the query plan from the first one. Put another way, what happens if you append OPTION (RECOMPILE) to each?

This question is not answerable without query plans, and table and index defintions.

Why SELECT * perhaps you should limit your selected columns, then create an index on AtTime covering those columns

@Charlieface I just added extra info pls check it thank you

Indeed appears that there is no index to cover the query. Either add more INCLUDE columns to IX_TrackerPositions, or only select columns available in that index rather than *. And remove that NOLOCK it will just cause incorrect results. Consider using Snapshot Isolation instead

The right answer is to use OPTION(RECOMPILE)
SELECT TOP(1) * FROM [TrackerPositions] WITH(NOLOCK) WHERE AtTime BETWEEN @shiftTime AND @startTime  OPTION(RECOMPILE)


This will certainly help get the wrong results faster. I suggest @NoWar remove the NOLOCK hint to help ensure rows are not  missed or duplicated in the results.

@DanGuzman I can't  exclude NOLOCK coz of intensive inserted operations on that table,

that's why we have snapshot isolation and RCSI (on by default in Azure SQL Database)
