Be cautious. Not all of these approaches optimize out the same.

We use an object factory that is tightly coupled to database tables. To simplify data access we have a single view that becomes in essence a partitioned table, called dbo.Object

This view looks something like this:

create view dbo.Object

as

select 1 as objecttype_id, ID, Description from dbo.object1

union all

select 2 as objecttype_ID, ID, Description from dbo.object2

union all

select 3 as objecttype_ID, ID, Description from dbo.object3

union all

select 4 as objecttype_ID, ID, Description from dbo.object4

Now to query against that view, you can simply predicate on an objecttype. If you check the IO statistics, it is only looking up against the one table, provided you query it properly. Don't bother looking at the Query Plan as it does not truly reflect what the server does to fetch the data.

use this to check io stats:

set statistics io on

--this query works properly

select * from dbo.object

where objecttype_id = 2

--this query does not, as it evaluates each table, but at least it doesn't scan them.

declare @jobtype int

set @objtype = objecttype.object1() -- a udf that returns a value

select * from dbo.object where objecttype_id = @objtype

--this query is closer, but it is creating a working table and is evaluating for every row

select * from dbo.object where objecttype_id = objecttype.object1()

--tried this, hoping that the deterministic decisions were based on the input param to output, but to no avail

--results were same as above.

select * from dbo.object where objecttype_id = objecttype.object1(0)

--Same result

select * from dbo.object where objecttype_id = objecttype.GetType('object1')

--this is the worst, as it scans each table

select * from dbo.object

where objecttype_id = (Select objecttype.object1())

Joining on a settings table is an option, unfortunately as we may be doing multiple joins in tables requiring these constants, we would have to do multiple joins on the settings table, which turns out to be very inefficient.

So until we have a better CONST struct in SQL we will be forced to hardcode values for some of our queries.