If there is anything in the row (in another column) that defines it as new, then you could do part of your requirement by using a filtered index:

CREATE UNIQUE INDEX

ON dbo.MyTable(MyColumn)

WHERE IsNew = 1;

That would still allow a new row to be inserted that has a value in MyColumn that exists in an old row, but it will not allow a new row with a value that exists in another new row.

For a full check, you can write a user-defined function (UDF) that tests if a duplicate was inserted, then use that in a CHECK constraint - but note that this will severely impact performance of multi-row insert and update operations since it has to execute the UDF for each affected row. Also note the order of execution: SQL Server first makes the change, then executes the UDF, and then rolls back the change if a violation occurs. So you cannot simply do an EXISTS for the value, as that will always return true.

Assuming you have another column (KeyColumn) that uniquely identifies the row, the code would be something like the below (untested):

CREATE FUNCTION dbo.MyCheck(@KeyValue int, @CheckValue varchar(20))

RETURNS bit

AS

BEGIN;

IF EXISTS (SELECT * FROM dbo.MyTable WHERE MyColumn = @CheckValue AND KeyColumn <> @KeyValue)

RETURN 1;

ELSE

RETURN 0;

END;

go

ALTER TABLE dbo.MyTable

ADD CONSTRAINT CK_NoDuplicates CHECK (dbo.MyCheck (KeyColumn, MyColumn) = 0);