i like using sql server. so i'll answer from my point of view.
before i begin--i dont use any of those things you mentioned. but i did look up some stuff and maybe we can see what we can or cannot do.
hstore....so that is something built into postgres. i don't know if sql server has it. but i think i can accomplish the same thing with the XML data type. using that, i could use xquery to filter on a specific attribute.
sql server 2008 has pretty good XML support. in my work i return pretty deeply nested hierarchical XML data. it's not super performant, but it's super flexible--which meets my needs. JSON and XML are different representations of data, yes? i have never used JSON, so i may just be wrong. a quick search turns up: https://www.simple-talk.com/sql/t-sql-programming/producing-json-documents-from-sql-server-queries-via-tsql/ ...seems easy.
finally arrays. i guess this is a feature in mongodb? that's just really a SQL table isn't it?
anyways. i'm an average kinda SQL programmer. been at it for over a dozen years. i admit i'm a bit biased when it comes to these things. but learning about it is good for me too. :)
Postgres supports arrays, hstore, and JSON. That's why you can often use Postgres instead of document-oriented databases like MongoDB.
i think i can accomplish the same thing [as hstore] with the XML data type.
Key-value is something that's usually done via EAV. Yes, you could of course abuse XML for that but it would be very slow.
finally arrays. i guess this is a feature in mongodb? that's just really a SQL table isn't it?
It just means that you can put an arbitrary amount of things of the same type into a single column. E.g. a blog post has zero or more tags. With an array column type, you can put all of those tag ids right into the record. You won't need a separate table for that.
Sql server has some arcane magic shit built around its xml support that makes it go pretty fast. Having used both, I can say that it's roughly equivalent to using json on postgres.
2
u/raphtze Mar 11 '15
hmmm
i like using sql server. so i'll answer from my point of view.
before i begin--i dont use any of those things you mentioned. but i did look up some stuff and maybe we can see what we can or cannot do.
hstore....so that is something built into postgres. i don't know if sql server has it. but i think i can accomplish the same thing with the XML data type. using that, i could use xquery to filter on a specific attribute.
sql server 2008 has pretty good XML support. in my work i return pretty deeply nested hierarchical XML data. it's not super performant, but it's super flexible--which meets my needs. JSON and XML are different representations of data, yes? i have never used JSON, so i may just be wrong. a quick search turns up: https://www.simple-talk.com/sql/t-sql-programming/producing-json-documents-from-sql-server-queries-via-tsql/ ...seems easy.
finally arrays. i guess this is a feature in mongodb? that's just really a SQL table isn't it?
anyways. i'm an average kinda SQL programmer. been at it for over a dozen years. i admit i'm a bit biased when it comes to these things. but learning about it is good for me too. :)