Key/Value table with limiters

performancequery-performance

I'm sure someone must have hit this or a problem like this before but I'm not finding anything on a google or DBA exchange search.

I have a table storing key/value pairs. There are also 2 limiting columns in the table, host and environment, both nullable. The way that the limiting tables work is that if there is an entry in the host column then the key/value applies to that host, similarly if there is an entry in the environment column then that entry takes effect only for that environment. If a key/value is sought with a host or environment then the limited values take precedent over the values where host=NULL or environment=NULL. Furthermore, host takes precedence over environment.

So as an example, here are some table entries:

id      env     host     key    value
1       NULL    NULL     fruit  apple
2       prod    NULL     fruit  banana
3       prod    www      fruit  mango
4       test    NULL     fruit  peach
5       NULL    test     fruit  orange

Given that example data, what I'm after is a SQL statement (or statements) that will return these data based on these criteria:

env     host   key    data returned
NULL    NULL   fruit  1, fruit, apple
prod    NULL   fruit  2, fruit, banana
NULL    www    fruit  3, fruit, mango
junk    junk   fruit  1, fruit, apple  // NULL/NULL values are used as a fallback
junk    www    fruit  1, fruit, apple  // no match, fallback
prod    test   fruit  5, fruit, orange // host=test takes precedence

Does that make sense?

I can break the search up into multiple selects, try each one, check to see if I get a hit, etc, but I'm hoping to achieve this as efficiently as possible.

Ideally I would like the type of search that would be easily extensible if more limiters are added later (e.g. group, namespace, etc).

Anyone?

Best Answer

So you're going to pass in two optional parameters:

  • @host
  • @env

Example sql (to be put in a stored proc):

Select top 1 * from dbo.FilterTable ft
Where 
    ((@host is not null and ft.host = @host) or ft.host is null)
    And ((@env is not null and ft.environment = @env) or ft.environment is null)
Order by 
    Case
        When ft.host is not null and ft.environment is not null then 1
        When ft.host is not null then 2
        When ft.environment is not null then 3
        Else 4
    End asc
Related Question