Data returned twice - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

Data returned twice

Author

Topic

steeffie
Starting Member

11 Posts

Posted - 2009-02-11 : 03:25:16

I've attached an mdf file to a SQL Server 2008 express edition. The mdf file comes from SQL Server 2005. It seems to work fine, but when I query tables all data is returned twice.

If for instance the table contains 10 rows and I would select all rows it will display row 1 to 10 and then row 1 to 10 again. Row count would be displayed as 20.

Someone familiar with this?

steeffie
Starting Member

11 Posts

Posted - 2009-02-11 : 03:43:00

In addition to this Ive noticed that using DISTINCT would not display the rows twice.

New tables Ive manually added to the database do not have this issue, so it occurs only with the tables that were present in the attached database file.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-11 : 04:02:46

Seems something is wrong with your indexes.
Recreate your indexes.

E 12°55'05.63"
N 56°04'39.26"

steeffie
Starting Member

11 Posts

Posted - 2009-02-11 : 04:13:07

The tables do not seem to have any indexes. I cant add unique indexes now because it will never return unique values for ID-columns. Strange.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-11 : 04:31:45

What do

SELECT COUNT(*), COUNT(Col1), COUNT(DISTINCT Col1) FROM Table1

return?

Substitute Col1 and Table1 with proper columnname and tablename.

E 12°55'05.63"
N 56°04'39.26"

steeffie
Starting Member

11 Posts

Posted - 2009-02-11 : 04:39:54

It would return 16,16,8 when using a table with 8 rows.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-11 : 04:50:21

I believe there are 16 records in the table...

What query do you use to tell there are only 8 records in the table?

E 12°55'05.63"
N 56°04'39.26"

steeffie
Starting Member

11 Posts

Posted - 2009-02-11 : 04:58:33

I am 100% absolutely certain that there are only 8 rows in this tabel. It starts with a ID column and it returns all ID's twice. Maybe the attach process doesnt work ok when attaching a sql server 2005 file.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-11 : 05:00:33

Yes it does work.
Is the ID column an IDENTITY column? Is it primary key also?

E 12°55'05.63"
N 56°04'39.26"

steeffie
Starting Member

11 Posts

Posted - 2009-02-11 : 05:13:22

It has no indexes as i said before. Unfortunately I dont know if the table doesnt have indexes on the original SQL server, I just got the mdf files. Maybe the indexes went lost when attaching the files.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-11 : 05:16:40

If you do a

SELECT * FROM Table1

what do the execution plan tell you?

E 12°55'05.63"
N 56°04'39.26"

steeffie
Starting Member

11 Posts

Posted - 2009-02-11 : 05:26:06

It performs a table scan and that the table has 16 rows (Actual Number of Rows).

So yes, normally i would also say it contains 16 rows, but knowing what is stored in it I am 100% sure it contains 8 rows (at least in the original tabel on the original SQL Server)

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2009-02-11 : 06:36:01

There's no way that an upgrade/attach will ever alter user table data except to run recovery if the original database was not shutdown cleanly. The table must have contained 16 rows before you upgraded.

Paul S. Randal, Managing Director, SQLskills.com (www.SQLskills.com/blogs/paul)
SQL Server MVP, Contributing Editor of TechNet Magazine
Author of SQL 2005 DBCC CHECKDB/repair code
Author & Instructor of Microsoft Certified Master - Database course

steeffie
Starting Member

11 Posts

Posted - 2009-02-11 : 07:23:28

If it was just one table i would inmmediately believe it, but we are talking about 50+ tables which al have this issue.

Its a database being used by a website. I noticed it when all dropdown controls contained all values twice. So we are not talking about some incident, its a big problem.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-11 : 07:48:11

Did someone import the complete database and all tables into self and thus doubling the amount of records for every table?

E 12°55'05.63"
N 56°04'39.26"

steeffie
Starting Member

11 Posts

Posted - 2009-02-11 : 08:01:11

I did not import data, I just added the database by attaching the mdf file. But I'll ask the person I got the files from if the data is ok.

Thx by the way for all your time to respond! Thumb up.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-11 : 08:05:07

Run this query for more tables than you didi before, to see if more tables are "doubled" up.

SELECT COUNT(*), COUNT(Col1), COUNT(DISTINCT Col1) FROM Table1

E 12°55'05.63"
N 56°04'39.26"

steeffie
Starting Member

11 Posts

Posted - 2009-02-11 : 08:12:58

All of them are doubled up, well at least all the ones i checked.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-11 : 08:20:03

That strengthen my suspicion that someone has, by mistake, imported all tables in the database into self instead of detach/attach.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-11 : 08:46:59

Here is how you can delete the duplicate records table by table.

DELETE	f
FROM	(
		SELECT	ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col1) AS recID
		FROM	Table1
	) AS f
WHERE	recID > 1

When this delete is done, you can create your indexes.

E 12°55'05.63"
N 56°04'39.26"

steeffie
Starting Member

11 Posts

Posted - 2009-02-11 : 08:59:48

Thx, that did indeed remove the double records. Ive asked the person who gave me the mdf files if he can provide me with new files.

So, you were right the tables contained the rows twice. But I guess you were convinced of that anyway ;-)

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources