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.