Introduction

Microsoft Azure offers hundreds of cloud products, with more on the way. In the database space, Microsoft offers Azure Cosmos DB, a NoSQL database product. We can build Java, .Net, etc. applications around Cosmos DB resources. To extend this capability, this article will show how to query Cosmos DB directly from SQL Server.

The SQL Server database

We’ll start with the sample OFFICE_EQUIPMENT_DATABASE database seen in my earlier SQL Shack articles, with modifications. Built-in SQL Server 2014 Standard on a Windows 10 Education edition device, it has three tables, as shown below:

• OFFICES
• OFFICE_EQUIPMENT
• OFFICE_MANAGERS

And this stored procedure,** SP_SQLSERVER_COSMOSDB_OFFICE_EQUIPMENT.**

Run the first script in SQL Shack article Lever T-SQL to dynamically define duplicate SQL Server database table rows to build the basic OFFICE_EQUIPMENT_DATABASE database. Then, run the below script to build the resources in that database that we’ll need for this article:

USE [OFFICE_EQUIPMENT_DATABASE]

CREATE TABLE [dbo].[OFFICES](
  [OFFICE_ID] [int] NOT NULL,
  [OFFICE_MANAGER_ID] [int] NOT NULL,
  [CITY] [nchar](50) NOT NULL,
  [STATE_OR_PROVINCE] [nchar](2) NOT NULL,
  [FLOOR_SPACE] [int] NOT NULL
)
GO

CREATE TABLE [dbo].[OFFICE_MANAGERS](
  [OFFICE_MANAGER_ID] [int] NOT NULL,
  [FIRST_NAME] [nvarchar](50) NOT NULL,
  [LAST_NAME] [nvarchar](50) NOT NULL,
  [EMAIL] [nvarchar](75) NOT NULL,
  [SALARY] [money] NOT NULL
) 
GO

INSERT [dbo].[OFFICE_MANAGERS] ([OFFICE_MANAGER_ID], [FIRST_NAME], [LAST_NAME], [EMAIL], [SALARY]) VALUES (1, N'Jan', N'Dodonna', N'jdodonna@yavin.edu', 250000.0000)
GO
INSERT [dbo].[OFFICE_MANAGERS] ([OFFICE_MANAGER_ID], [FIRST_NAME], [LAST_NAME], [EMAIL], [SALARY]) VALUES (2, N'James', N'Kirk', N'jtkirk@ncc1701.gov', 5450000.0000)
GO
INSERT [dbo].[OFFICE_MANAGERS] ([OFFICE_MANAGER_ID], [FIRST_NAME], [LAST_NAME], [EMAIL], [SALARY]) VALUES (3, N'Joseph', N'Crater', N'judgecrater@erehwon.edu', 1350000.0000)
GO
INSERT [dbo].[OFFICE_MANAGERS] ([OFFICE_MANAGER_ID], [FIRST_NAME], [LAST_NAME], [EMAIL], [SALARY]) VALUES (4, N'Charles', N'Kane', N'kane@xanadu.org', 13050000.0000)
GO
INSERT [dbo].[OFFICE_MANAGERS] ([OFFICE_MANAGER_ID], [FIRST_NAME], [LAST_NAME], [EMAIL], [SALARY]) VALUES (5, N'Randy', N'Stone', N'rstone@chicagoilstar.com', 100000.0000)
GO
INSERT [dbo].[OFFICE_MANAGERS] ([OFFICE_MANAGER_ID], [FIRST_NAME], [LAST_NAME], [EMAIL], [SALARY]) VALUES (6, N'Britt', N'Ponset', N'ponset@outwest.edu', 75000.0000)

