How to query metadata to discover nested views

Views – Introdution

Views, love them or loathe them if you work for very long with SQL Server you are bound to encounter them. Also I should warn you that discussing views is straying into hallowed ground, look at the discussion thread on this introduction to views by Mr Celko, it’s a flaming religious strife.

https://www.red-gate.com/simple-talk/sql/learn-sql-server/sql-view-basics/

So putting on my asbestos shirt, I’m going to risk writing a few lines about views and in particular detecting nested views. So what is a view ? It is a stored query which can be treated like a table, it can join several tables together, filter and/or aggregate rows and in turn refer to other views.
So when should one use a view ? I refer you to lists from a couple of references, follow the links and read the whole article if you want to know more.

http://www.craigsmullins.com/cnr_0299b.htm

• to provide row and column level security
• to ensure efficient access paths
• to mask complexity from the user
• to ensure proper data derivation
• to provide domain support
• to rename columns, and
• to provide solutions which can not be accomplished without views

https://technet.microsoft.com/en-us/library/ms188250(v=sql.105).aspx

• to focus on specific data
• to simplify data manipulation
• to provide backward compatibility
• to customize data
• to export and import data
• to combine partitioned data across servers

Views – pitfalls

Sounds like views can be pretty useful then ? Well yes but with the power comes a degree of danger in inexperienced hands. The chief risk coming when one view refers to another which in turn refers to another – this is known as a nested view and to your grizzled DBA sets the alarm bells ringing. The first alarm is the complexity of tracking columns back to their source through various levels of the application of aliases when debugging errors or making changes. Secondly nested views often end up confusing and overloading the query optimiser which attempts to simplify the query plan to the most efficient access path to the requested data but can’t always succeed. When an inner join is included in the view then the optimizer may be forced to retrieve and join all the rows from the second table (to filter the original table) even though the query on the view does not include columns from the second table. A lot of the evidence re the poorer performance of nested views is anecdotal – I provide links below giving more information and also showing how the eventual execution plan can be simplified & performance improved by unfolding the query and referring to the base tables instead.

https://www.red-gate.com/simple-talk/sql/performance/the-seven-sins-against-tsql-performance/

https://itknowledgeexchange.techtarget.com/sql-server/why-are-nested-views-horrible/

This next link is succinct and worth quoting in full :-

https://www.red-gate.com/simple-talk/sql/t-sql-programming/sql-code-smells/

“Nesting views as if they were Russian dolls Views are important for abstracting the base tables. However, they do not lend themselves to being deeply nested. Views that reference views that reference views that reference views perform poorly and are difficult to maintain. Recommendations vary but I suggest that views relate directly to base tables where possible.”

Detecting Nested Views

So far it’s mainly been a literature review but hopefully we have established that nested views within a database solution are something we would like to be made aware of. Now to more practical matters – how can that be achieved ?
We can make use of the DMV SYS.SQL_EXPRESSION_DEPENDENCIES present in all supported versions of SQL Server (2008 and onwards).

To quote Books on Line

“Contains one row for each by-name dependency on a user-defined entity in the current database. This includes dependences between natively compiled, scalar user-defined functions and other SQL Server modules. A dependency between two entities is created when one entity, called the referenced entity, appears by name in a persisted SQL expression of another entity, called the referencing entity. For example, when a table is referenced in the definition of a view, the view, as the referencing entity, depends on the table, the referenced entity. If the table is dropped, the view is unusable.”

We will use a recursive common table expression (CTE) to build a tree of views that refer to views until we reach the maximum (up to 32 levels – which is the maximum nesting that SQL Server allows for views). see code block at the end of this blogpost.

So to make our anchor query we need to restrict both the referencing and the referenced object types to be views. Then in the recursive part of the CTE we simply keep on referring back to the referencing_id and retreive any matching referenced_ids.

If you have a copy of AdventureWorks2016 and you run the setup scripts to create some nested views and then run the CTE you should get the following results

Note that we retrieve each view twice as there are dependencies on 2 views (vEmpNested joins HumanResources.vEmployee & HumanResources.vEmployeeDepartment)

In the case of vEmpNested4 the view appears 2 times twice, once for the full chain dependency through dbo.vEmpNested>dbo.vEmpNested2>dbo.vEmpNested3>dbo.vEmpNested4 and once for the reference directly from vEmpNested4 to vEmpNested.

I use this query when taking over a new database and I when I want to run some investigative metadata queries to better understand what I am dealing with. Past experience has shown that nested views are a frequent source of poor performance and once I know which of my views refer to other views (& how many levels of nesting are involved) I can be alert to these names coming up in execution plans, long running queries and other contexts.

TSQL Code

/*
-- SET UP
USE [AdventureWorks2016CTP3]
GO

create view vEmpNested as
select
e.BusinessEntityID
,e.Title
,e.FirstName
,e.MiddleName
,e.LastName
,e.Suffix
,e.JobTitle
,e.City
,ed.Department
,ed.GroupName
FROM HumanResources.vEmployee e
JOIN HumanResources.vEmployeeDepartment ed ON
e.BusinessEntityID = ed.BusinessEntityID

GO

create view vEmpNested2 as
select * from vEmpNested

GO

create view vEmpNested3 as
select * from vEmpNested2

create view vEmpNested4 as
select vEmpNested.*
from vEmpNested3
cross join vEmpNested

GO
*/

;WITH
cRefobjects AS (
-- Anchor level a view which refers to another view
SELECT DISTINCT
sed.referencing_id,
sed.referenced_id,
s.name AS SchemaName,
o.name as ViewName,
Convert(nvarchar(2000), N'>>'+ s.name+'.'+o.name) COLLATE DATABASE_DEFAULT as NestViewPath,
o.type_desc,
1 AS level
FROM sys.sql_expression_dependencies sed
INNER JOIN sys.objects o ON
o.object_id = sed.referencing_id and
o.type_desc ='VIEW'
INNER JOIN sys.schemas AS s ON
s.schema_id = o.schema_id
LEFT OUTER JOIN sys.objects o2 ON
o2.object_id = sed.referenced_id and
o2.type_desc IN ('VIEW')
WHERE
o2.object_id is null

UNION ALL
-- Recursive part, retrieve any higher level views, build the path and increment the level
SELECT
sed.referencing_id,
sed.referenced_id,
s.name AS sch,
o.name as viewname,
Convert(nvarchar(2000),cRefobjects.NestViewPath + N'>' + s.name+'.'+o.name) COLLATE DATABASE_DEFAULT,
o.type_desc,
level + 1 AS level
FROM sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects o ON
o.object_id = sed.referencing_id and
o.type_desc ='VIEW'
INNER JOIN sys.schemas AS s ON
s.schema_id = o.schema_id
INNER JOIN cRefobjects ON
sed.referenced_id = cRefobjects.referencing_id
)
SELECT DISTINCT SchemaName+'.'+ViewName as ViewName, NestViewPath, type_desc, level
FROM cRefobjects
WHERE level > 1
ORDER BY level desc, viewname
OPTION (MAXRECURSION 32);