Azure SQL Database 的多模型功能Multi-model capabilities of Azure SQL Database

多模型資料庫可讓您儲存及處理以多種資料格式 (例如關聯式資料、圖形、JSON/XML 文件、索引鍵/值組) 表現的資料。Multi-model databases enable you to store and work with data represented in multiple data formats such as relational data, graphs, JSON/XML documents, key-value pairs, etc.

使用多模型功能的時機When to use multi-model capabilities

Azure SQL Database 的設計訴求是要使用關聯式模型,在各種一般用途應用程式的大部分情況下達到最佳效能。Azure SQL Database is designed to work with the relational model that provides the best performance in the most of the cases for a variety of general-purpose applications. 不過,Azure SQL Database 不限於關聯式資料。However, Azure SQL Database is not limited to relational-data only. Azure SQL Database 可讓您使用緊密整合到關聯式模型中的各種非關聯式格式。Azure SQL Database enables you to use a variety of non-relational formats that are tightly integrated into the relational model. 在下列情況下, 您應該考慮使用 Azure SQL Database 的多模型功能:You should consider using multi-model capabilities of Azure SQL Database in the following cases:

  • 您有一些較適合 NoSQL 模型的資訊或結構, 而且您不想要使用個別的 NoSQL 資料庫。You have some information or structures that are better fit for NoSQL models and you don't want to use separate NoSQL database.
  • 大部分的資料都適用于關聯式模型, 而您需要以 NoSQL 樣式為數據的某些部分建立模型。A majority of your data is suitable for relational model, and you need to model some parts of your data in NoSQL style.
  • 您想要利用豐富的 Transact-sql 語言來查詢和分析關聯式和 NoSQL 資料, 並將其與可使用 SQL 語言的各種工具和應用程式整合。You want to leverage rich Transact-SQL language to query and analyze both relational and NoSQL data, and integrate it with a variety of tools and applications that can use SQL language.
  • 您想要套用如記憶體內部技術的資料庫功能, 以改善 NoSQL 資料 strucutres 的分析或處理效能, 請使用異動複寫可讀取的複本來建立資料的複本另一個地方, 然後從主資料庫卸載一些分析工作負載。You want to apply database features such as in-memory technologies to improve performance of your analytic or processing of your NoSQL data strucutres, use transactional replication or readable replicas to create copy of your data on the other place and offload soem analytic workloads from the primary database.

總覽Overview

Azure SQL 提供下列多模型功能:Azure SQL provides the following multi-model features:

  • 圖形功能可讓您以一組節點和邊緣的形式表現資料,並使用以 MATCH 運算子增強的標準 Transact-SQL 查詢來查詢圖形資料。Graph features enable you to represent your data as set of nodes and edges, and use standard Transact-SQL queries enhanced with graph MATCH operator to query the graph data.

  • JSON 功能可讓您將 JSON 文件放在資料表、將關聯式資料轉換為 JSON 文件,反之亦然。JSON features enable you to put JSON documents in tables, transform relational data to JSON documents and vice versa. 您可以使用以 JSON 函式增強的標準 Transact-SQL 語言來剖析文件,並使用非叢集式索引、資料行存放區索引或記憶體最佳化的資料表,將查詢最佳化。You can use the standard Transact-SQL language enhanced with JSON functions for parsing documents, and use non clustered indexes, columnstore indexes, or memory-optimized tables, to optimize your queries.

  • 空間功能可讓您儲存地理和幾何資料、使用空間索引來編製其索引,以及使用空間查詢來擷取資料。Spatial features enables you to store geographical and geometrical data, index them using the spatial indexes, and retrieve the data using spatial queries.

  • XML 功能可讓您在資料庫中儲存 XML 資料及編製其索引,並使用原生 XQuery/XPath 作業來處理 XML 資料。XML features enable you to store and index XML data in your database and use native XQuery/XPath operations to work with XML data. Azure SQL 資料庫有專門處理 XML 資料的內建 XML 查詢引擎。Azure SQL database has specialized built-in XML query engine that process XML data.

  • 索引鍵/值組並未明確獲得支援,因為可以原生方式將索引鍵/值組模型化為雙資料行的資料表。Key-value pairs are not explicitly supported as special features since key-value paris can be natively modeled as two-column tables.

    注意

    您可以在相同的 Transact-SQL 查詢中使用 JSON 路徑運算式、XQuery/XPath 運算式、空間函式和圖形查詢運算式,以存取您儲存在資料庫中的任何資料。You can use JSON Path expression, XQuery/XPath expressions, spatial functions, and graph-query expressions in the same Transact-SQL query to access any data that you stored in the database. 此外,任何執行 Transact-SQL 查詢的工具或程式設計語言,也可以使用該查詢介面來存取多模型資料。Also, any tool or programming language that can execute Transact-SQL queries, can also use that query interface to access multi-model data. 相較於多模型資料庫 (例如可針對不同資料模型提供特製化 API 的 Azure Cosmos DB),這是主要差異。This is the key difference compared to the multi-model databases such as Azure Cosmos DB that provides specialized API for different data models.

