問題描述

我目前正在從 MS Access 遷移到 SQL Server.Access 允許在唯一索引中有多個空值，而 SQL Server 則不允許......我一直在通過刪除 SQL Server 中的索引并添加過濾索引來處理遷移:

I am currently doing some migration from MS Access to SQL Server. Access allows multiple Nulls in unique indexes where as SQL Server does not... I've been handling the migration by removing the indexes in SQL Server and adding filtered indexes:

CREATE UNIQUE NONCLUSTERED INDEX idx_col1_notnull 
ON tblEmployee(col1) 
WHERE col1 IS NOT NULL;

我遇到的問題是我不確定如何實現復合或多列過濾"索引...或者這是否真的可行，因為我在研究中沒有發現任何例子.

The problem I am having is that I am not sure how to implement a composite or multi-column "filtered" indexes... or if this is really possible as I've found no examples in researching it.

我確實有一個想法，通過像這樣創建過濾索引來實現它:

I do have an idea to implement it by creating filtered indexes like so:

CREATE UNIQUE NONCLUSTERED INDEX idx_col1col2_notnull 
ON tblEmployee (col1, col2) 
WHERE col1 IS NOT NULL

然后添加第二個過濾索引:

And then adding a second filtered index:

CREATE UNIQUE NONCLUSTERED INDEX idx_col2col1_notnull 
ON tblEmployee (col1, col2) 
WHERE col2 IS NOT NULL

但我不確定這是否有效，更不用說是最好的方法了.非常感謝您提供正確方向的指導.

But I'm not sure if this would even work let alone be the best method. Guidance in the right direction would be greatly appreciated.

推薦答案

您可以添加以下索引以僅索引不可為空的列:

You can add the following index to index only non nullable columns:

create table tblEmployee(col1 int, col2 int)
go

create unique nonclustered index idx_col1col2_notnull ON tblEmployee(col1,col2) 
where col1 is not null and col2 is not null
go

--This Insert successeds
insert into tblEmployee values
(null, null),
(null, null),
(1, null),
(1, null),
(null, 2),
(null, 2)

--This Insert fails
insert into tblEmployee values
(3, 4),
(3, 4)

這篇關于TSQL 多列唯一約束也允許多個空值的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！