Cosmos DB SQL API Overview - MSDN Documentation

Azure Cosmos DB SQL API Overview

This document provides a comprehensive overview of the Azure Cosmos DB SQL API, a powerful query interface for interacting with your NoSQL data. It enables you to use familiar SQL syntax to query, insert, update, and delete data stored in Azure Cosmos DB.

Key Concepts

Data Model: Azure Cosmos DB stores data as items, which are JSON documents. These items are organized into collections (containers), and containers are organized into databases.
SQL Query Language: The SQL API allows you to leverage a subset of the Transact-SQL (T-SQL) language for querying your JSON documents. This includes familiar constructs like SELECT, FROM, WHERE, JOIN, and aggregation functions.
Indexing: Azure Cosmos DB automatically indexes all data in your containers. The SQL API benefits from this by allowing efficient querying without manual indexing configuration. You can customize indexing policies for performance tuning.
Consistency Levels: Choose from five well-defined consistency levels (Strong, Bounded Staleness, Session, Consistent Prefix, Eventual) to balance performance and data consistency for your application's needs.
Partitioning: To achieve massive scalability, Azure Cosmos DB employs horizontal partitioning. Understanding partition keys is crucial for designing performant and scalable solutions.

Core Operations with SQL API

You can perform the following operations using the SQL API:

Querying Data: Use SELECT statements to retrieve data. The query language supports filtering, sorting, projecting fields, and joining related documents.
Data Manipulation: Although primarily a query interface, the SQL API can also be used to perform basic data manipulation, though direct SDK methods are often preferred for complex updates or inserts.
Stored Procedures and Triggers: Write and execute JavaScript-based stored procedures and triggers directly within Azure Cosmos DB for server-side logic and transactional operations.

Example SQL Query

Here's a simple example of how to select all products with a price greater than 50:

SELECT VALUE p FROM products p WHERE p.price > 50

Benefits of Using the SQL API

Familiarity: If you have experience with SQL, the learning curve for the Cosmos DB SQL API is significantly reduced.
Developer Productivity: Leverage existing SQL knowledge and tools to build applications faster.
Rich Querying Capabilities: Perform complex queries, including aggregations, joins, and geospatial queries.
Performance: Optimized for performance with automatic indexing and efficient query execution.

Did you know? The Cosmos DB SQL API is schema-agnostic. It can query JSON documents with varying structures, making it highly flexible for evolving applications.

Getting Started

To start using the Cosmos DB SQL API:

Create an Azure Cosmos DB account and a SQL API database.
Create a container within your database.
Use the Azure portal, Azure CLI, PowerShell, or the Azure Cosmos DB SDKs (available for various languages like .NET, Java, Node.js, Python, and Go) to interact with your data using SQL queries.