DZone
Database Zone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
  • Refcardz
  • Trend Reports
  • Webinars
  • Zones
  • |
    • Agile
    • AI
    • Big Data
    • Cloud
    • Database
    • DevOps
    • Integration
    • IoT
    • Java
    • Microservices
    • Open Source
    • Performance
    • Security
    • Web Dev
DZone > Database Zone > Elastic Query in Azure SQL Database and Views

Elastic Query in Azure SQL Database and Views

Creating views that join across databases? Read this article to find out how to make those views play nice with Elastic Query.

Grant Fritchey user avatar by
Grant Fritchey
·
Jun. 07, 16 · Database Zone · Tutorial
Like (3)
Save
Tweet
4.96K Views

Join the DZone community and get the full member experience.

Join For Free

The question came up: How do the constructs necessary for Elastic Query within Azure SQL Database affect your ability to create views that join across databases? Since I haven’t tested this myself, and I haven’t seen anyone else doing anything with it yet, I decided to set up a test. I recreated my cross database queries using the scripts posted here. Let’s create a view:

CREATE VIEW dbo.JoinedView
AS
SELECT dt.Val,
 dt2.Val AS Val2
FROM dbo.DB1Table AS dt
LEFT JOIN dbo.DB2Table AS dt2
 ON dt2.ID = dt.ID;

If I run the query, I get back results. Done. We can create views that join between Azure SQL Databases… But, views are all about masking right? What if I wanted to change the name of the table in my database. Could I do something like this?

CREATE EXTERNAL TABLE dbo.CanICheatThisName (
ID int, 
Val varchar(50)) 
WITH (
DATA_SOURCE = DB2Access);

The CREATE script runs just fine with no errors. When I try to query it, though:

Msg 46823, Level 16, State 2, Line 55
 Error retrieving data from one or more shards.  The underlying error message received was: ‘Invalid object name ‘dbo.CanICheatThisName’.’.

Understanding how the Elastic Query works, of course this fails. So… what about faking it the other way? I’ll create a view in my second database:

CREATE VIEW dbo.FakeTable
AS
SELECT *
FROM dbo.DB2Table AS dt;

Now, I’ll create the EXTERNAL TABLE using the name of the view:

CREATE EXTERNAL TABLE dbo.FakeTable (
ID int, 
Val varchar(50)) 
WITH (
DATA_SOURCE = DB2Access);

When I query this, the data comes across just fine.

Conclusion

Creating a view, or any other query, that joins across databases using Elastic Query works just fine. However, if you want to mask things using a view, you might need to get a little creative in how you implement Elastic Query. The good news is, Elastic Query is somewhat, shall we say, elastic in how you set it up. More so than it immediately appears.

Database sql azure

Published at DZone with permission of Grant Fritchey, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Flutter vs React Native. How to Cover All Mobile Platforms in 2022 With No Hassle
  • How to Use Geofences for Precise Audience Messaging
  • Kafka Fail-Over Using Quarkus Reactive Messaging
  • A Guide to Understanding Vue Lifecycle Hooks

Comments

Database Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • MVB Program
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends:

DZone.com is powered by 

AnswerHub logo