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?
2 Solutions collect form web for “How to ensure non-null duplicate values are not inserted in SQL Server 2005”
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;
You can use unique constraint for RowId