November 21, 2017 at 1:17 pm
Hi,
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
Mike Scalise, PMP
https://www.michaelscalise.com
November 21, 2017 at 1:37 pm
My preference would be to put the additional stuff into another database & live with the dependency.
I would, however, make sure that this was a one-way dependency (source DB references common DB, but not vice versa).
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 21, 2017 at 2:39 pm
If you want to store data in a vendor's database, do it. My recommendation would be to create your own schema in their database and put all your objects in there. And no foreign keys to their objects or you'll break their upgrades.
An alternative is to put everything in a script and create your objects, then test, then delete all your objects each time you want to test. Or just create two procedures in the vendor's database to CreateMyObjects and DropMyObjects for easy use.
Another alternative is to create synonyms in the vendor's database that point to your database objects.
November 22, 2017 at 7:06 am
Thanks, everyone, for the suggestions. It's always good to see the myriad ways to solve problems in SQL Server.
Mike Scalise, PMP
https://www.michaelscalise.com
November 22, 2017 at 11:59 am
I have used views for that, but only for a very limited number of values, since it can't be effectively indexed.
Unique indexes are potentially extremely valuable, even for small lookup tables, because it can prevent SQL from having to loop thru every lookup row for every "calling"/joining row. In other words, keep in mind that the number of rows requiring the lookup can still be quite large even if the lookup table itself is quite small.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 22, 2017 at 3:21 pm
I agree with Bill Talada... it's your server... you should be able to drop a table in wherever you need to. If it end up on a 3rd party database, then do as Bill suggests and create your own schema on that database for a table with the understanding that you may have to rebuild the table after a vendor upgrade. Do don't always do an in-place upgrade... sometimes they'll squirrel off the data, drop the database, create a new one, and then inport the data they squirreled away.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 24, 2017 at 6:34 am
Have a word with the vendor and ask if they have a preferred method by which you can add objects for yourself. In my experience some vendors will have a reserved schema or prefix specifically designed for this purpose and these are excluded from any updates that they may issue which involves dropping objects.
I have generally found vendors to be fairly helpful when it comes to this kind of thing.
...
November 27, 2017 at 8:09 am
+1 for the use your own schema
Another option if you only need the reference data inside a single sproc would be to use #temp_tables or @in_memory tables. #tables can be indexed and both will die when the session ends (it makes sense to wrap prefix your #temp creations with a begin try
drop table #temp
end try
begin catch
end catch
or or if exists (..) syntax
as it stops create/insert errors in development when the table already exists
The disadvantage is that the logic and the data are hidden inside the procedure so cannot be maintained without editing the code which may go against dev-ops principles.
November 27, 2017 at 10:42 am
I've used views like this, but don't like it. I'd +1 for the schema in the 3rd party db with no linkage/dependency on their objects.
November 27, 2017 at 11:03 am
Steve Jones - SSC Editor - Monday, November 27, 2017 10:42 AMI've used views like this, but don't like it. I'd +1 for the schema in the 3rd party db with no linkage/dependency on their objects.
My biggest reservation with choosing this option is the fact that you are mixing in-house code with external code and therefore CI/CD processes which perform deployments or create drift reports, for example, become all the more complicated to build and maintain.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 27, 2017 at 11:52 am
Phil Parkin - Monday, November 27, 2017 11:03 AMMy biggest reservation with choosing this option is the fact that you are mixing in-house code with external code and therefore CI/CD processes which perform deployments or create drift reports, for example, become all the more complicated to build and maintain.
Maybe. It's not terribly different from using a table and tracking the data as part of CI/CD. I could easily write tests to detect what values I want.
The bigger issue for me is that if there are issues, someone might change the code of the view rather than make a data change, which might not be caught as easily.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply