November 28, 2003 at 2:45 pm
Hi there, I got this really weird behavor of my sqlserver 2000. I used the DTS to transfter a large table from a sybase server to my sqlserver. That table in sybase, let's call it table_orignial has roughly 2 million rows.
When I run the DTS to transfer it, it seems take forever, and I saw the number continues to increase even after 8 million rows has been transfer. So I stopped it.
now I got 8200,000 rows in my transftered table in my sql server.
quite surprised?! couldn't figure out what happened. So I checked the original table, it has a composed clustered index on it. Then I created the same cluster index on my transfered table, it created successfully!
Is that means that table definitely has no duplicated rows?! (cause I thought data might be duplicated while transfering....)
Is there any way to find out?! Or any way to find out the last row of my table?! it is really large, takes forever to select all the rows...
Thanks a lot!
November 30, 2003 at 5:33 pm
>> Is that means that table definitely has no duplicated rows?!
No. Only a unique index will prevent duplicates. A clustered index just orders the rows.
If you have inserted over 8 million rows from a 2 million row table then you must have duplicates.
You can check the number of rows quickly by
select rows from stsindexes where id = object_id('mytbl') and indid in (0,1)
Pick a value in the unique index on the sybase table and select it from sql server. You should find duplicates and it should be quick due to the index.
if that doesn't work
set rowcount 10
select col1, col2, col3, count(*)
from tbl
group by col1, col2, col3
set rowcount 0
where col1, col2, col3 are the unique fields.
Cursors never.
DTS - only when needed and never to control.
Cursors never.
DTS - only when needed and never to control.
November 30, 2003 at 6:38 pm
Just to add to Nigel's comments, once you do get the query straight to not get the dupes, a composite clustered index is not typically reccomended, but if you are going to use it, importing in order of the clustered key will help speed the import up.
HTH
Ray Higdon MCSE, MCDBA, CCNA
------------
Ray Higdon MCSE, MCDBA, CCNA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply