1

I've inherited some legacy code which has some ostensibly strange views in an SQL Server database.

They are basically complete passthroughs for a table. No column differences - a comparison of the columns using INFORMATION_SCHEMA.COLUMNS yields identical results. There is no limiting with WHERE clauses. There are no joins into other tables. As I said, complete passthroughs.

Personally, I would go direct against the table and I don't understand why my predecessors haven't?

Is there any present or historical reason why this route might have been taken?

Paul Alan Taylor
  • 559
  • 2
    How are that views being used? Are they being used? Are the views formatting any data? Obfuscating data? – Greg Burghardt Oct 29 '20 at 01:47
  • In some queries, but not all. I suspect it was some idea that was intended to be developed, but no, it's like for like, no formatting, no dropping of blobby column names. It seems bizarre. The answer might just be that my predecessors were insane ;) – Paul Alan Taylor Oct 29 '20 at 01:52
  • They probably had some idea. There is probably two tables out there that implemented that idea and you just haven't found it yet. Keep looking. I bet you'll find it, but not before going insane. – Greg Burghardt Oct 29 '20 at 02:12

2 Answers2

6

The reason you would do this is it gives you the freedom to change the table however you like as long as you keep the “interface”, aka the view, intact. This could be a anything from putting it in a different schema or even database, to simply adding an additional column which only gets used under certain circumstances which you don’t want to expose (for instance you could have a persisted calculated column which gets used by a stored procedure or job, but isn’t used by the primary app).

Personally I’ve recently had the idle thought of having a cross-database view be changed to point to a new database with a view in it to the original table, so that the original database table can be refactored without having to change the applications that depend upon it. Not sure why I thought a new database would be better than what you describe.

Why your predecessors did it, I couldn’t say. Just that it’s not always a completely crazy idea.

jmoreno
  • 10,853
  • 1
  • 31
  • 48
0

As I see it, jmoreno described the most pragmatic reason: decouple the systems to provide flexibility against future changes: different database systems may have different cadence of deployments and different teams may be responsible for management of the underlying tables.

Other reasons could be security and consistency: views are a handy way of granting access to the underlying data in a consistent and manageable way. They may also provide a layer of abstraction which may need to be used later to facilitate system changes.

I normally use pass-through views as an interface, a common pattern for any database systems integration in some ETL/ELT scenario.

DataUnitedLtd
  • 1