I know there are several cases where we as database developers need reference tables to help us with our querying (e.g., Tally tables, Calendar tables, etc.). Occasionally, I find myself needing other types of reference tables, sometimes ones that may not be able to be dynamically generated. But let's ignore that last point for a second. The fact is, I need to store my reference tables somewhere.
The easiest option, I think, is to create the reference tables within the database where I'm currently working.
But let's say this is a third-party database that you can't or aren't supposed to create tables in. Another option might be to create a separate DBAUtils or Common database to hold these tables that contain static data.
Let's say that you don't want to create a new database just for these one or two tables because then the queries become dependent on two databases when all of the raw data I need lives in one of the databases.
The third-party database allows for views and stored procedures, so I'm wondering--does anyone ever use views to actually store data itself rather than the query to get the data? For example, if I needed to know some chemistry symbols and names for my query, rather than use any of the techniques above, I could store the data I need right within the view and never worry about base tables:
CREATE VIEW ChemReference AS
SELECT 'm' AS Symbol, 'meter' AS [Name], 'length' AS Quantity UNION ALL
SELECT 'g', 'gram', 'mass' UNION ALL
SELECT 'Pa', 'pascal', 'pressure' UNION ALL
SELECT 'K', 'kelvin', 'temperature' UNION ALL
SELECT 'mol', 'mole', 'amount of substance' UNION ALL
SELECT 'J', 'joule', 'energy, work, quantity of heat' UNION ALL
SELECT 's', 'second', 'time' UNION ALL
SELECT 'min', 'minute', 'time' UNION ALL
SELECT 'h', 'hour', 'time' UNION ALL
SELECT 'd', 'day', 'time' UNION ALL
SELECT 'y', 'year', 'time' UNION ALL
SELECT 'L', 'liter', 'volume' UNION ALL
SELECT 'ppm', 'parts per million', 'concentration' UNION ALL
SELECT 'M', 'molarity', 'solution concentration' UNION ALL
SELECT 'u', 'atomic mass unit', 'atomic mass'
Then use the view in my query. I know I wouldn't be able index the view because of the UNION ALLs, but it would be one way to get around the rules or not creating a table within the database and not introducing a dependency on another database. And maybe for few enough records like this, the index wouldn't provide much improvement anyway.
Thoughts on a static stored view?
Mike Scalise, PMP