How To Ensure Non-null Duplicate Values Are Not Inserted In SQL Server 2005

I have a history database in SQL Server 2005 that is used to archive data from live database. I need to add a column in history database named RowID INT. This column cannot be made as identity since it need to store values from live database.

Note: In the live database the RowID is identity column. This column was added by me in live database also recently.

I need to make sure that in history database, the non-null values inserted into this column is unique. I understand that in SQL Server, we cannot make a nullable column as unique.

So, in SQL Server 2005, what is the most efficient way to make sure that insertion of non-null duplicate values into RowID columns throws an error?

This is too long for a comment. It is time to upgrade to a more recent version of SQL Server. Microsoft is discontinuing support in April (2016) (see here).

Starting in SQL Server 2008, you can trivially do what you want with a filtered unique index:

create unique index unq_history_rowid on history(rowid) where col is not null;