These pairs can be used presented as rows and columns, or as a rowset view over JSON file. OPENJSON was introduced in SQL Server 2016 and is a table-valued function that parses JSON formatted text and returns objects and properties in form of key:value pairs. A final win for JSON.This article first appeared in the SQLServerGeeks Magazine. XML is able to filter out 96 rows in 200ms and JSON accomplishes the same in 9ms. If we try to find all rows that match a predicate: SELECT Id, XmlData FROM dbo.XmlVsJson2 t WHERE t.XmlData.exist('/car/make') = 1 SELECT Id, JsonData FROM dbo.XmlVsJson2 t WHERE JSON_VALUE(t.JsonData, '$.make') = 'ACURA'
(Note: I also tried adding an XML secondary index for even better performance, but I couldn’t get the query engine to use that secondary index on such a basic dataset) DROP INDEX IF EXISTS PXML_XmlData ON XmlVsJson2 CREATE PRIMARY XML INDEX PXML_XmlData ON XmlVsJson2 (XmlData) ALTER TABLE dbo.XmlVsJson2 ADD MakeComputed AS JSON_VALUE(JsonData, '$.make') CREATE NONCLUSTERED INDEX IX_JsonData ON dbo.XmlVsJson2 (MakeComputed)
The XML datatype in SQL Server has its own types of indexes, while JSON simply needs a computed column with a regular index applied to it. Now that we have our expanded data in our table, let’s add some indexes.
Sql server json compare full#
(once again, full dataset at GitHub if you are playing along at home) Here’s what it looks like once I loaded it into a table called dbo.XmlVsJson: CREATE TABLE dbo.XmlVsJson ( Id INT IDENTITY PRIMARY KEY, XmlData XML, JsonData NVARCHAR(MAX) ) The test data I’m using is vehicle year/make/model data from. SQL Server’s JSON function signatures are easier to remember and cleaner to write on screen. So let’s run some test queries! Is JSON SQL Server’s New Sheriff in Town?Īlthough performance is the final decider in these comparison tests, I think JSON has a head start over XML purely in terms of usability. SQL Server’s implementation of XML does have some nice features like a dedicated datatype that reduces storage space and validates syntax, but I find the querying of XML to be clumsy.Īll XML grievances aside, I am still willing to use XML if it outperforms JSON. XML is too wordy (lots of characters wasted on closing tags), it has elements AND attributes (I don’t like having to program for two different scenarios), and depending on what language you are programming in, sometimes you need schema files and sometimes you don’t. Enter XML, SQL’s Bad Hombreįull disclosure: I don’t love XML and I also don’t love SQL Server’s implementation of it. Today I want to pit JSON against XML and see which is the better format to use in SQL Server. Although the implementation is not perfect, I am still a huge fan.Įven if a new feature like JSON support is awesome, I am only likely to use it if it is practical and performs better than the alternatives. Starting with the 2016 release, SQL Server offers native JSON support. Ĭome see me speak about JSON and XML at SQL Saturday Columbus on July 22 ! Additional performance comparisons available in an updated post.
0 kommentar(er)
