applying Unique,Non-Clustered Index difference between 2000 and 2008 R2

Question

Hi all,

We are migrating  SQL Sever 2000 DB into 2008 R2 ,  a field in old system has unique and non clustered index on it but we see dupes  on fields where index is defined , we realised it only when we tried loding the data into 2008 R2 database , not sure how they crept up in the old system . Is there any implimentaion change from 2000 to 2008 reg unique non-Clutered indexes ??. We cannot ignore them as field will be used as primary key after dataload. 

any help and inputs are appreciated.

Thanks all,

Vishal.

Answer 1

It could have been bug in SQL Server 2000. Will you be able to correct the problem before importing and making sure that no duplicates will be in that field?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

it seems some issue in script or process of setting up database over SQL 2008 from SQL 2000.

Unique, Non clustered index will work fine on both SQL 2008 & SQL 2000.

Compare the table script for index & check same is created or not on SQL 2008.

---

Regards,
Rohit Garg
(My Blog)
This posting is provided with no warranties and confers no rights.
Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

Answer 3

Is it possible that you load the data first and then try applying unique index with duplicates on it , this isdefinetly not allowed in 2008 and may have been allowed in 2000 .

Answer 4

The SQL 2000 index may have been created with:

IGNORE_DUP_KEY

Controls what happens when an attempt is made to insert a duplicate key value into a column that is part of a unique clustered index. If IGNORE_DUP_KEY was specified for the index and an INSERT statement that creates a duplicate key is executed, SQL Server issues a warning and ignores the duplicate row.

Please see:

http://msdn.microsoft.com/en-us/library/aa258260(v=sql.80).aspx

As Naomi said, if this is a primary key, you are required to fix duplicates in the SQL 2000 source, before copying it to SQL 2008 or any version.

Answer 5

Any progress?

The following blog is on duplicate removal:

http://www.sqlusa.com/bestpractices/eliminateduplicates/

---

Kalman Toth SQL 2008 GRAND SLAM
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012