Pitfalls of complex views

Views are often used with the same intent as User-Defined Functions (UDFs) – to allow easy re-use of what could otherwise be a complex expression to inline in our T-SQL query. Often developers build a view that will serve multiple queries, and then just select from that view with different SELECT statements and different filters, be those joins or search predicates. However, what may look like a seemingly harmless T-SQL construct may be detrimental for query performance if the underlying view is complex.

Imagine that in the AdventureWorks sample database, a developer built an all-encompassing view that gets data on all company employees, as in the following example:

CREATE OR ALTER VIEW [HumanResources].[vEmployeeNew]
AS
SELECT e.[BusinessEntityID], p.[Title], p.[FirstName], p.[MiddleName],
     p.[LastName], p.[Suffix], e.[JobTitle], pp.[PhoneNumber],
     pnt.[Name] AS [PhoneNumberType], ea....