SQL Server In-Memory OLTP Hash Index set value for BUCKET_COUNT

Question

When creating a hash index in memory optimization table I can set value for variable BUCKET_COUNT

CREATE TABLE [Table1] (
[Id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY NONCLUSTERED HASH 
                                WITH (BUCKET_COUNT = 1000000),
[Name] NVARCHAR(100) NOT NULL
) 
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

What is the optimal value for this variable?

Answer 1

According to Determining the Correct Bucket Count for Hash Indexes:

In most cases the bucket count should be between 1 and 2 times the number of distinct values in the index key. If the index key contains a lot of duplicate values, on average there are more than 10 rows for each index key value, use a nonclustered index instead

You may not always be able to predict how many values a particular index key may have or will have. Performance should be acceptable if the BUCKET_COUNT value is within 5 times of the actual number of key values.

And:

Primary Key and Unique Indexes

Because the primary key index is unique, the number of distinct values in the key corresponds to the number of rows in the table.