在下列各節中,您可以了解 Azure SQL Database 的最重要多模型功能。In the following sections, you can learn about the most important multi-model capabilities of Azures SQL Database.

Graph 功能Graph features

Azure SQL Database 會提供圖形資料庫功能,以將資料庫中的多對多關聯性模型化。Azure SQL Database offers graph database capabilities to model many-to-many relationships in database. 圖形是節點 (或頂點) 和邊緣 (或關聯性) 的集合。A graph is a collection of nodes (or vertices) and edges (or relationships). 節點代表實體 (例如,個人或組織),而邊緣代表其所連接的兩個節點之間的關聯性 (例如,按讚數或朋友)。A node represents an entity (for example, a person or an organization) and an edge represents a relationship between the two nodes that it connects (for example, likes or friends). 以下是讓圖表資料庫變成唯一的一些功能:Here are some features that make a graph database unique:

  • 邊緣或關聯性是圖形資料庫中的第一級實體,可以有與其相關聯的屬性。Edges or relationships are first class entities in a Graph Database and can have attributes or properties associated with them.
  • 單一邊緣可以彈性地連接圖形資料庫中的多個節點。A single edge can flexibly connect multiple nodes in a Graph Database.
  • 您可以輕鬆地表達模式比對和多躍點導覽查詢。You can express pattern matching and multi-hop navigation queries easily.
  • 您可以輕鬆地表達遞移閉包和多型查詢。You can express transitive closure and polymorphic queries easily.

圖形關聯性和圖形查詢功能都已整合到 Transact-SQL 中,並享有使用 SQL Server 作為基礎資料庫管理系統的優點。The graph relationships and graph query capabilities are integrated into Transact-SQL and receive the benefits of using SQL Server as the foundational database management system. 圖形處理是核心 SQL Server 資料庫引擎功能,可讓您可找到關於圖形處理的詳細資訊。Graph processing is the core SQL Server Database Engine feature, so you can find more info about the Graph processing there.

使用圖形功能的時機When to use a graph capability

使用關聯式資料庫可以達成圖形資料庫所能達成的一切。There is nothing a graph database can achieve, which cannot be achieved using a relational database. 不過,圖形資料庫可以輕鬆地表達某些查詢。However, a graph database can make it easier to express certain queries. 您可以根據下列因素來決定選擇哪一種:Your decision to choose one over the other can be based on following factors:

  • 一個節點可有多個父代的模型階層式資料,因此不能使用 HieararchyIdModel hierarchical data where one node can have multiple parents, so HierarchyId cannot be used
  • 您的應用程式具有複雜多對多關聯性的模型;隨著應用程式發展,會加入新的關聯性。Model has Your application has complex many-to-many relationships; as application evolves, new relationships are added.
  • 您需要分析互連的資料和關聯性。You need to analyze interconnected data and relationships.

JSON 功能JSON features

Azure SQL Database 可讓您剖析及查詢以「JavaScript 物件標記法」 (JSON) 格式表示的資料,然後將您的關聯式資料匯出成 JSON 文字。Azure SQL Database lets you parse and query data represented in JavaScript Object Notation (JSON) format, and export your relational data as JSON text.

JSON 是一種用於在新式的 Web 與行動應用程式中交換資料的常用資料格式。JSON is a popular data format used for exchanging data in modern web and mobile applications. JSON 也用於將半結構化的資料儲存在記錄檔或 NoSQL 資料庫 (例如 Azure Cosmos DB) 中。JSON is also used for storing semi-structured data in log files or in NoSQL databases like Azure Cosmos DB. 許多 REST Web 服務都會傳回採用 JSON 文字格式的結果,或是接受採用 JSON 格式的資料。Many REST web services return results formatted as JSON text or accept data formatted as JSON. 大多數 Azure 服務 (例如 Azure 搜尋服務Azure 儲存體Azure Cosmos DB) 都有會傳回或取用 JSON 的 REST 端點。Most Azure services such as Azure Search, Azure Storage, and Azure Cosmos DB have REST endpoints that return or consume JSON.

Azure SQL Database 可讓您輕鬆使用 JSON 資料,並將資料庫與新式服務整合。Azure SQL Database lets you work with JSON data easily and integrate your database with modern services. Azure SQL Database 提供下列可與 JSON 資料搭配使用的函數:Azure SQL Database provides the following functions for working with JSON data:

