Thanks so much, Jeff. I can't believe I had not thought of this!

Ok the problem has nothing to do with partitioning. I still don't know what the problem is but at least I know partitioning isn't the cause.

If I check the...

That was a typo. The nonclustered primary key actually was created on the correct student_attendance table and not the bkup.

If I allow dupes then there will be dupes in...

No Problem adding PK to BKUP table. The duplicate check query you said to try, which I've already run myself on the original table and backup table, produces no...

If I create a new, empty table that is NOT partitioned and place a primary key on the ID column then copy the data to the new table I do...

The new partitioned table IS clustered on DATE with a nonclustered primary key on ID. Problem I'm having (which is NOT a problem if I just use a test...

The current table has a clustered primary key on the ID column (nchar) and no index on the DATE column at all. This table is copied to a backup...

The table will continue to grow as there are no plans for archiving, which is why I am thinking about partitioning. There COULD be as many as 3.6B rows...

Thanks for your help. It worked in my test case. But my live table with 23M rows is another story.

For some reason when I create the...

I thought I was creating a partitioned table since the following query looked like it showed record counts in separate partitions:

SELECT $PARTITION.LargeTable_PartitionFunction(AbcDate) AS PARTITIONID,...

Ok this is really driving me crazy. I followed your suggestion but have the same problem with duplicates.

If I make a nonunique index on the ID column the total...

Oh and I ran DBCC CHECKDB on the database before and after the table renaming and new table creations and there are no problems showing in the database.

Not sure what that post was all about, canonzone??

I have been trying to figure out what is going on and have queried the renamed (staging) table as well as the...

Yes, the original table has a primary key on the same AbcID column. I added a step to drop the primary key constraint before renaming the table to staging...

Let me ask this... Does the process of partitioning a table add the partition key to the primary key? This is the only thing I can think of that...