Storing JSON in an SQL Server database?

Question

I'm developing a form generator, and wondering if it would be bad mojo to store JSON in an SQL Server database?

I want to keep my database & tables simple, so I was going to have

`pKey, formTitle, formJSON`

on a table, and then store

{["firstName":{"required":"true","type":"text"},"lastName":{"required":"true","type":"text"}}

in formJSON. would this slow down the DB server too much to set live?

Any input is appreciated.

Answer 1

It will be slower than having the form defined in code, but one extra query shouldn't cause you much harm. (Just don't let 1 extra query become 10 extra queries!)

Edit: If you are selecting the row by formTitle instead of pKey (I would, because then your code will be more readable), put an index on formTitle

Answer 2

I wouldn't recommend it.

If you ever want to do any reporting or query based on these values in the future it's going to make your life a lot harder than having a few extra tables/columns.

Why are you avoiding making new tables? I say if your application requires them go ahead and add them in... Also if someone has to go through your code/db later it's probably going to be harder for them to figure out what you had going on (depending on what kind of documentation you have).

Answer 3

fast way to shred json using clr

http://www.sql-library.com/?p=140