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.

  • How to convert string data into datetime in SQL Server using dateadd() function
  • Cross Join 'n' times a table
  • Violation of UNIQUE KEY constraint on INSERT WHERE COUNT(*) = 0 on SQL Server 2005
  • UPDATE Doesn't Work Even Though I Get Success Response in Console
  • Multpilcation Aggregate in Sql Server
  • How to insert a record with only default values?
  • 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?

  • SQL Server 2008 error - cannot allocate space for object
  • Error while getting sql output to html
  • SET statement always runs regardless of IF ELSE condition
  • How to Design “Country”, “Province” and “City” Tables?
  • Using System Tables to Count the Percent of Rows Null in Various Tables
  • 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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.