/* Filter Index SQL SERVER 2008 Test */
/*
Application: User table, through ID can login,
You can sign in if you have a phone number, but you must ensure that the phone number is unique.
*/
--1, creating a test table
CREATE TABLE T
(
ID VARCHAR () not NULL PRIMARY KEY,
MOBILE VARCHAR (+) NULL
)
--2, creating index-unique, nonclustered indexes (and adding filter criteria)
CREATE UNIQUE nonclustered INDEX [idx_mobile] on [dbo]. T
(
[MOBILE] Asc
)
WHERE ([MOBILE] is not NULL)
DROP INDEX [idx_mobile] on t
INSERT into T SELECT ' A ', NULL
INSERT into T SELECT ' B ', NULL
INSERT into T SELECT ' C ', ' A '
INSERT into T SELECT ' d ', ' d '
INSERT into T SELECT ' E ', ' B '
INSERT into T SELECT ' F ', ' C '
INSERT into T SELECT ' G ', NULL
INSERT into T SELECT ' H ', NULL
SELECT * from T
/*mysql Create a unique index, null value is ignored directly */
CREATE TABLE T
(
ID varchar () NOT NULL PRIMARY key,
Mobile varchar (+) NULL
)
SELECT * FROM t
INSERT into T (id,mobile) VALUES (' A ', NULL);
INSERT into T (id,mobile) VALUES (' B ', NULL);
INSERT into T (id,mobile) VALUES (' C ', ' A ');
INSERT into T (id,mobile) VALUES (' d ', ' d ');
INSERT into T (id,mobile) VALUES (' E ', ' B ');
INSERT into T (id,mobile) VALUES (' F ', ' C ');
INSERT into T (id,mobile) VALUES (' G ', ');
INSERT into T (id,mobile) VALUES (' H ', ');
TRUNCATE TABLE T
--2, CREATE index unique
CREATE UNIQUE INDEX idx_mobile on t
(
Mobile ASC
)
Create a unique index in SQL Server with MySQL