JSON 函數

如果您有 JSON 文字,您可以透過使用內建的函式 JSON_VALUEJSON_QUERYISJSON,從 JSON 擷取資料或確認 JSON 的格式是否正確。If you have JSON text, you can extract data from JSON or verify that JSON is properly formatted by using the built-in functions JSON_VALUE, JSON_QUERY, and ISJSON. JSON_MODIFY 函式可讓您更新 JSON 文字內的值。The JSON_MODIFY function lets you update value inside JSON text. 針對更進階的查詢和分析, OPENJSON 函數可以將 JSON 物件陣列轉換成一組資料列。For more advanced querying and analysis, OPENJSON function can transform an array of JSON objects into a set of rows. 您可以在傳回的結果集上執行任何 SQL 查詢。Any SQL query can be executed on the returned result set. 最後,還有 FOR JSON 子句,此子句可讓您將儲存在關聯式資料表中的資料格式化為 JSON 文字。Finally, there is a FOR JSON clause that lets you format data stored in your relational tables as JSON text.

如需詳細資訊,請參閱如何在 Azure SQL Database 中處理 JSON 資料For more information, see How to work with JSON data in azure SQL Database. JSON 是核心 SQL Server 資料庫引擎功能,可讓您可找到關於 JSON 功能的詳細資訊。JSON is core SQL Server Database Engine feature, so you can find more info about the JSON feature there.

使用 JSON 功能的時機When to use a JSON capability

在某些特定情況下,可以使用文件模型,而不是關聯式模型:Document models can be used instead of the relational models in some specific scenarios:

  • 結構描述的高度正規化並不會帶來重大優點,因為您可一次存取物件的所有欄位,或您永遠不會更新物件的正規化組件。High-normalization of schema doesn't bring significant benefits because you access the all fields of objects at once, or you never update normalized parts of the objects. 不過,正規化模型會增加查詢的複雜度,因為您需要聯結大量資料表才能取得資料。However, the normalized model increases the complexity of your queries due to the large number of tables that you need to join to get the data.
  • 您正在使用以原生方式使用 JSON 文件的應用程式屬於通訊或資料模型,而且您不想引進可將關聯式資料轉換為 JSON (反之亦然) 的額外藏次。You are working with the applications that natively use JSON documents are communication or data models, and you don't want to introduce additional layers that transforms relational data to JSON and vice versa.
  • 您需要將子系資料表或實體物件值模式反正規化,以簡化您的資料模型。You need to simplify your data model by de-normalizing child tables or Entity-Object-Value patterns.
  • 您需要載入或匯出以 JSON 格式儲存的資料,而不需一些可剖析資料的其他工具。You need to load or export data stored in JSON format without some additional tool that parses the data.

空間功能Spatial features

空間資料代表幾何物件的實體位置和圖形相關資訊。Spatial data represents information about the physical location and shape of geometric objects. 這些物件可以是點位置或更複雜的物件, 例如國家/地區、道路或 lake。These objects can be point locations or more complex objects such as countries/regions, roads, or lakes.

Azure SQL Database 支援兩種空間資料類型 - 幾何資料類型和地理資料類型。Azure SQL Database supports two spatial data types - the geometry data type and the geography data type.

  • 幾何類型代表歐氏 (平面) 座標系統中的資料。The geometry type represents data in a Euclidean (flat) coordinate system.
  • 地理類型代表球形地球座標系統中的資料。The geography type represents data in a round-earth coordinate system.

有許多可使用於 Azure SQL 資料庫中的空間物件,例如 PointLineStringPolygon 等。There is a number of Spatial objects that can be used in Azure SQL database such as Point, LineString, Polygon, etc.

Azure SQL Database 也會提供特製化空間索引,以便改善空間查詢的效能。Azure SQL Database also provides specialized Spatial indexes that can be used to improve performance of your spatial queries.

空間支援是核心 SQL Server 資料庫引擎功能,可讓您可找到關於空間功能的詳細資訊。Spatial support is core SQL Server Database Engine feature, so you can find more info about the spatial feature there.

XML 功能XML features

