In-memory table range scan/ seek

Question

Suppose I have a table
CREATE TABLE dbo.ShoppingCart (
ShoppingCartId INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
UserId INT NOT NULL INDEX ix_UserId NONCLUSTERED
HASH WITH (BUCKET_COUNT=1000000),
mytext varchar(100)
) WITH (MEMORY_OPTIMIZED=ON)
GO

then I do a insert to this table ( around 300K records) . and then I do a query
select * from dbo.ShoppingCart where ShoppingCartId < 100

It give me a table scan inside the execution plan .

why ? Inmemory table can have range seek ?

Answer 1

SQL Server can definitely do a range scan on a "regular index" (BW-Tree) for a in-mem table. We have to have a full repro, or at the very least the plan at pastetheplan.com in order to answer why it doesn't do that in your case. All we have is an image of the execution plan operators. Likely it has to do with calculated selectivity.

Answer 2

Hi @sakuraime ,

...It give me a table scan inside the execution plan.why ?

It maybe caused by index of ShoppingCartId is not created successfully, or be deleted by mistake.

1.What version do you use? please use code as next to have a check :

select @@version   

2.If ShoppingCartId is nolustered index, it will be use index seek. Please check wether you delete the index, if there is no index on the column, it will use table scan.

Same code on myside:
If select by ShoppingCartId, index seek;
If select by mytext , table scan

use test1021  

--only create one memory-optimized filegroup per database  
ALTER DATABASE test1021 ADD FILEGROUP imoltp_mod CONTAINS MEMORY_OPTIMIZED_DATA    

--add one or more containers to the MEMORY_OPTIMIZED_DATA filegroup  
ALTER DATABASE test1021 ADD FILE (name='test1021_imoltp_mod1', filename='C:\data\test1021') TO FILEGROUP imoltp_mod    


CREATE TABLE dbo.ShoppingCart (  
ShoppingCartId INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,  
UserId INT NOT NULL INDEX ix_UserId NONCLUSTERED  
HASH WITH (BUCKET_COUNT=1000000),  
mytext varchar(100)  
) WITH (MEMORY_OPTIMIZED=ON)  
GO  


DECLARE @num int  
SET @num = 1  
WHILE @num <= 3000000  
BEGIN  
INSERT INTO dbo.ShoppingCart (UserId,mytext) VALUES(@num,'A' + CONVERT(varchar(10), @num))  
SET @num = @num + 1  
END  

Inmemory table can have range seek ?

Yes. If seek means scan. Memory-optimized nonclustered indexes support range scans and ordered scans.

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 3

Hi @sakuraime ,

Is the reply helpful?

BR,
Mia