Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link: http://hub-nl.insight.com/i/692679

Contents of this Issue

Navigation

Page 98 of 212

86 C H A P T E R 5 | Broader data access Example 5-24: Using the JSON_MODIFY function UPDATE Production.Product SET Reviews = JSON_MODIFY(Reviews, '$.Rating', 4) WHERE ProductID = 709 Note You can also use the JSON_MODIFY function to rename or delete a property or to append a new value to an array. For more information, see "JSON_MODIFY (Transact-SQL)" at https://msdn.microsoft.com/en-us/library/dn921892.aspx. Lax mode versus strict mode When you use any of the built-in JSON functions, you can use lax mode or strict mode. By default, a function runs in lax mode, which means it returns a null value if it encounters an error. For example, a function returns null if you request a property such as $.Reviewer.Address and the property does not exist. In strict mode, the function returns an error. You declare the mode explicitly by including the lax or strict keyword at the beginning of the path expression, like this: lax$.Reviewer.Address or strict$.Reviewer.Address. Indexing JSON data If you store JSON in a relational table, you might need to filter or sort by a property in the JSON data. For better performance, you might need an index. However, you cannot directly reference a JSON property in an index. Instead, you create a computed column and then create an index for that column, as shown in Example 5-25. The expression you use in the computed column must match the expression that queries use. When a query executes with this expression, the database engine recognizes the equivalent computed column and applies its index when possible. Because this new column is computed only when you build or rebuild the index, you do not incur any additional storage costs for the computed column. Example 5-25: Index a JSON property as a computed column ALTER TABLE Production.Product ADD vReviewer AS JSON_VALUE(Reviews, '$.Reviewer.Name'); CREATE INDEX idx_Production_Product_Reviewer_Name ON Production.Product(vReviewer); Note If queries often return other columns from the same table, you can also use the INCLUDE argument when you create an index in order to add additional columns to the index as a further optimization. To learn more about indexing JSON data, see "Index JSON Data" at https://msdn.microsoft.com/en-us/library/mt612798.aspx.

Articles in this issue

Links on this page

Archives of this issue

view archives of Netherlands: Software - Introductie van Micorosoft SQL Server 2016