SQL Server 提供強大的平台,可供開發豐富的應用程式來進行半結構化資料管理。SQL Server provides a powerful platform for developing rich applications for semi-structured data management. XML 支援已整合到 SQL Server 中的所有元件,包括下列各項:Support for XML is integrated into all the components in SQL Server and includes the following:

  • Xml 資料類型。The xml data type. XML 值可以原生方式儲存在 xml 資料類型資料行中,其可依照 XML 結構描述的集合設定類型或維持不具類型。XML values can be stored natively in an xml data type column that can be typed according to a collection of XML schemas, or left untyped. 您可以編製 XML 資料行的索引。You can index the XML column.
  • 針對資料行中儲存的 XML 資料和 xml 類型的變數指定 XQuery 查詢的能力。The ability to specify an XQuery query against XML data stored in columns and variables of the xml type. XQuery 功能可使用於任何 Transact-SQL 查詢,以存取您在資料庫中使用的任何資料模型。XQuery functionalities can be used in any Transact-SQL query that access any data model that you use in your database.
  • 使用主要 XML 索引,自動為 XML 文件中的所有元素編製索引,或使用次要 XML 索引,指定應該編製索引的確切路徑。Automatically index all elements in XML documents using primary XML index or specify the exact paths that should be indexed using secondary XML index.
  • OPENROWSET 允許大量載入 XML 資料。OPENROWSET that allows bulk loading of XML data.
  • 將關聯式資料轉換為 XML 格式。Transform relational data to XML format.

XML 是核心 SQL Server 資料庫引擎功能,可讓您可找到關於 XML 功能的詳細資訊。XML is core SQL Server Database Engine feature, so you can find more info about the XML feature there.

使用 XML 功能的時機When to use an XML capability

在某些特定情況下,可以使用文件模型,而不是關聯式模型:Document models can be used instead of the relational models in some specific scenarios:

  • 結構描述的高度正規化並不會帶來重大優點,因為您可一次存取物件的所有欄位,或您永遠不會更新物件的正規化組件。High-normalization of schema doesn't bring significant benefits because you access the all fields of objects at once, or you never update normalized parts of the objects. 不過,正規化模型會增加查詢的複雜度,因為您需要聯結大量資料表才能取得資料。However, the normalized model increases the complexity of your queries due to the large number of tables that you need to join to get the data.
  • 您正在使用以原生方式使用 XML 文件的應用程式屬於通訊或資料模型,而且您不想引進可將關聯式資料轉換為 XML (反之亦然) 的額外藏次。You are working with the applications that natively use XML documents are communication or data models, and you don't want to introduce additional layers that transforms relational data to XML and vice versa.
  • 您需要將子系資料表或實體物件值模式反正規化,以簡化您的資料模型。You need to simplify your data model by de-normalizing child tables or Entity-Object-Value patterns.
  • 您需要載入或匯出以 XML 格式儲存的資料,而不需一些可剖析資料的其他工具。You need to load or export data stored in XML format without some additional tool that parses the data.

索引鍵/值組Key-value pairs

Azure SQL Database 沒有可支援索引鍵/值組的特製化類型或結構,因為索引鍵/值結構可以原生方式表現為標準關聯式資料表:Azure SQL Database don't have specialized types or structures that support key-value pairs since key-value structures can be natively represented as standard relational tables:

CREATE TABLE Collection (
  Id int identity primary key,
  Data nvarchar(max)
)

您可自訂此索引鍵/值結構以符合您的需求,沒有任何限制。You can customize this key-value structure to fit your needs without any constraints. 例如,此值可以是 XML 文件,而不是 nvarchar(max) 類型,如果值為 JSON 文件,您可以放置 CHECK 條件約束來確認 JSON 內容的有效性。As an example, the value can be XML document instead of nvarchar(max) type, if the value is JSON document, you can put CHECK constraint that verifies the validity of JSON content. 您可以在額外的資料行中放置某個索引鍵的任意多個相關值、新增計算的資料行和索引以簡化和最佳化資料存取、將資料表定義為僅限記憶體/最佳化結構描述的資料表以取得更佳效能等等。You can put any number of values related to one key in the additional columns, add computed columns and indexes to simplify and optimize data access, define the table as memory/optimized schema-only table to get better performance, etc.

請參閱 BWin 如何使用記憶體內部 OLTP 來達到前所未有的效能和規模中達成每秒快取 1.200.000 個批次的 ASP.NET 快取解決方案,作為關聯式模型如何才能有效地實際作為索引鍵/值組解決方案的範例。See how BWin is using In-Memory OLTP to achieve unprecedented performance and scale for their ASP.NET caching solution that achieved 1.200.000 batches per seconds, as an example how relational model can be effectively used as key-value pair solution in practice.

後續步驟Next steps

Azure SQL Database 中的多模型功能也是 Azure SQL Database 與 SQL Server 之間共用的核心 SQL Server 資料庫引擎功能。Multi-model capabilities in Azure SQL Databases are also the core SQL Server Database Engine features that are shared between Azure SQL Database and SQL Server. 若要了解這些功能的詳細資訊,請瀏覽 SQL 關聯式資料庫文件頁面:To learn more details about these features, visit the SQL Relational database documentation pages: