Tuning T-SQL - Professional SQL Server 2005 Performance Tuning - page 278

There is a point where adding additional columns has no effect on the number of reads since the pages
have to be loaded anyway. The cost is paid by the merge operation that is putting the Join together
between the SalesOrderHeader and SalesOrderDetail tables. This is seen in the steady increase in CPU
costs for each additional column added to the Select.
SimpleSelectViews
If there is a performance penalty for each additional column in a select statement, how is performance
affected by using views? What happens when you join to a view? To consider views from a perfor-
mance perspective, separate views into two types: Simple select and Complex. A view by definition is a
virtual table that should be treated as if the results were fully materialized. From a performance tuning
perspective, using a simple select view is the same as selecting from a derived table. The T-SQL con-
tents of the view does not get reorganized to make a better plan when simply selected from or joined to
additional tables. You can test this by running the T-SQL contents of a view against a straight SELECT
on a view. Normally, the only performance penalties from joining to views come from the overhead of
loading pages of unneeded columns or from additional JOINS made to either existing view tables or new
tables when additional columns are retrieved.
Performance problems can appear later in the life cycle of a view when it is being used for another
purpose and an additional field is needed from an existing table in the view. What typically happens is a
join is made directly to the view to retrieve the additional column. Unfortunately, the optimizer doesn't
realize that this table is already part of the view and reads the table twice, once for the view, and the
second time for the additional join. To see this in action, compare a select from the vEmployee view with
a join back to the HumanResources.Employee Table to retrieve the NationalIdNumber, with a straight
select to the underlying tables to pick up the additional column in the Employee table.
--SELECT FROM VIEW
SELECT v.*, Emp.NationalIdNumber
FROM [HumanResources].[vEmployee] v
INNER JOIN [HumanResources].[Employee] emp
ON V.EmployeeId = emp.EmployeeId
Copy the contents of the HumanResources.vEmployee view in the query window and add the additional
select column for NationalIdNumber to the select list:
--SELECT USING BASE TABLES
SELECT
e.[EmployeeID],c.[Title],c.[FirstName],c.[MiddleName],c.[LastName],c.[Suffix]
,e.[Title] AS [JobTitle],c.[Phone],c.[EmailAddress],c.[EmailPromotion]
,a.[AddressLine1],a.[AddressLine2],a.[City],sp.[Name] AS [StateProvinceName]
,a.[PostalCode],cr.[Name] AS [CountryRegionName],c.[AdditionalContactInfo]
,e.[NationalIdNumber]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]
INNER JOIN [HumanResources].[EmployeeAddress] ea
ON e.[EmployeeID] = ea.[EmployeeID]
INNER JOIN [Person].[Address] a
ON ea.[AddressID] = a.[AddressID]
INNER JOIN [Person].[StateProvince] sp
ON sp.[StateProvinceID] = a.[StateProvinceID]