find a location for property in a new city

Tuesday, 8 November 2011

Cross database joins in SQL Azure

Currently cross database joins are not supported in SQL Azure. Also you cannot change database mid query so you cannot, for example, put a USE [MyDB] in your query either. As a side note, please vote for it to be a priority feature for the SQL Azure team to develop soon.

So, since cross database joins are not supported at this time you must find a workaround. I will give you two possible solutions I would recommend and you can hopefully choose the one that is best for your application.

Combine your databases

If you have tables that are frequently used together, i.e. they are joined in queries or the rows are inserted in the same transactions, then it would be a good idea to move the similar tables into the same database. This of course eliminates the need to traverse databases. SQL Azure has very recently increased the maximum database size from 50GB to 150GB, which potentially makes this a more viable option than perhaps it once was.

Join your data in your application

Two separate queries could be run on the two separate databases and then these results could be joined within that application. Obvious downsides to this will be the potential for large DB I/O, large network transfer and large memory usage in your app. This is not something to consider if the amount of data that is likely to be returned is large (e.g. 1000+ rows) but it is fine if the data will be manageable.

Conclusion

Personally I would much rather settle for bringing all my similar tables that are likely to be used within the same queries together into one database so there is no longer a need for cross database solutions. This makes for cleaner application code and more efficient use of your resources. However, if this option is not available to you then perhaps the second option may have to be the one you choose.

Surely soon in the future the SQL Azure team will address this issue though and your cross database code can stay clean! Although in fairness cross database querying isn't even available in Entity Framework yet (but still workaround-able) either. I wonder why this is so difficult for Microsoft? Just shows you should always try and combine similar DBs in any database designs where possible.

Follow britishdev on Twitter

59 comments:

  1. I am very glad to know a lot from you this meaningful.your weight loss is rich with a lot of useful and helpful information.
    custom essay

    ReplyDelete
  2. Did you just realise you have an assignment pending for the submission in two days? You need not worry! Just place an urgent order at Law Writers UK. You don’t need to ask anyone can you do my assignment urgent, when we’re just one tap away. Whether your assignment is due for submission in a few days or a few hours, we can handle the burden by meeting the deadline. We are well-known for providing high-quality content and employing a team of excellent writers with a proven track record. With our 24/7 online support, you can track your order to stay updated about the progress.

    Also See: How To Write Dissertation

    ReplyDelete
  3. I am very glad to know a lot from you this meaningful.your weight loss is rich with a lot of useful and helpful information. دانلود آهنگ های ایرانی

    ReplyDelete
  4. Buy online wholesale printed custom cbd boxes and packaging at easy prices and shipping worldwide, with 24/7 customer support. Get a free custom quote now!!

    ReplyDelete
  5. Blackbird packaging provide you with any size, shape design, lipstick boxes sooner than any other packaging competitor in the market

    ReplyDelete
  6. Blackbird packaging provide you with any size, shape design, cookie boxes wholesale sooner than any other packaging competitor in the market

    ReplyDelete
  7. Blackbird packaging provide you with any size, shape design, lipstick boxes sooner than any other packaging competitor in the market

    ReplyDelete
  8. Blackbird packaging provide you with any size, shape design, custom cbd boxes sooner than any other packaging competitor in the market

    ReplyDelete
  9. Blackbird packaging provide you with any size, shape design, wholesale cookie boxes sooner than any other packaging competitor in the market

    ReplyDelete
  10. Pacakaging town is a one stop solution for the short-run cbd facial oil boxes, needs and supplies. At affordable prices. Request a free quote now.

    ReplyDelete
  11. Packaging town is a one stop solution for the short-run tincture packaging needs and supplies. At affordable prices. Request a free quote now.

    ReplyDelete
  12. Great Article
    Cloud Computing Projects




    Networking Projects



    Final Year

    Projects for CSE





    JavaScript Training in Chennai



    JavaScript

    Training in Chennai




    The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals,

    Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer.

    Angular Training

    ReplyDelete
  13. Packers and Movers Bangalore - Reliable and Verified Household Shifting Service Providers Give Reasonable ###Packers and Movers Charges. Cheap and Best Office Relocation Compare Quotation for Assurance for Local and Domestic House Shifting and Get estimates today to save upto 20%, ***Read Customer Reviews - @ Packers And Movers Bangalore

    ReplyDelete
  14. Buy custom vape boxes online from blackbird packaging at best prices. get free shipping.

    ReplyDelete
  15. Blackbird packaging company can provide you with any size, shape design, and bath bomb boxes sooner than any other packaging competitor in the market

    ReplyDelete
  16. buy cbd vape cartridge packaging online from blackbird packaging at best prices. Get Free shipping

    ReplyDelete
  17. buy lahore grocery from alfatah at best prices. Get free shipping

    ReplyDelete
  18. Blackbird packaging company can provide you with any size, shape design, and cbd vape cartridge packaging sooner than any other packaging competitor in the market

    ReplyDelete
  19. We at Al-Fatah have perfected the floor care with the right variety of Vacuum Cleaners online available under one roof. Al-Fatah is a One-Stop Shop for all your home appliances buying needs. Buy best variety of Vacuum Cleaners in Pakistan at Al-Fatah. Shop Vacuum Cleaners of Top & Genuine Brands in Pakistan at Al-Fatah now!

    ReplyDelete
  20. buy grocery online in lahore from alfatah at best prices. Get free shipping

    ReplyDelete
  21. Shop from a wide range of Local and Imported variety of makeup removers and makeup remover wipes from Al-Fatah. Shop from a diversified range of makeup removers and makeup remover wipes from all top brands. Buy the best for your skin. Avail Free Delivery on orders above 1000 PKR for selected cities in Pakistan.

    ReplyDelete
  22. Buy best dispenser online from Al-Fatah at best prices. Get free shipping.

    ReplyDelete
  23. Packaging town is a one stop solution for the short-run custom soap boxes needs and supplies. At affordable prices. Request a free quote now.

    ReplyDelete
  24. Buy exercise cycles online from Al-Fatah at best prices. Get free shipping.

    ReplyDelete
  25. Blackbird packaging company can provide you with any size, shape design, and custom cookie boxes sooner than any other packaging competitor in the market

    ReplyDelete
  26. Packaging town is a one stop solution for the short-run custom soap boxes needs and supplies. At affordable prices. Request a free quote now.

    ReplyDelete
  27. We at Al-Fatah have perfected the floor care with the right variety of Vacuum Cleaners available under one roof. Al-Fatah is a One-Stop Shop for all your home appliances buying needs. Buy best variety of Vacuum Cleaners in Pakistan at Al-Fatah. Shop Vacuum Cleaners of Top & Genuine Brands in Pakistan at Al-Fatah now!

    ReplyDelete
  28. Wild Goat brings you Polo shirts like you have never seen before. Prioritizing your style and comfort as well as preserving our national heritage. Wild Goat’s Polos will guarantee the perfect fit each time! Get your Polos now at a 20% off.

    ReplyDelete
  29. Blackbird packaging provide you with any size, shape design, bath bomb packaging sooner than any other packaging competitor in the market

    ReplyDelete
  30. Packaging town is a one stop solution for the short-run custom soap boxes needs and supplies. At affordable prices. Request a free quote now.

    ReplyDelete
  31. Blackbird packaging provide you with any size, shape design, bath bomb boxes sooner than any other packaging competitor in the market

    ReplyDelete
  32. Blackbird packaging provide you with any size, shape design, bath bomb packaging boxes sooner than any other packaging competitor in the market

    ReplyDelete
  33. Packaging town is a one stop solution for the short-run custom soap boxes needs and supplies. At affordable prices. Request a free quote now.

    ReplyDelete
  34. A good blog always comes-up with new and exciting information and while reading I have feel that this blog is really have all those quality that qualify a blog to be a one. it's time to avail this Air Duct Cleaning Services Lakewood visit here for more details.

    ReplyDelete
  35. I recently found many useful information in your website especially this blog page. Among the lots of comments on your articles. Thanks for sharing. it's time to avail this garage door repair kennesaw visit here for more info.

    ReplyDelete
  36. Logo Design Dubai Company having experts in logo designing who providing the best quality services of logos like brand, creative, graphic design at affordable cheap logo design service by creative and professional graphic designers in 24 hours.

    ReplyDelete
  37. buy galaxy leggings online shopping from dshred at best prices deliered at your doorstep. Get free shipping

    ReplyDelete
  38. Buy the latest Luxury Chiffon Collection 2021 from Reign and enjoy the best price with free home delivery.

    ReplyDelete
  39. Buy the latest Organza Collection 2021 from Reign and enjoy the best price with free home delivery.

    ReplyDelete
  40. Buy the latest Lawn Collection from Reign and enjoy the best price with free home delivery.

    ReplyDelete
  41. Buy electronics online in Pakistan from alfatah at best prices delivered at your doorstep. Get free Shipping

    ReplyDelete
  42. Buy sports bra online from Dshred at easy prices delivered at your doorstep. Get free shipping

    ReplyDelete
  43. Buy jogger pants men from ndure at best prices delivered at your doorstep. Get free shipping

    ReplyDelete
  44. Buy gym trousers for men online dshred at best prices delivered at your doorstep. Get free shipping

    ReplyDelete
  45. Now You can buy lactogen price in Pakistan from medisense where all kind of medicine and baby foods are available. Quick delivery.

    ReplyDelete
  46. Buy best quality blood pressure machine in Pakistan online at affordable prices from medisense. Fast delivery.

    ReplyDelete
  47. Now You can buy Online certeza nebulizer price in Pakistan from medisense where all kind of nebulizer and steamers are available. Quick delivery.

    ReplyDelete
  48. Buy best quality sexual products online shopping in Pakistan from medisense at your door step. Order Now!

    ReplyDelete
  49. Buy gym trousers for men online from dshred at best prices delivered at your doorstep. Get free shipping

    ReplyDelete
  50. We do extra care of your babies. Buy best quality baby care products online in Pakistan from medisense. Order Now and get fast delivery.

    ReplyDelete
  51. Buy best quality hair care supplements in Pakistan at best prices from medisense. Order Now and get fast delivery at your doorstep.

    ReplyDelete
  52. Buy Good quality baby food online in Pakistan at best prices from medisense. Order Now and get fast delivery.

    ReplyDelete
  53. Buy Mens t-shirt online from Ndure at best prices delivered at your doorstep. Get free shipping.

    ReplyDelete
  54. Buy toys online from Al-Fatah at best prices delivered at your doorstep. Get free shipping

    ReplyDelete
  55. Buy men clothing online in Pakistan at best prices delivered at your doorstep. Get free shipping.

    ReplyDelete
  56. buy grocery online in Pakistan at best prices delivered at your doorstep. Get free shipping.

    ReplyDelete