My job as a developer evangelist for Couchbase means than I work much less with SQL Server than I used to. However, it doesn't mean I don't keep up with it.

In fact, inspired by Couchbase, I decided to try out using SQL Server as a sort of document data store. SQL Server 2016 introduced some interesting new features that make this kinda possible: JSON_VALUE, JSON_QUERY, and JSON_MODIFY.

I set up a "document" table, which is two fields: a guid and an nvarchar(max). This is kinda like a Couchbase bucket: a key and a JSON document to go with it.

I put fairly complex hierarchical documents in these fields, something like:

I say "fairly complex", because representing this in a standard normalized fashion would require at least two tables, foreign keys, constraints, and then data migration and schema migration as the model evolves.

Now, suppose I want to execute a query and find all the document that are "Show=true". With Couchbase and N1QL, I would just use something like "SELECT * FROM `bucket` WHERE show = true".

With SQL Server, there's a little more work. "SELECT t.key, t.doc FROM [table] t WHERE JSON_VALUE(t.SpeakingInfo,'$.Show') = 'true'". Notice that JSON_VALUE is being applied to a text field, and a JSON path is used within JSON_VALUE to get a specific value from within that JSON object.

For this simple project I'm doing, that's all I need. No idea yet what kind of performance level I can expect from JSON_VALUE and more complex JSON paths.

But, this is definitely an example of the kinds of database convergence I've been telling people about. The separations between NoSQL and SQL are becoming less strict, at least in terms of data modeling and querying.

You need to be using SQL Server 2016 or SQL Server Azure to take advantage of the JSON_* functions.

Couchbase Day is a day-long FREE event where you can participate in hands-on workshops and technical presentations. If you've never used NoSQL databases before, or you're interested in Couchbase Server in particular, this is the event for you.

Couchbase Day is coming to Columbus, Ohio on October 27, 2016.

Click that link for more details, including the technical prerequisites that you will use during the workshops.

Sign up today, and I'll see you there!

I was recently on David Giard's show Technology and Friends! This is my second time on his show. This time I got to talk about NoSQL and Couchbase Server in particular. Check it out, and check out Couchbase Server.