Select Distinct in SQL Server vs Access

views:

114

answers:

+1 Q:

Select Distinct in SQL Server vs Access

I have a large query that pulls data from 3 tables. It is a SELECT DISTINCT query.

I am in the process of migrating this query from Access to SQL Server.

The query in Access, which uses linked tables to SQL Server, returns 920K records.

The query in SQL Server (querying the same 3 tables) returns 1.1 million records.

They are the same query. There is nothing fancy like a group by or anything. Just a straight pull.

When I do a Select count(*) from the 3 joined tables I get the same amount of records in SQL as I do in Access.

Do SQL Server and Access perfrom SELECT DISTINCT queries differently? I have a hunch that this may have something to do with NULL values, but I'm not really sure.

Any thoughts?

Thanks

+2 A:

I'm assuming your using distinctrow since it would explain the difference.

Distinctrow is not the same as distinct see: http://office.microsoft.com/en-us/access/HA012313511033.aspx

JJoos 2009-09-03 15:22:11

Indeed, it's not the same, and that's the whole reason for it to exist. But DISTINCTROW would tend to give you *more* rows in Access than you'd get with DISTINCT in SQL Server, so it's unlikely it's the cause of the discrepancy. FWIW, DISTINCTROW was the default for all SELECT queries up through Access 2, and, thankfully, they removed that starting with Access 95.

David-W-Fenton 2009-09-03 23:50:25

@David W. Fenton: I think you'll find it was Access97 and Jet 3.5 when they changed DISTINCTROW behaviour (http://support.microsoft.com/kb/168438). P.S. DISTINCTROW is an abomination (revealing that the Access Database Engine is not a SQL) and should never be used.

onedaywhen 2009-09-04 10:33:43

ansaurus

tags:

views:

answers:

Select Distinct in SQL Server vs Access

related questions