Access’ DISTINCTROW keyword explained – it means WHERE EXISTS

Steve Koop spoke recently at the Adelaide SQL Server User Group, talking about things which don’t convert particularly nicely when upsizing from Microsoft Access to SQL Server 2008. I think this is a really important thing for SQL people to know, as there seem to be many Access databases living in even the largest organisations.

One of the things he mentioned was DISTINCTROW. I’ve never really known what DISTINCTROW does, so I asked him. He sent me a link which explained the difference between DISTINCTROW and DISTINCT, and it described as “DISTINCTROW works on records, not just individual fields”. This might be good for some people, but I wanted to know a little more.

http://msdn.microsoft.com/en-us/library/aa140015.aspx says “The DISTINCTROW keyword is similar to the DISTINCT keyword except that it is based on entire rows, not just individual fields.” – but it also goes on to say a little more, confirming my suspicions. “It is useful only when based on multiple tables, and only when you select fields from some, but not all, of the tables.”

So DISTINCTROW is more about the JOIN type than anything else. It only applies if you are querying multiple tables, but not returning fields from all of them. So it’s a SEMI JOIN to the unused tables, which you write SQL Server using a WHERE EXISTS clause. It’s not really like DISTINCT at all – it’s about doing a Join without seeing the ‘duplication’ effect, clearly only feasible if you’re not returning columns from the other table.

If you’re not sure what a Semi Join is, then just think about the WHERE EXISTS clause, and it should become clear. If you look at the execution plan of a query in SQL Server that uses WHERE EXISTS, you’ll see that it’s doing a Semi Join. And if you’re looking at queries which use DISTINCTROW, consider changing them to WHERE EXISTS instead.

6 thoughts on “Access’ DISTINCTROW keyword explained – it means WHERE EXISTS”

  1. Easy to get pills online! Buy now! Order online and get viagra pills BONUS for every order! Buy Viagra On Line
    http://www.thegeenadavisinstitute.org/forum/index.php?action=profile;u=12900 Viagra H k i s C 9 4 1 3 0 Buy from us at the best prices pills and others by 70-80% cheaper! http://board.teamdistort.com/index.php?action=profile;u=34226 Buy Cialis On Line S A v b p 6 8 1 9 7 VISA, MasterCard. USD EUR GBP. http://www.thegeenadavisinstitute.org/forum/index.php?action=profile;u=12885 Buy Levitra 3 T 2 D y 8 9 0 5 4 Low prices, fast delivery, high reliability. http://fr.smash-online.com/forum/member.php?u=10705 Buy Propecia On Line Z W M f 4 8 3 5 1 4 No prescription needed! Customer support available 24/7! Fast worldwide shipping! http://board.teamdistort.com/index.php?action=profile;u=34223 Acomplia Online U 8 c s x 4 2 0 3 6 ONLINE DRUGSTORE 24X7! BUY PILLS ONLINE! 4-12 FREE PILLS FOR OTHER ORDERS!
    Low prices, fast delivery, high reliability. Enter and check it lowest pills prices. Viagra
    http://www.freecodesource.com/community/forums/showthread.php?p=53370 Viagra h J y 6 f 7 3 0 8 2 Pills online – save time and money. Great quality! http://board.teamdistort.com/index.php?topic=95727.0 Cialis z 0 B 9 f 5 0 6 9 1 Check pills prices, save your dollars! http://swimmablenyc.info/forum/index.php?topic=112541.0 Buy Levitra On Line O H 1 i b 6 5 2 0 7 100% Satisfaction Guarantee or your Money Back. http://board.teamdistort.com/index.php?topic=96118.0 Propecia Online 4 U k O a 7 6 8 3 4 We accept Visa, Mastercard, AMEX, E Check & other. http://board.teamdistort.com/index.php?topic=95710.0 Acomplia S 6 X B I 9 7 0 5 8 Save your time – buy pills right now!
    http://www.playlist.com/blog/entry/12492741891 buy Viagra http://www.playlist.com/blog/entry/12492728323 buy viagra in australia
    http://www.playlist.com/blog/entry/12492704259 buy viagra in canada http://www.playlist.com/blog/entry/12492680195 buy viagra low cost http://www.playlist.com/blog/entry/12492602371 buy viagra
    http://0-buying.cheaprxshop.com/map.html 1 4 dose propecia. http://buyonlinerx.t35.com propecia chemical http://cost-low.cheaprxshop.com/map.html cost of propecia. persistent propecia effects http://propecia-that.cheaprxshop.com/map.html propecia the prostitute. propecia and receding hairline http://propecia-and.cheaprxshop.com/map.htm |propecia and transsexual. propecia dont take http://propecia-liver.cheaprxshop.com/map.html propecia long term. propecia blinddate parody http://internet-pharma.cheaprxshop.com/map.html is buying propecia online legal. proscar instead of propecia
    http://0-buying.cheaprxshop.com/index.html 1 4 dose propecia. propecia chemical http://cost-low.cheaprxshop.com/index.html cost of propecia. persistent propecia effects cost of propecia. persistent propecia effects
    http://propecia-that.cheaprxshop.com/index.html propecia the prostitute. propecia and receding hairline propecia the prostitute. propecia and receding hairline http://propecia-and.cheaprxshop.com/index.html propecia and transsexual. propecia dont take propecia and transsexual. propecia dont take http://propecia-liver.cheaprxshop.com/index.html propecia long term. propecia blinddate parody propecia long term. propecia blinddate parody http://internet-pharma.cheaprxshop.com/index.html is buying propecia online legal. proscar instead of propecia is buying propecia online legal. proscar instead of propecia

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>