Postgresql said, Sql Server(2008) said. Non-Unique Nulls, Unique Nulls

Postgres said:

create table x
(
name varchar(50) null unique
);

insert into x values('John');
insert into x values('Paul');
insert into x values(null); -- will allow this
insert into x values(null); -- will also allow this. Postgres allow duplicate nulls, it only indexes non-nulls. Compliant to ANSI SQL specifcation

How to emulate that in Sql Server 2008?

create table x
(
name varchar(50) null -- don't put unique here
)


create unique index uk_x on x(name) where name is not null -- this index creation, covers non-nulls only

insert into x values('John')
insert into x values('Paul')
insert into x values(null) -- will allow this
insert into x values(null) -- will also allow this

Sql Server 2008 said:

create table x
(
name varchar(50) null unique 
)


insert into x values('John')
insert into x values('Paul')
insert into x values(null)
insert into x values(null) -- this won't be allowed, Sql Server's unique column cover all values(non-nulls and nulls)

How to emulate that in Postgres?

create table x
(
name varchar(50) null unique -- keep this
);

-- Then we resolve all null to single value, e.g. number 1. 
-- essentially preventing duplicate nulls
create unique index uk_x on x (cast(1 as int)) where name is null; 


insert into x values('John');
insert into x values('Paul');
insert into x values(null);
insert into x values(null); -- this won't be allowed

Related to: http://stackoverflow.com/questions/10468657/postgres-index-for-join-table/10468686#10468686