Stored procedures are an effective method to perform operations within Azure Cosmos DB. Deploying and executing stored procedures is a vital expertise required for the Design and Implementation of Native Applications using Microsoft Azure Cosmos DB, which falls within DP-420 exam preparation. Throughout this article, we will detail the processes of writing, deploying, and calling a stored procedure.
Writing a Stored Procedure
A stored procedure is a series of JavaScript operations to be executed on the server-side, providing an execution layer between the client and the database. Upon execution, stored procedures have exclusive access to the current partition scope ensuring atomicity. Below is a basic example of how to write a stored procedure:
function sampleStoredProcedure() {
let context = getContext();
let collection = context.getCollection();
let response = context.getResponse();
let isAccepted = collection.queryDocuments(
collection.getSelfLink(),
'SELECT * FROM root r',
function(err, documents, options) {
if(err) throw err;
response.setBody('Number of documents: ' + documents.length);
}
);
if(!isAccepted) throw new Error('The query wasn\'t accepted by the server.');
}
Deploying a Stored Procedure
Stored procedures are associated with a specific collection within a database, and thus have to be deployed within that collection’s scope. Azure Cosmos DB provides APIs through which you can deploy the written stored procedure.
For deploying stored procedures, Azure Cosmos DB supports multi-master setups and implements optimistic concurrency control via etag.
Here is how you can use the JavaScript API within Azure Cosmos DB SDK to deploy the stored procedure:
const cosmosClient = new CosmosClient({endpoint, key});
const database = cosmosClient.database('
const container = database.container('
const sproc = {
id: '
body: function() {
'SELECT * FROM root r'
}
};
await container.scripts.storedProcedures.create(sproc);
Calling a Stored Procedure
Once the stored procedure has been deployed, it is executed using the executeStoredProcedure method. Below is a demonstration in JavaScript:
let result = await container.scripts
.storedProcedure('
.execute('
The executeStoredProcedure method triggers the execution of the stored procedure and you will receive an asynchronous response containing the return data of the stored procedure.
In conclusion, the process of writing, deploying, and executing stored procedures is vital to mastering Azure Cosmos DB operation. Understanding these concepts will no doubt be beneficial in preparing for the DP-420 exam, as well as in real-world application development scenarios using Azure Cosmos DB.
Practice Test
True or False: Stored procedures in Microsoft Azure Cosmos DB are similar to those in SQL Server.
- True
- False
Answer: True.
Explanation: Both SQL Server and Microsoft Azure Cosmos DB utilize stored procedures for effectively running a series of operations.
The stored procedures in Microsoft Azure Cosmos DB support which of the following programming languages?
- A. JavaScript
- B. Python
- C. Java
- D. C#
Answer: A. JavaScript.
Explanation: In Microsoft Azure Cosmos DB, stored procedures, triggers and user-defined functions are written using JavaScript.
Which of the Microsoft Azure services can be used to deploy applications?
- A. Azure DevOps
- B. Azure Machine Learning
- C. Azure Cosmos DB
- D. Azure Stream Analytics
Answer: A. Azure DevOps.
Explanation: Azure DevOps provides developer services to support teams to plan work, collaborate on code development, and build and deploy applications.
True or False: It’s not necessary to write stored procedures in Microsoft Azure Cosmos DB in the same partition key for it to be executed.
- True
- False
Answer: False.
Explanation: In Microsoft Azure Cosmos DB, stored procedures must be written in the same partition key as the items for it to be executed.
Single or Multiple Select: Which of the following are necessary to create and register a stored procedure for Microsoft Azure Cosmos DB?
- A. JavaScript code
- B. Authentication token
- C. Services SDK
- D. Database and container reference
Answer: A. JavaScript code, C. Services SDK, and D. Database and container reference.
Explanation: All are required to create and register a Stored Procedure in Microsoft Azure Cosmos DB.
True or False: Stored Procedures in Microsoft Azure Cosmos DB are always globally distributed.
- True
- False
Answer: True.
Explanation: As Microsoft Azure Cosmos DB is a globally-distributed database, stored procedures are globally distributed as well.
Which of the following methods involves a stored procedure possibility when using Microsoft Azure Cosmos DB?
- A. increase the number of request units per second (RU/s)
- B. batch multiple operations
- C. faster data ingestion
- D. use with MongoDB API
Answer: B. Batch multiple operations.
Explanation: Stored procedures in Microsoft Azure Cosmos DB allow for atomic transactions even when multiple documents get updated.
True or False: One of the advantages of stored procedures in Azure Cosmos DB is that they are portable.
- True
- False
Answer: True.
Explanation: Stored procedures are stored on the server and can be called from any client, making them portable.
Can you call a stored procedure without deploying it in Microsoft Azure Cosmos DB?
- A. Yes
- B. No
Answer: B. No
Explanation: To call a stored procedure, it must first be deployed in Microsoft Azure Cosmos DB.
If there is an exception within the Stored Procedure execution in Cosmos DB, what will be the outcome?
- A. Partial data will be updated
- B. The entire transaction will fail
- C. Stored Procedure will stop executing
- D. Exception will be logged and execution will continue
Answer: B. The entire transaction will fail
Explanation: Stored Procedures in Cosmos DB are atomic, meaning if there’s any error in execution, the entire transaction will be rolled back and no changes will be made to the database.
Interview Questions
What is a stored procedure in the context of Azure Cosmos DB?
In Azure Cosmos DB, a stored procedure is a JavaScript function that is stored and executed on the database service. Stored procedures provide better performance for client applications because they enable you to bundle multiple SQL operations into a single call to the database service.
How do you deploy a stored procedure in Azure Cosmos DB?
Stored procedures in Azure Cosmos DB are deployed using the Azure portal, Azure CLI, or SDKs. They are added directly to the desired container in the Cosmos DB.
How do you call a stored procedure in Azure Cosmos DB?
Stored procedures are called using an API provided by Azure Cosmos DB. The specific method of calling a stored procedure will depend on the SDK you are using to interact with Cosmos DB.
What benefits do stored procedures offer in Azure Cosmos DB?
Stored procedures offer several benefits including transactional execution of complex operations and reduced latency since the operations are executed directly within Cosmos DB, without network overhead.
Can you modify a stored procedure in Azure Cosmos DB once it’s deployed?
No, you cannot modify a stored procedure once it’s deployed. If you want to make changes, you have to delete the existing stored procedure and deploy a new one.
Are stored procedures in Azure Cosmos DB automatically compatible with all API models provided by Cosmos DB?
No, stored procedures are currently only available for SQL (Core) API.
What programming language is used to write stored procedures in Azure Cosmos DB?
Stored procedures in Azure Cosmos DB are written using JavaScript.
Can a stored procedure in Azure Cosmos DB operate across multiple partitions?
No, a stored procedure operates on a single logical partition. To perform database operations spanning multiple partitions, client-side logic must be implemented.
How does error handling work in Azure Cosmos DB stored procedures?
Error handling in Azure Cosmos DB stored procedures can be performed using try…catch blocks in the JavaScript code.
What is the scope of a transaction in an Azure Cosmos DB stored procedure?
The scope of a transaction in an Azure Cosmos DB stored procedure is the single stored procedure execution.
How can we monitor the performance of stored procedures in Azure Cosmos DB?
We can monitor the performance of stored procedures using Azure Monitor and the metrics provided by Azure Cosmos DB like request charge, latency, etc.
Can stored procedures in Azure Cosmos DB use the full extent of JavaScript functionality?
No, not all JavaScript functionality is supported in stored procedures. Some global JavaScript functions and features like timers, are not supported.
What happens to a stored procedure in Azure Cosmos DB during request throttling?
During request throttling, a stored procedure might fail to execute and would return a 429 error code indicating “Too many requests”.
Can stored procedures in Azure Cosmos DB call other stored procedures?
No, a stored procedure cannot call other stored procedures in Azure Cosmos DB.
Is there any time limit on the execution of stored procedures in Azure Cosmos DB?
Yes, stored procedures have an execution time limit. This limit varies depending on the service tier but is typically 5 seconds for most tiers.
extutorials.com