create unique nonclustered index IX_test
     on your_table (column_name)
     where (column_name is not null) and (column_name <> 0)

create function dbo.CustomUQ (@value int) returns bit as
begin
            declare @b bit
            if @value = 0 
                  set @b = 1
            else if not exists 
                    (select *
                     from your_table
                     where column_name = @value)
                  set @b = 1
            else 
                  set @b = 0

            return @b
end
go

alter table your_table
add constraint UQ_01
check ( dbo.CustomUQ ( Column_name ) = 1 )
go

create trigger trg_UQ
on your_table
instead of insert, update
as
begin       
            declare @v int
            select @v = column_name from inserted

            if @v = 0 or not exists 
                         (select *
                          from your_table
                          where column_name = @v)
                  insert into your_table 
                  select * from inserted

            else 
                  raiserror ('Duplicate Value',16,1)

end