Query to View with Many Columns

Hi All,

I have a simple question. If I have a view that query from joined multiple tables and return a lot of columns (more than 100). If I run a simple query just return several columns (e.g. 4-5 columns), will SQL Server query all columns first from all joined table? or can SQL Server query only necessary column from related table?

Does anyone have idea how to join table that can reflect both left and right join?

Table A Table B

Column1 Column2 Column3 Column4 Column1 Column2 Column 3 Column5

A Jan 5 xxx A Jan 1 yyy

B Feb 3 C Mar 4

B Mar 4 C Apr 3

C Apr 2 D May 2

E Mar 1

Result Table

Column1 Column2 Column3 Column4 Column 5

A Jan 6 (= 5+1) xxx yyyy

B Feb 3

B Mar 4

C Mar 4

C Apr 5 (= 2+3)

D May 2

E Mar 1

So the result table is a join on column1 and column2 (both are primary key), with column3 is a sum aggregate. Table A has additional column4 and Table B has additional column5, so quite difficult to union (In fact, there are a lot of column differences between table).

Thanks for the help.

The VIEW will first create a virtual table containing ALL of the columns, and then you will get a resultset of just the columns requested.

That could be very inefficient. Most likely, it would be better to query against the underlaying table for just the columns required. Or create a separate VIEW for this purpose.

|||

Thanks for the answer .

The problem is this query will be executed by Report Builder, so all of those columns will be needed as user might select any combination to include in the report, so it's quite difficult to provide only certain column by creating another view. I already try to separate the View into multiple entity in Report Builder, but when it comes to the aggregate, some of the calculation is calculated wrongly in the Report Builder, and some complexity with multiple currency/exchange rate araise, that force me to add more column inside the view .

Any idea or suggestion for my situation? Thanks

|||

You haven't provided enough detail for anyone to offer cogent suggestions.

However, one thing comes to mind. You might be able to use a Stored Procedure that Creates a custom VIEW (from input parameters), and then returns the resultset from that VIEW.