INSERT [dbo].[OFFICES] ([OFFICE_ID], [OFFICE_MANAGER_ID], [CITY], [STATE_OR_PROVINCE], [FLOOR_SPACE]) VALUES (1, 3, N'Pasadena', N'CA', 2500)
GO
INSERT [dbo].[OFFICES] ([OFFICE_ID], [OFFICE_MANAGER_ID], [CITY], [STATE_OR_PROVINCE], [FLOOR_SPACE]) VALUES (2, 6, N'Phoenix', N'AZ', 3000)
GO
INSERT [dbo].[OFFICES] ([OFFICE_ID], [OFFICE_MANAGER_ID], [CITY], [STATE_OR_PROVINCE], [FLOOR_SPACE]) VALUES (3, 5, N'Denver', N'CO', 1750)
GO
INSERT [dbo].[OFFICES] ([OFFICE_ID], [OFFICE_MANAGER_ID], [CITY], [STATE_OR_PROVINCE], [FLOOR_SPACE]) VALUES (4, 4, N'Dallas', N'TX', 2500)
GO
INSERT [dbo].[OFFICES] ([OFFICE_ID], [OFFICE_MANAGER_ID], [CITY], [STATE_OR_PROVINCE], [FLOOR_SPACE]) VALUES (5, 2, N'Boise', N'ID', 2750)
GO
INSERT [dbo].[OFFICES] ([OFFICE_ID], [OFFICE_MANAGER_ID], [CITY], [STATE_OR_PROVINCE], [FLOOR_SPACE]) VALUES (6, 1, N'Vancouver', N'BC', 3125)
GO

--  Add the OFFICE_ID column to the OFFICE_EQUIPMENT table,
--  and update OFFICE_ID values for some of the rows

ALTER TABLE [dbo].[OFFICE_EQUIPMENT]
ADD OFFICE_ID INT NULL;
GO

UPDATE  OFFICE_EQUIPMENT
SET OFFICE_ID = 1
WHERE OFFICE_EQUIPMENT_ID IN (8, 17, 18)

UPDATE  OFFICE_EQUIPMENT
SET OFFICE_ID = 2
WHERE OFFICE_EQUIPMENT_ID IN (5, 16)

UPDATE  OFFICE_EQUIPMENT
SET OFFICE_ID = 3
WHERE OFFICE_EQUIPMENT_ID IN (4, 14)

UPDATE  OFFICE_EQUIPMENT
SET OFFICE_ID = 4
WHERE OFFICE_EQUIPMENT_ID IN (20, 22)

/****** Object: StoredProcedure [dbo].[SP_SQLSERVER_COSMOSDB_OFFICE_EQUIPMENT] Script Date: 7/18/2020 2:00:16 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_SQLSERVER_COSMOSDB_OFFICE_EQUIPMENT]

  @OFFICE_ID int = NULL

AS

/*

  To use: 

    EXEC SP_SQLSERVER_COSMOSDB_OFFICE_EQUIPMENT
    EXEC SP_SQLSERVER_COSMOSDB_OFFICE_EQUIPMENT 1
    EXEC SP_SQLSERVER_COSMOSDB_OFFICE_EQUIPMENT -3
 */

SELECT  OE.OFFICE_EQUIPMENT_NAME AS 'Office Equipment Name',
  COE.OFFICE_ID AS 'Office ID',
  OE.PURCHASE_DATE AS 'Purchase Date',
  COM.FIRST_NAME + ' ' + COM.LAST_NAME AS 'Office Manager'
FROM  OFFICE_EQUIPMENT OE LEFT OUTER JOIN
  (
    SELECT  CITY, FLOOR_SPACE, OFFICE_ID,
      OFFICE_MANAGER_ID, STATE_OR_PROVINCE
    FROM  OPENQUERY(COSMOSDB,
      'SELECT CITY, FLOOR_SPACE, OFFICE_ID,
          OFFICE_MANAGER_ID, STATE_OR_PROVINCE
       FROM   OFFICES')
    ) COE
    ON OE.OFFICE_ID = COE.office_id
    LEFT OUTER JOIN
    (
      SELECT  OFFICE_MANAGER_ID, FIRST_NAME, LAST_NAME, EMAIL, SALARY
      FROM  OPENQUERY(COSMOSDB,
        'SELECT OFFICE_MANAGER_ID, FIRST_NAME,
            LAST_NAME, EMAIL, SALARY
         FROM   OFFICE_MANAGERS')
    ) COM
    ON COE.OFFICE_MANAGER_ID = COM.OFFICE_MANAGER_ID
WHERE @OFFICE_ID IS NULL OR
  OE.OFFICE_ID = @OFFICE_ID
GO

We’ll use Azure Cosmos DB to host data that the SQL Server stored procedure will query. Although Azure products can become expensive, depending on data volumes and product use, Azure offers a free trial pricing plan. The data we’ll host will fall within the trial plan limits. This SQL Shack article,Getting Started with Azure Cosmos DB, explains how to get going with it, so we won’t cover that topic in this article. For this article, we’ll need an Azure Cosmos DB resource with firewall and virtual network settings that will accept queries that originate from the internet.

#azure cosmos db #json #stored procedures #sql #azure