Hekaton CTP1 – Row order limitation

UPDATE : Good news, this post relates to CTP1 only, the 8000 row limit has been lifted in CTP2

If you have been reading about hekaton and compiled stored procedure you are probably full of aspirations to move all your data into memory and magically have your system for faster.

Slow down just a minute though and carefully look through the list of supported and non-supported functionality here. That is quite a long list and im sure that given time there will be many items moving into the ‘supported’ arena.

However there is one restriction not listed that I have stumbled over, ordering is limited to 8000 rows. If you are planning to do anything which requires ordering , ORDER BY , GROUP BY ,TOP etc, there is a hard limit of 8000 rows that can be ordered.

Just for japes lets give it a try…

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
CREATE TABLE Hek1
(
--See the section on bucket_count for more details on setting the bucket count.
col1 INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000),
col2 INT NOT NULL,
col3 INT NOT NULL,
INDEX [idx] NONCLUSTERED HASH ([col3]) WITH(BUCKET_COUNT = 100000)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO
 
 INSERT INTO Hek1
select top(8001) row_number() over(order by (select null)),
row_number() over(order by (select null)),
row_number() over(order by (select null))
from sys.columns a cross join sys.columns b
go
 
 Drop PROCEDURE LargeSort
go
CREATE PROCEDURE LargeSort
WITH NATIVE_COMPILATION, EXECUTE AS OWNER, SCHEMABINDING
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
select col3,count(*) from dbo.Hek1
group by col3
end
 
 exec LargeSort

You should now have the error :

What if we run :

1
2
3
4
5
6
7
CREATE PROCEDURE HekatonGroup
WITH NATIVE_COMPILATION, EXECUTE AS OWNER, SCHEMABINDING
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
select col1,count(*) from dbo.Hek1
group by col1
end

Well that works. Why ? Just like in a regular query (ie non compiled) it is not need here, the aggregation is on col1, our primary key. It simply doesn’t matter.

So does this limit really matter ? Well as this functionality is aimed at an OLTP environment then im saying no. If are requiring to order that many rows you are *probably* doing something wrong.

As an aside, did you notice the order, or the lack of it ? That’s right it completely unordered, all SQL Server has done is scan the hash buckets in turn. The rows are always going to be in a ‘random’ order. So now more than ever the adage of “Without order by, order is not guaranteed’ is true now more than ever.

Leave a Reply

Your email address will not be published. Required fields are marked *