lundi 2 février 2015

Efficiency of a view with hundreds of columns


We have a number of tables (about 40) with approximately 2500 columns between them that all have matching primary keys, but different unique columns. For various procedures and views, we'll draw on any number of them - sometimes hundreds of them - to build the necessary results.


I've been considering creating a view that ties all these tables and columns together primarily for the sake of simplicity in referring to the tables and columns in code for subsequent procedures and views, but I wanted to check that I'm not making a major mistake in doing so. Unfortunately, I don't directly have the ability to change the tables, although there are circumstances where the tables will have columns added or removed, so I need to account for that.


My first question is, I would expect for any subsequent query against the view that the SQL Server optimizer would exclude any tables or columns from the actual execution that aren't actually used in the result set, correct?


I would also need to create a stored procedure to update the definition of the view so that it adjusts the columns included in the view as they change, correct? Are there any features or possible hang-ups I'm not considering here?





Aucun commentaire:

Enregistrer un commentaire