should we use json or xml in sqlserver.

Question

Post reply

should we use json or xml in sqlserver.

rajemessage 14195

Hall of Fame

Points: 3301
More actions
August 6, 2016 at 4:52 am

#400532

hi,
in sql server 2016 json has been introduced, i wanted to know
, should we change to jsaon and leave xml.
because we are keeping configuration in xml in one column.
or in other words what will be the feuture of xml?
yours sincerely

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

Answer withdrawn because I prefer not to answer interview questions.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1003933 More actions · Answer 2

rajemessage 14195 (8/6/2016)
hi,
in sql server 2016 json has been introduced, i wanted to know
, should we change to jsaon and leave xml.
because we are keeping configuration in xml in one column.
or in other words what will be the feuture of xml?
yours sincerely

Interview question of not, so far as I'm concerned, both are mostly stupid choices. The data should be shredded and properly normalized in tables instead of violating the first rule of normalization.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182889 More actions · Answer 3

rajemessage 14195 (8/6/2016)
hi,
in sql server 2016 json has been introduced, i wanted to know
, should we change to jsaon and leave xml.
because we are keeping configuration in xml in one column.
or in other words what will be the feuture of xml?
yours sincerely

Hardly makes sense to change from XML to Json if it's already there and working, it makes sense to use Json if you are interacting with an application that uses it.

XML is easier for humans to read and manipulate, JSon is slightly harder on that part but slimmer (less bloated) and should be easier for the computer. Haven't done any performance comparisons yet but from what I've seen there is hardly any difference between the two.

I don't think you should keep the configuration as an XML, use proper data structure and generate the XML on the retrieval.

😎

Eirikur Eiriksson SSC Guru Points: 182889 More actions · Answer 4

The Dixie Flatline (8/6/2016)
Answer withdrawn because I prefer not to answer interview questions.

Just curious, how do you determine that this is an interview question?

😎

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

rajemessage 14195 (8/6/2016)
should we change to jsaon and leave xml.

No, you should in most cases go for a properly designed normalised data structure.

Otherwise, whether you use json or xml depends on what that unstructured data is used by.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 6

Eirikur Eiriksson (8/6/2016)
The Dixie Flatline (8/6/2016)
Answer withdrawn because I prefer not to answer interview questions.
Just curious, how do you determine that this is an interview question?
😎

I don't know it for a fact.

But, I do know that most people post a question and wait for an answer.

He posted three questions in 30 minutes.

1. XML vs JSON.

2. What to do about NOLOCK since a current syntax is deprecated.

3. Serial vs Read Committed

Taken together this reads very much like someone is testing the depth of his SQL knowledge. I answered his first two questions but then hit the third one and thought about the content and the time frame.

If I'm wrong he has my apology. If I'm right, he still got the same answers from you all.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1003933 More actions · Answer 7

The Dixie Flatline (8/6/2016)
Eirikur Eiriksson (8/6/2016)
The Dixie Flatline (8/6/2016)
Answer withdrawn because I prefer not to answer interview questions.
Just curious, how do you determine that this is an interview question?
😎
I don't know it for a fact.
But, I do know that most people post a question and wait for an answer.
He posted three questions in 30 minutes.
1. XML vs JSON.
2. What to do about NOLOCK since a current syntax is deprecated.
3. Serial vs Read Committed
Taken together this reads very much like someone is testing the depth of his SQL knowledge. I answered his first two questions but then hit the third one and thought about the content and the time frame.
If I'm wrong he has my apology. If I'm right, he still got the same answers from you all.

Nah... you're probably right. It's a fairly typical pattern. Even if they're not interview questions, it's a carpet bombing of questions that someone could probably get the answers to if they spent a little time doing their own work. For example (and I'll admit I've not looked it up in the last 2 months or so), I'm pretty sure that the use of NOLOCK hasn't actually been deprecated. Rather, the use of NOLOCK on places where it has no effect and was previously ignored (on the table that's the target of an UPDATE, for example) has been "deprecated" and will cause errors in the future. It's well documented and I didn't answer that one for that reason.

The Serial vs Read Committed stuff is also well documented and so I didn't go there, either.

This JSON vs XML crap is documented the wrong way. Other than storing either in a staging table for purposes of shredding on receipt, it shouldn't be stored in a database. It violates 1st normal form, causes unnecessary bloating everywhere from transmission to backups, has virtually no methods of integrity or uniqueness checking, and causes a wealth of anguish and performance problems. I couldn't let that one go. 😉 Even Gail weighed in on that one.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Jeff Moden (8/7/2016)
For example (and I'll admit I've not looked it up in the last 2 months or so), I'm pretty sure that the use of NOLOCK hasn't actually been deprecated.

Nope. What is deprecated, and has been for sometime, is specifying table hints without using WITH.

FROM table1 (NOLOCK)

vs

FROM table1 WITH (NOLOCK)

Even Gail weighed in on that one.

I weighed in on all, but no answers because none of them had a straightforward answer.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003933 More actions · Answer 9

GilaMonster (8/7/2016)
Jeff Moden (8/7/2016)
For example (and I'll admit I've not looked it up in the last 2 months or so), I'm pretty sure that the use of NOLOCK hasn't actually been deprecated.
Nope. What is deprecated, and has been for sometime, is specifying table hints without using WITH.
FROM table1 (NOLOCK)
vs
FROM table1 WITH (NOLOCK)

Yep... knew that one and you're right... that's been out for a very long time. Since 2005 IIRC. I, apparently, misread what the OP stated. It sounded to me like he was saying that NOLOCK (including the proper use of WITH(NOLOCK) was going away. The hint (in its proper form) isn't what's going away. Using the naked hint (just NOLOCK without the WITH) is what's going away.

What I was referring to is the following deprecation...

Specifying NOLOCK or READUNCOMMITTED in the FROM clause of an UPDATE or DELETE statement.

You can find that by searching the following page for the word "NOLOCK".

https://msdn.microsoft.com/en-us/library/ms143729.aspx

Notice that they didn't include WITH on that either but I believe they mean any use of the NOLOCK hint, proper or not.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 10

Sorry for the drama guys. Totally my fault.

I vaguely remember a similar debate about this many years ago. The purpose of these forums are to put out good information and, as Eirik pointed out there is no way to be sure for what purpose an OP is looking for answers. So all questions get answered.

Although the choice to help or not help an OP is a personal one, I should have just edited my answers to blanks without getting on a high horse. My bad.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1003933 More actions · Answer 11

The Dixie Flatline (8/7/2016)
Sorry for the drama guys. Totally my fault.
I vaguely remember a similar debate about this many years ago. The purpose of these forums are to put out good information and, as Eirik pointed out there is no way to be sure for what purpose an OP is looking for answers. So all questions get answered.
Although the choice to help or not help an OP is a personal one, I should have just edited my answers to blanks without getting on a high horse. My bad.

You've really got nothing to apologize for although part of what makes you a great person is the fact that you did. 😉

Now, back to biting my lip on some of the posts. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)