SQL Server equivalent of hstore?

Question

Is there an equivalent of Postgresql's hstore within SQL Server? I'm running SQL Server 2008 with a web application that has an increasingly large need for sparse key/value pairing on the database. It is not feasible in our situation to create a new column each time this occurs. Currently we use a standard column and throw all of this "schema-less" sparse data into a single column and then run checks in the application for the keys we need. This is not very clean/elegant.

I've just recently begun working on another project that uses Postgresql and uses hstore for a very similar problem. hstore is a much cleaner solution to the problem we have, so now I'm wondering if there is an equivalent feature within SQL Server 2008?

Gynaecomastia

There aren't any functions that do this in SQL Server. You could create a table called KeyValue with 2 columns (Key and Value). You would then join onto that table when needed and insert new rows as needed. – Jimerson

That sort of defeats the purpose. It would just create a situation where numerous joins are required to get at all of the keys for a single row of data. For the number of keys we are dealing with this would be terribly slow. Currently we just drop the key/val pairing all into a single column and use a regexp match to get what we need. This works ok, but an hstore equivalent would be ideal. Another option would be to add a schema-less database to supplement this type of data to our relational database, but that is just to heavy for our current needs. hstore offers the best of both worlds :( – Gynaecomastia

You'd probably be stuck with storing json or xml, and using user-defined functions to fetch out keys. Needless to say, it won't be fast. Y'know, a really handy option here is to use PostgreSQL ... ;-) – Greeson

Craig; you're preaching to the choir. Unfortunately that decision is out of my hands on this one. – Gynaecomastia

You say it would be terribly slow but it is a simple join to one table. Assuming the keys are unique, index that column and you should have fast enough performance for what you are trying to do. I don't see doing regex/string math being faster than a b-tree operation. I could be wrong and if I am, please let me know. – Jimerson

@TTeeple, if the situation were such that I was looking for a single key I could join once and have the key/value come back as part of my row data. I'm dealing with many keys and the number of keys continues to grow. To retrieve all of the data in a single row would require numerous joins, one for every key which would be slow even with indexing. Using a single join would return multiple rows of data; this would create a larger data set which in my situation would slow data delivery times, not map to the ORM, and be slower to handle in my client side application which is javascript heavy. – Gynaecomastia

MSSQL 2016 has a json type learn.microsoft.com/en-us/sql/relational-databases/json/… – Freestyle

Do you only need to fetch key/value data for a certain row or do you want to be able to look up rows based on this key/value data? – Martyrize

Answer 1

MSSQL does not come with any builtin function similar to "hstore" unless you want to build your function that will return the value based on the key.

Concordat