IN Memory table create unique nonclustered index

Question

I have a scenario. I want my inmemory table column to accept multiple NULL values but when entered something besides NULL, It should be unique. This i kept doing in Disk tables using CREATE UNIQUE NONCLUSTERED INDEX.

ex:

CREATE UNIQUE NONCLUSTERED INDEX  [IX_customer_PRN]
ON [dbo].[customer](PRN)
WHERE PRN IS NOT NULL
GO

Need suitable alternative in IN MEMORY tables.

is it possible for you to use a temp table in stead of an in memory table ? — GuidoG, May 14 '19 at 14:46
maybe [this](https://stackoverflow.com/questions/886050/creating-an-index-on-a-table-variable) can help — GuidoG, May 14 '19 at 14:51

score 2 · Answer 1 · answered May 14 '19 at 14:40

2

As stated in the documentation for in-memory tables, you should be able to achieve what you need by just creating a unique non-clustered index on the table, as below:

ALTER TABLE [dbo].[customer]
    ADD CONSTRAINT IX_customer_PRN
    UNIQUE NONCLUSTERED (PRN);  
GO

answered May 14 '19 at 14:40

Rigerta

3,959
15
26

that is about `memory-optimized` tables. Im not sure that is what the OP means by `in memory table` – GuidoG May 14 '19 at 14:43
Given that the name of the table is `dbo.customer`, I assumed it wasn't a temp or a variable table. – Rigerta May 14 '19 at 14:48
I think that name was an example of how he would do it with a regular table. But I could be wrong off course – GuidoG May 14 '19 at 14:48
I could be wrong as well :) Let's wait for OP's answer. – Rigerta May 14 '19 at 14:52
I agree on that – GuidoG May 14 '19 at 14:53
Unique non clustered index will accept one null value in In-memory TABLE. I want the column to accept multiple null values. But anything besides null should be unique. – Younus Mohammed May 14 '19 at 21:24
Younus, we are not sure what you mean by `in memory table`. Do you mean something like `declare @mytable table (id int, ...)` ? – GuidoG May 15 '19 at 06:27
memory-optimized table. Sorry for confusion – Younus Mohammed May 15 '19 at 07:52

IN Memory table create unique nonclustered index

1 Answers1