lnardozi 61862 (1/29/2013)
Now I'm really curious. What does changing a table structure have to do with SQL programming skills?
--Jeff Moden
I'm sure you've seen this hundreds of times before - you're one of the brighter lights on this board. You need some configuration data stored somewhere, so a table is created. Then another and another and before you know it you've got a few hundred. Those few hundred tables each have their own queries, their own table design, they end up getting used in joins - sometimes very badly. When the tables get created, do they have the correct indices? What is involved enterprise wide when it's time to change one of those tables - particularly one used in several hundred queries? Assuming your DBA has the time to look at all those queries (mine doesn't), what are the odds he'll hit every single one right on the head? My system is heavily optimized for retrieval and supports everything except binary - which means the TVFs can be used in stored procs, views, web services, wcf services, restful services, Json services and Sharepoint. Anything in our enterprise that might have had hard coded information in instead table driven with no duplication of data and a well defined map that explains the dependencies enterprise wide. Sniff if you want, but it's the best thing I've ever done in almost 30 years of programming.
I try not to sniff at anything except unnecessary RBAR or the improper use of CLR. 😀
Unless I'm mistaken, you talking about a bunch of similar small reference tables that may have an ID, a code, and a description in them? If that's correct, I don't see those tables ever having a new column being added to them. Could you explain why you might want to add a column to such a reference table?
If you have hundreds of them, I can see where adding new data to them could be a bit of a problem because you'd have to find the right one first. That would be easy if DRI were properly instantiated at table creation time.
Of course, I might be misunderstanding what you mean. Are you, in fact, saying that you consolidated all such reference tables into a single EAV? If so, how are you handling the implicit conversion problems that destroy SARGability during joined lookups? Did you use SQL_Variant to fix that particular problem or some other method?
--Jeff Moden
Change is inevitable... Change for the better is not.