JSON, SQL and NoSQL

Over the last year, I’ve been presenting on CosmosDB at SQL Saturday’s and various user groups.

This presentation has morphed somewhat over time to now be more of a comparison between functionality in CosmosDB and functionality in SQL Server -and a discussion of where one technology may be more relevant than the other.

In case you’re not aware of CosmosDB it’s Microsoft’s cloud based, planet scale, fully managed NoSQL (that’s Not only SQL) database – and it’s heavily JSON based.

While a lot of the knowledge gained as a SQL Server professional will be relevant to the world of Cosmos DB – there are elements that can lead the SQL Server professional down the wrong path and cause a substantial degree of pain at a later date.

However, lately, I’ve took to diving into the comparison with what I think is a good starting point – and that’s JavaScript Object Notation or JSON.

It’s kind of amazed me how many SQL professionals have turned up for my presentation and have not explored JSON at all – sure, they are aware of what it is, but the mechanics of it are mostly left as an exercise ‘to be explored’ at a later date.

That ‘later date’ maybe when some sort of data transfer has to done involving SSIS, ADF (v2), CosmosDB or some other technology or situation that lends itself well to JSON formats.

One of those uses – I’ve found – is setting up tables with configuration information -sure, there are lots of ways to achieve this, but the JSON format is one of the easier – and best – that I’ve found to date.

SQL Server 2016 introduced JSON support within the database and this support is in every edition of SQL Server from SQL Server 2016 forward.

One myth that I see regularly is that the JSON support in SQL Server may be analogous to the XML support offered by the same product. While, there are some comparisons, I’ve personally found dealing with JSON in SQL Server much more straightforward than dealing with XML – and, if you have never dealt with JSON in SQL Server before then you might be surprised how flexible it it.

For that reason, I’ve decided to invest some time in producing some posts that are specifically dedicated to the JSON functionality in SQL Server, while there’s a lot of good stuff already out there – and I’ll reference this when it’s relevant – one of my goals will be to build on each post to eventually explore many uses and potentials of JSON and the opportunites that it offers for SQL Server professionals.

I hope you enjoy it and I’d love to get feedback from you of any variety.

Have a great day

Cheers

Marty

Generating Insert Statements

G’day,

I been asked a few times lately the best way to generate INSERT statements.

I’d always replied to use SSMS.

I know SSMS has this feature, it’s just not something that I have the need to use on a regular basic. And – as we all know – when you don’t use something regularly you tend to forget where to find it, or – as happens reasonably regularly nowadays – the feature, or tool, gets an update and you find yourself re-learning something you thought that you were already familiar with.

Fortunately, the feature to generate INSERT statements was pretty much where I expected – but it did take a few minutes to find the exact spot, so I thought that I’d mention it here – just in case anybody else is looking too, or you just need a reminder that SSMS is very capable of performing this task.

Simply right click on the database that contains the table – or tables – that you need to generate INSERT statements for and select TASKS

Select TASKS

From the resulting menu choose GENERATE SCRIPTS

Select Generate Scripts

Then it’s just a simply case of following the Wizard through.

If you’ve not started this Wizard before then you’ll see the Introduction page – you can safely tick “Don’t show this page again”, so you basically don’t see this page again the next time you start the same Wizard.

You probably don’t want to see this page every time you start the Wizard.

Then, on the next page, either select either all objects to script – or just the specific one(s) that you want. In my case I’m interested in one particular table.

Select the object(s) that you want to script.

On the next page select the location where you want the file to go. I’m selecting a new query editor window, but you could select a file location or the clipboard.

Then hit the “Advanced” button

Selecting the Advanced button is important

Clicking the Advanced button will show the “Advanced Scripting Options” – scroll down until you find “Types of data to script” and select your choice from the drop down. I’ve chose to script “Schema and data” – but you can also select just Data or only schema.

Select the option that best suits your need.

The next page will give you a summary

Hit Next on the summary page

The it’s just a case of hitting Next and then your script is generated. Simply hit finish on the next screen

You’ll see a summary and hit Finish

And you should see your script. Save it to a location of your choice.

Finally – you see your nice INSERT script

I hope this was useful.

Have a great day

Cheers

Marty.