• Home
  • About Us
  • Contact Us
  • Privacy Policy
  • Special Offers
Business Intelligence Info
  • Business Intelligence
    • BI News and Info
    • Big Data
    • Mobile and Cloud
    • Self-Service BI
  • CRM
    • CRM News and Info
    • InfusionSoft
    • Microsoft Dynamics CRM
    • NetSuite
    • OnContact
    • Salesforce
    • Workbooks
  • Data Mining
    • Pentaho
    • Sisense
    • Tableau
    • TIBCO Spotfire
  • Data Warehousing
    • DWH News and Info
    • IBM DB2
    • Microsoft SQL Server
    • Oracle
    • Teradata
  • Predictive Analytics
    • FICO
    • KNIME
    • Mathematica
    • Matlab
    • Minitab
    • RapidMiner
    • Revolution
    • SAP
    • SAS/SPSS
  • Humor

Tracking Underlying Object Changes in Views

April 7, 2020   BI News and Info

Views are used to encapsulate common queries for reuse by SQL Server database objects or applications. They also allow for customized permissions to be applied while avoiding granting access to the underlying objects.

While this can be hugely beneficial, views are additional database objects that incur a maintainability cost in addition to the typical cost of upkeep for new code.

When a view’s underlying objects change, the view itself will not change. This can result in a view where the data types of columns, as well as nullability, precision, and scale can be reported inaccurately. When this happens, it is possible for queries against these columns to return errors, truncate data, perform poorly, or otherwise behave in unexpected ways.

This article will delve into views, how they are defined, and how T-SQL can be used to programmatically test the validity of views and ensure they never become stale.

How Can Views Become Stale?

To demonstrate how a view can fall out-of-sync with the tables it references, we will introduce a view and step through the process of altering data types underneath it.

Consider the view HumanResources.vEmployeeDepartment, which can be found in the AdventureWorks sample database:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

CREATE VIEW [HumanResources].[vEmployeeDepartment]

AS

SELECT

    e.[BusinessEntityID]

    ,p.[Title]

    ,p.[FirstName]

    ,p.[MiddleName]

    ,p.[LastName]

    ,p.[Suffix]

    ,e.[JobTitle]

    ,d.[Name] AS [Department]

    ,d.[GroupName]

    ,edh.[StartDate]

FROM [HumanResources].[Employee] e

INNER JOIN [Person].[Person] p

ON p.[BusinessEntityID] = e.[BusinessEntityID]

    INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh

    ON e.[BusinessEntityID] = edh.[BusinessEntityID]

    INNER JOIN [HumanResources].[Department] d

    ON edh.[DepartmentID] = d.[DepartmentID]

WHERE edh.EndDate IS NULL

The view contains ten columns that are selected from four tables. The data types for each of these columns are defined based on the underlying source columns. For example, the Suffix column in Person.Person is NVARCHAR(10); therefore, the Suffix column in vEmplloyeeDepartment will also be NVARCHAR(10). The data types of view columns can be quickly checked via SQL Server Management Studio by expanding Views within the database:

 Tracking Underlying Object Changes in Views

Let’s consider an upcoming software release that lengthens the Suffix column from 10 to 15 characters to accommodate new entries from within an application. Lengthening columns is a common change that helps an app adjust to changing business requirements as its data needs evolve over time. The change can be made with the following T-SQL:

ALTER TABLE Person.Person ALTER COLUMN Suffix NVARCHAR(15) NULL;

This change can be confirmed via the table definition:

 Tracking Underlying Object Changes in Views

If we return to the view definition, though, we’ll note that the column definition there has not changed:

 Tracking Underlying Object Changes in Views

The discrepancy is not a UI glitch. No amount of refreshing the table or reopening the database server will change the length of the column. This functionality is intentional, and views are designed not to update when referenced objects change. The security, performance, and data ramifications of cascading data type changes would be far more consequential than the current state of affairs in which no updates occur.

Anyone that looks at the view definition will observe a length of 10, whereas the Person table reports a length of 15. This discrepancy can result in a variety of problems that are not likely to manifest themselves immediately, such as:

  • Confusion and disruption to application development as the data type definitions documented by these two sources conflict with each other.
  • ORMs or data analytics tools may report the wrong column length and throw errors or behave erratically.
  • Unexpected behavior by SQL Server when processing misreported columns.

Note that the actual observed behavior will vary depending on the details of a view’s definition, the table’s definition, and how they are queried. Arguably, uncertainty is worse in code than a known and well-documented bug, and, therefore, we should not rely on unexpected behavior to diagnose view-related inconsistencies.

Since a software release does not always immediately precede corresponding data changes, it is possible that weeks or months could pass before a user enters data greater than 10 characters into this column. Our ability to keep track of views and update them diligently is therefore important while a release is fresh in our minds.

Updating View Metadata

The solution to stale views is the built-in stored procedure sp_refreshview. When called, this stored procedure will regenerate the metadata for a given view based on all of its underlying objects. The syntax is straightforward and consists of one parameter: the name of the view.

Using this view, we can update the metadata for vEmployeeDepartment:

EXEC sp_refreshview @viewname = ‘Humanresources.vEmployeeDepartment’;

After executing, let’s return to the view definition, refresh it, and check the data type for the Suffix column:

 Tracking Underlying Object Changes in Views

After executing sp_refreshview, the view now reports the Suffix column as NVARCHAR(15), rather than its previous length of 10. Success!

What About Schema binding?

There is another option that allows views to stay in sync with their underlying objects: schema binding. When a view is created with schema binding, its definition is bound to the underlying objects. This prevents all changes to referenced objects while the schema bound view exists.

Schema binding protects a view from unintended changes to any objects it references. To illustrate this, let’s create a new view in AdventureWorks:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

CREATE VIEW v_employee_test

WITH SCHEMABINDING

AS

SELECT

Employee.BusinessEntityID,

Employee.NationalIDNumber,

Person.FirstName,

Person.MiddleName,

Person.LastName,

Person.Suffix

FROM HumanResources.Employee

INNER JOIN Person.Person

ON Person.BusinessEntityID = Employee.BusinessEntityID

WHERE Person.Suffix IS NOT NULL;

This view selects a handful of columns from two tables. Note the use of the WITH SCHEMABINDING clause on the CREATE VIEW statement.

Consider a scenario where a developer was to attempt to adjust the length of the Suffix column back to 10:

ALTER TABLE Person.Person ALTER COLUMN Suffix NVARCHAR(20) NULL;

Unlike earlier when the ALTER TABLE command was allowed, this attempt to change the column length will be met with an error message:

 Tracking Underlying Object Changes in Views

Schema binding enforces the underlying database objects and will prevent changes to any of them. This is intentional and forces developers to address the view itself before making changes. The two most common solutions to manage changes to underlying objects are:

  1. Drop the view, change the tables it depends upon, recreate the view with schema binding.
  2. Alter the view to remove schema binding, change the tables it depends upon, and alter the view again to add schema binding to it.

The first option is problematic as dropping the view will remove its ability to serve an application. If an app needs the view, then dropping it will result in an outage. The second option has more steps but allows for the view to remain in place while objects are altered. The following script illustrates applying this method to our previous column change:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

ALTER VIEW v_employee_test

AS

SELECT

Employee.BusinessEntityID,

Employee.NationalIDNumber,

Person.FirstName,

Person.MiddleName,

Person.LastName,

Person.Suffix

FROM HumanResources.Employee

INNER JOIN Person.Person

ON Person.BusinessEntityID = Employee.BusinessEntityID

WHERE Person.Suffix IS NOT NULL;

GO

ALTER TABLE Person.Person ALTER COLUMN Suffix NVARCHAR(20) NULL;

GO

ALTER VIEW v_employee_test

WITH SCHEMABINDING

AS

SELECT

Employee.BusinessEntityID,

Employee.NationalIDNumber,

Person.FirstName,

Person.MiddleName,

Person.LastName,

Person.Suffix

FROM HumanResources.Employee

INNER JOIN Person.Person

ON Person.BusinessEntityID = Employee.BusinessEntityID

WHERE Person.Suffix IS NOT NULL;

GO

This script succeeds in altering the column to a length of 20 by removing schema binding, altering the column length, and then adding schema binding back to the view. The view remains present for the entire operation, ensuring that anything that relies on it will continue to function.

Schema binding is heavy-handed, but an effective solution for managing the tables that views rely on and ensuring that changes cannot slip through the cracks unnoticed. It forces developers to consider dependencies anywhere they exist, prior to changes being made.

Many organizations will not use schema binding for this exact reason, in any case. If a database has many views in it, then schema binding will force developers to alter views frequently throughout the scripting process. This can bloat release scripts and by increasing the volume of release code also increase the risk of things going wrong.

The remainder of this article deals with databases where schema binding is not used for all views.

Solving the Problem Permanently

For most databases, schema binding will not be universally used. As a result, it will be possible to change objects without being forced to also address the views as part of those changes.

Tackling this challenge requires building a solution that can check view metadata before and after a release and determine if underlying objects have changed in ways that could invalidate their dependent views.

Setup

Running this test requires a development environment where we can make changes freely without worries about breaking an app. To do this, we would need a database backup or, if data is large, a copy of a database’s schema (with empty tables) to work against. Data is not needed for this validation, so feel free to leave it behind.

Validation Steps

The goal is to take a snapshot of all views in the database and compare that against what those views would look like after they are all refreshed. To do this, let’s create a pair of temporary tables that will store these snapshots:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

CREATE TABLE #view_metadata_before

( view_metadata_before_id INT NOT NULL

                IDENTITY(1,1) PRIMARY KEY clustered,

view_object_id INT NOT NULL,

view_schema_name SYSNAME NOT NULL,

view_name SYSNAME NOT NULL,

column_name SYSNAME NOT NULL,

column_id INT NOT NULL,

user_type_id INT NOT NULL,

user_type_name SYSNAME NOT NULL,

column_max_length SMALLINT NOT NULL,

column_precision TINYINT NOT NULL,

column_scale TINYINT NOT NULL,

is_column_nullable BIT NOT NULL,

is_schemabound_view BIT NOT NULL);

CREATE TABLE #view_metadata_after

( view_metadata_after_id INT NOT NULL

                IDENTITY(1,1) PRIMARY KEY CLUSTERED,

view_object_id INT NOT NULL,

view_schema_name SYSNAME NOT NULL,

view_name SYSNAME NOT NULL,

column_name SYSNAME NOT NULL,

column_id INT NOT NULL,

user_type_id INT NOT NULL,

user_type_name SYSNAME NOT NULL,

column_max_length SMALLINT NOT NULL,

column_precision TINYINT NOT NULL,

column_scale TINYINT NOT NULL,

is_column_nullable BIT NOT NULL,

is_schemabound_view BIT NOT NULL);

With some holding places defined, we can populate #view_metadata_before with the current state of affairs in the database:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

INSERT INTO #view_metadata_before

(view_object_id, view_schema_name, view_name, column_name,

         column_id, user_type_id, user_type_name, column_max_length,

         column_precision, column_scale, is_column_nullable,

         is_schemabound_view)

SELECT

views.object_id AS view_object_id,

schemas.name AS view_schema_name,

views.name AS view_name,

columns.name AS column_name,

columns.column_id,

types.user_type_id,

types.name AS user_type_name,

CASE WHEN types.name IN (‘nchar’, ‘nvarchar’) THEN

                columns.max_length / 2 ELSE columns.max_length END

                AS column_max_length,

columns.precision AS column_precision,

columns.scale AS column_scale,

columns.is_nullable,

OBJECTPROPERTY(views.object_id, ‘IsSchemaBound’)

                 AS is_schemabound_view

FROM sys.views

INNER JOIN sys.columns

ON columns.object_id = views.object_id

INNER JOIN sys.types

ON types.user_type_id = columns.user_type_id

INNER JOIN sys.schemas

ON schemas.schema_id = views.schema_id

ORDER BY views.object_id, columns.column_id;

The result of this is a row per column within each view and some metadata describing the nature of each column:

 Tracking Underlying Object Changes in Views

Note the CASE statement that checks the data type and halves the column length when a double-byte data type is present. This is not necessary, but helps the results match up better with what a user might see in SQL Server Management Studio.

The results are not an exhaustive list of column attributes. There are other pieces of metadata stored in sys.columns that could potentially affect how that column operates. These views contain the most commonly used attributes that will actually impact views. If a database environment has unique circumstances, then adjusting these tables to add more metadata is relatively straightforward.

With a baseline defined, we now need to refresh all views. If a view’s underlying objects have changed, then its metadata will also update accordingly. Updating all views requires a distinct list of views, which we can quickly collect from the list we generated above:

1

2

3

4

5

6

7

8

9

10

11

12

CREATE TABLE #distinct_view_list

(distinct_view_list_id INT NOT NULL

                IDENTITY(1,1) PRIMARY KEY CLUSTERED,

view_schema_name SYSNAME NOT NULL,

view_name SYSNAME NOT NULL);

INSERT INTO #distinct_view_list

(view_schema_name, view_name)

SELECT DISTINCT

view_metadata_before.view_schema_name,

view_metadata_before.view_name

FROM #view_metadata_before view_metadata_before

WHERE view_metadata_before.is_schemabound_view = 0;

This script takes the columns list and distills it into a list of views. Schemabound views are filtered out as they will not impact our testing. Since they cannot have their components altered without explicit view changes, there won’t be any surprises to find from them.

Refreshing all views will require some dynamic SQL to generate sp_refreshview statements for each view identified above:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

CREATE TABLE #view_ddl_exception_list

(view_name SYSNAME NOT NULL PRIMARY KEY CLUSTERED);

DECLARE @sql_command NVARCHAR(MAX);

SELECT @sql_command = ”;

SELECT

@sql_command = @sql_command + ‘

BEGIN TRY

    EXEC sp_refreshview N’” +

               CAST(distinct_view_list.view_schema_name

                   AS VARCHAR(MAX)) + ‘.’ +

                   CAST(distinct_view_list.view_name

                       AS VARCHAR(MAX)) + ”‘;

END TRY

BEGIN CATCH

    PRINT ‘‘The following view is broken via DDL errors

                during sp_refreshview: ‘ +

                CAST(distinct_view_list.view_schema_name

                    AS VARCHAR(MAX)) + ‘.’ +

                CAST(distinct_view_list.view_name

                    AS VARCHAR(MAX)) + ”‘;

IF @@TRANCOUNT > 0

BEGIN

ROLLBACK;

END

INSERT INTO #view_ddl_exception_list

(view_name)

SELECT

    ‘” + CAST(distinct_view_list.view_name

                    AS VARCHAR(MAX)) + ”‘;

END CATCH;’

FROM #distinct_view_list distinct_view_list;

EXEC sp_executesql @sql_command;

Note that there is some added complexity here in the form of try…catch blocks. A view can be broken by changes to its underlying tables, and we’d like to catch these errors gracefully, rather than have our code break mid-execution. The resulting command that gets executed will look like this:

 Tracking Underlying Object Changes in Views

This sample shows the T-SQL needed to refresh two views. The full script will contain a TRY and CATCH block for each view. Note that the temp table #view_ddl_exception_list will be populated with a broken view name, which will allow you to research further when this script is complete. We can capture more metrics, such as error codes and messages, but I wanted to stop short of making this script too complex. Feel free to add more debugging if it proves helpful in error-trapping.

Assuming no errors, the dynamic SQL above will execute quietly, and execution will move on to the next section of code with no fanfare. The next step is to capture view metadata a second time, using the same syntax that was used earlier:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

INSERT INTO #view_metadata_after

(view_object_id, view_schema_name, view_name, column_name,

         column_id, user_type_id, user_type_name, column_max_length,

         column_precision, column_scale, is_column_nullable,

         is_schemabound_view)

SELECT

views.object_id AS view_object_id,

schemas.name AS view_schema_name,

views.name AS view_name,

columns.name AS column_name,

columns.column_id,

types.user_type_id,

types.name AS user_type_name,

CASE WHEN types.name IN (‘nchar’, ‘nvarchar’) THEN

            columns.max_length / 2 ELSE columns.max_length END

            AS column_max_length,

columns.precision AS column_precision,

columns.scale AS column_scale,

columns.is_nullable,

OBJECTPROPERTY(views.object_id, ‘IsSchemaBound’)

           AS is_schemabound_view

FROM sys.views

INNER JOIN sys.columns

ON columns.object_id = views.object_id

INNER JOIN sys.types

ON types.user_type_id = columns.user_type_id

INNER JOIN sys.schemas

ON schemas.schema_id = views.schema_id

WHERE views.name NOT LIKE ‘x[_]%’

ORDER BY views.object_id, columns.column_id;

With a before and after snapshot available, we can compare the two and determine if any differences exist. To do this meaningfully, we should perform left joins between the before-snapshot and the after-snapshot. To be thorough, we should execute this query starting with the before metadata and then also starting with the after metadata. This ensures that we capture any changes, including dropped or added columns. The resulting query is a bit lengthy, but will provide exactly what is needed to evaluate changes in views:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

WITH CTE_before_except AS (

SELECT

view_object_id,

view_schema_name,

view_name,

column_name,

column_id,

user_type_id,

user_type_name,

column_max_length,

column_precision,

column_scale,

is_column_nullable,

is_schemabound_view

FROM #view_metadata_before

EXCEPT

SELECT

view_object_id,

view_schema_name,

view_name,

column_name,

column_id,

user_type_id,

user_type_name,

column_max_length,

column_precision,

column_scale,

is_column_nullable,

is_schemabound_view

FROM #view_metadata_after),

CTE_after_except AS (

SELECT

view_object_id,

view_schema_name,

view_name,

column_name,

column_id,

user_type_id,

user_type_name,

column_max_length,

column_precision,

column_scale,

is_column_nullable,

is_schemabound_view

FROM #view_metadata_after

EXCEPT

SELECT

view_object_id,

view_schema_name,

view_name,

column_name,

column_id,

user_type_id,

user_type_name,

column_max_length,

column_precision,

column_scale,

is_column_nullable,

is_schemabound_view

FROM #view_metadata_before)

SELECT

CTE_before_except.view_object_id,

CTE_before_except.column_id,

CTE_before_except.view_schema_name AS view_schema_name_before,

view_metadata_after.view_schema_name AS view_schema_name_after,

CTE_before_except.view_name AS view_name_before,

view_metadata_after.view_name AS view_name_after,

CTE_before_except.column_name AS column_name_before,

view_metadata_after.column_name AS column_name_after,

CTE_before_except.user_type_id AS user_type_id_before,

view_metadata_after.user_type_id AS user_type_id_after,

CTE_before_except.user_type_name AS user_type_name_before,

view_metadata_after.user_type_name AS user_type_name_after,

CTE_before_except.column_max_length AS column_max_length_before,

view_metadata_after.column_max_length AS column_max_length_after,

CTE_before_except.column_precision AS column_precision_before,

view_metadata_after.column_precision AS column_precision_after,

CTE_before_except.column_scale AS column_scale_before,

view_metadata_after.column_scale AS column_scale_after,

CTE_before_except.is_column_nullable AS is_column_nullable_before,

view_metadata_after.is_column_nullable

              AS is_column_nullable_after,

CTE_before_except.is_schemabound_view

INTO #sp_refreshview_results

FROM CTE_before_except

LEFT JOIN #view_metadata_after view_metadata_after

ON view_metadata_after.view_object_id = CTE_before_except.view_object_id

AND view_metadata_after.column_id = CTE_before_except.column_id

UNION

SELECT

view_metadata_before.view_object_id,

view_metadata_before.column_id,

view_metadata_before.view_schema_name AS view_schema_name_before,

CTE_after_except.view_schema_name AS view_schema_name_after,

view_metadata_before.view_name AS view_name_before,

CTE_after_except.view_name AS view_name_after,

view_metadata_before.column_name AS column_name_before,

CTE_after_except.column_name AS column_name_after,

view_metadata_before.user_type_id AS user_type_id_before,

CTE_after_except.user_type_id AS user_type_id_after,

view_metadata_before.user_type_name AS user_type_name_before,

CTE_after_except.user_type_name AS user_type_name_after,

view_metadata_before.column_max_length AS column_max_length_before,

CTE_after_except.column_max_length AS column_max_length_after,

view_metadata_before.column_precision AS column_precision_before,

CTE_after_except.column_precision AS column_precision_after,

view_metadata_before.column_scale AS column_scale_before,

CTE_after_except.column_scale AS column_scale_after,

view_metadata_before.is_column_nullable

               AS is_column_nullable_before,

CTE_after_except.is_column_nullable AS is_column_nullable_after,

CTE_after_except.is_schemabound_view

FROM CTE_after_except

LEFT JOIN #view_metadata_before view_metadata_before

ON view_metadata_before.view_object_id = CTE_after_except.view_object_id

AND view_metadata_before.column_id = CTE_after_except.column_id;

IF EXISTS (SELECT * FROM #sp_refreshview_results)

BEGIN

SELECT

*

FROM #sp_refreshview_results;

END

The results of the above T-SQL provide a clean data set showing what views have changed, but have not been refreshed:

 Tracking Underlying Object Changes in Views

Seven views rely on the Suffix column that we altered earlier. The details above show the view name, the column, and how it changed. Before and after metadata are included, allowing for a full understanding of changes without any guesswork. Using this information, we can review our application changes and ensure that views remain accurate as changes occur to tables and columns.

One hugely important note: Always run this process in a development environment! In order to do a before & after schema comparison, it is necessary to refresh views and determine if any changes have occurred. Perform this test in development and then refresh specific views in production to bring them in sync with the underlying schema.

Bonus: Validating Views

We can also test views for errors that may have arisen due to changes in underlying objects. For example, if a table that a view references is dropped, the view will throw an error when queried. Data type mismatches or truncation could also occur if data types change without related views getting altered or refreshed.

This process is simpler than verifying schema changes and can be conducted by a nice combination of brute force and dynamic SQL. If a view no longer functions correctly, it can be tested by selecting a row and catching errors when they occur. Dynamic SQL provides an easy means to accomplish this task:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

DECLARE @sql_command NVARCHAR(MAX);

IF EXISTS (SELECT * FROM tempdb.sys.tables

           WHERE tables.name LIKE ‘#distinct_view_list%’)

BEGIN

DROP TABLE #distinct_view_list;

END

CREATE TABLE #distinct_view_list

(distinct_view_list_id INT NOT NULL

             IDENTITY(1,1) PRIMARY KEY CLUSTERED,

view_schema_name SYSNAME NOT NULL,

view_name SYSNAME NOT NULL);

INSERT INTO #distinct_view_list

(view_schema_name, view_name)

SELECT

schemas.name AS view_schema_name,

views.name AS view_name

FROM sys.views

INNER JOIN sys.schemas

ON schemas.schema_id = views.schema_id;

IF EXISTS (SELECT * FROM tempdb.sys.tables WHERE tables.name

           LIKE ‘#view_ddl_exception_list%’)

BEGIN

DROP TABLE #view_ddl_exception_list;

END

CREATE TABLE #view_ddl_exception_list

(view_name SYSNAME NOT NULL PRIMARY KEY CLUSTERED);

SELECT @sql_command = ”;

SELECT

@sql_command = @sql_command + ‘

BEGIN TRY

SELECT TOP 1 *

    INTO temp_results_’ +

                    CAST(distinct_view_list.distinct_view_list_id

                        AS VARCHAR(MAX)) + ‘

FROM [‘ + CAST(distinct_view_list.view_schema_name

                       AS VARCHAR(MAX)) + ‘].[‘ +

                     CAST(distinct_view_list.view_name AS VARCHAR(MAX))

                     + ‘];

DROP TABLE temp_results_’ +

                CAST(distinct_view_list.distinct_view_list_id

                AS VARCHAR(MAX)) + ‘;

END TRY

BEGIN CATCH

PRINT ‘‘The following view is broken via DML errors

                   from a SELECT * FROM query: ‘ +

                   CAST(distinct_view_list.view_schema_name

                   AS VARCHAR(MAX)) + ‘.’ +

                   CAST(distinct_view_list.view_name AS VARCHAR(MAX)) + ‘

Error Message: ‘‘ + ERROR_MESSAGE();

IF @@TRANCOUNT > 0

BEGIN

ROLLBACK;

END

END CATCH;’

FROM #distinct_view_list distinct_view_list

WHERE distinct_view_list.view_name NOT IN

(SELECT view_ddl_exception_list.view_name

         FROM #view_ddl_exception_list view_ddl_exception_list);

EXEC sp_executesql @sql_command;

The first part of the script collects a list of views and is a simplified version of what we worked with earlier. Since all that is needed are view names, we can forgo collecting column and data type metadata.

The remainder of the script uses dynamic SQL to attempt a SELECT of the top one row from each view. If it succeeds, then nothing happens. If an error is thrown, then the CATCH block will print the error message and view name for our review when the process completes.

Note that permanent tables are used in the dynamic SQL, which guards against errors if there happens to be XML schema collections defined within the database. While uncommon, I chose here to include rather than omit them. Feel free to adjust these to temporary tables if this will not be an issue within your own database environment.

We can test this code by intentionally building a view and then breaking it:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

ALTER TABLE Person.Person ADD

    favorite_ice_cream_flavor VARCHAR(50) NULL;

GO

UPDATE Person.Person

SET favorite_ice_cream_flavor = ‘Mint Chocolate Chip’;

GO

CREATE VIEW v_employee_ice_cream

AS

SELECT

Person.BusinessEntityID,

Person.FirstName,

Person.LastName,

CAST(Person.favorite_ice_cream_flavor

              AS VARCHAR(25)) AS favorite_ice_cream_flavor

FROM Person.Person;

GO

SELECT TOP 10

favorite_ice_cream_flavor

FROM dbo.v_employee_ice_cream;

This T-SQL creates a new column in Person.Person to store a favorite ice cream flavor. A view is then created that returns a person’s ID, name, and favorite flavor:

 Tracking Underlying Object Changes in Views

Since the view was not created with schema binding, we can alter the Person table with no immediate feedback:

ALTER TABLE Person.Person DROP COLUMN favorite_ice_cream_flavor;

ALTER TABLE Person.Person ADD favorite_ice_cream_flavor XML NULL;

UPDATE Person.Person

SET favorite_ice_cream_flavor =

    ‘<ice_cream>Mint Chocolate Chip</ice_cream>’;

By dropping and recreating the column as XML, we are creating a situation where casting the data on the fly is going to fail. Now let’s run the code we created earlier and test all views in our database for DML errors. To make testing this easier, you can find a stored procedure version here. When we do so, the following information is printed for our review:

 Tracking Underlying Object Changes in Views

Our code caught the error shown above and returned it as a friendly message instead. It also provided some details as to which view broke, allowing us to quickly test and verify for ourselves that it is indeed broken:

SELECT TOP 10

favorite_ice_cream_flavor

FROM dbo.v_employee_ice_cream;

Here is the message that is returned by SQL Server:

 Tracking Underlying Object Changes in Views

This code will catch any DML error that can be thrown by the view, allowing us to identify obscure or unexpected errors that may not be found otherwise. The benefit of this is that we can reduce bugs up-front, long before app changes are deployed!

Conclusion

Views are useful constructs that can save time and improve application maintainability when used effectively. When not schema bound, though, views can become stale and become the source of bugs. The code discussed in this article has allowed us to identify three distinct types of problems:

  1. Views that are not refreshed and are reporting incorrect data types.
  2. Views that contain DDL errors and cannot be refreshed or used.
  3. Views that can be refreshed, but contain DML errors related to data types or data content.

Adding a script that performs these tasks on a regular basis as part of a QA process can allow for views to be tested and validated before they ever see a production environment. Over time, this will reduce bugs and improve code quality as mistakes are caught immediately, rather than days or weeks later.

Let’s block ads! (Why?)

SQL – Simple Talk

Changes, object, Tracking, Underlying, Views
  • Recent Posts

    • NOW, THIS IS WHAT I CALL AVANTE-GARDE!
    • Why the open banking movement is gaining momentum (VB Live)
    • OUR MAGNIFICENT UNIVERSE
    • What to Avoid When Creating an Intranet
    • Is Your Business Ready for the New Generation of Analytics?
  • Categories

  • Archives

    • January 2021
    • December 2020
    • November 2020
    • October 2020
    • September 2020
    • August 2020
    • July 2020
    • June 2020
    • May 2020
    • April 2020
    • March 2020
    • February 2020
    • January 2020
    • December 2019
    • November 2019
    • October 2019
    • September 2019
    • August 2019
    • July 2019
    • June 2019
    • May 2019
    • April 2019
    • March 2019
    • February 2019
    • January 2019
    • December 2018
    • November 2018
    • October 2018
    • September 2018
    • August 2018
    • July 2018
    • June 2018
    • May 2018
    • April 2018
    • March 2018
    • February 2018
    • January 2018
    • December 2017
    • November 2017
    • October 2017
    • September 2017
    • August 2017
    • July 2017
    • June 2017
    • May 2017
    • April 2017
    • March 2017
    • February 2017
    • January 2017
    • December 2016
    • November 2016
    • October 2016
    • September 2016
    • August 2016
    • July 2016
    • June 2016
    • May 2016
    • April 2016
    • March 2016
    • February 2016
    • January 2016
    • December 2015
    • November 2015
    • October 2015
    • September 2015
    • August 2015
    • July 2015
    • June 2015
    • May 2015
    • April 2015
    • March 2015
    • February 2015
    • January 2015
    • December 2014
    • November 2014
© 2021 Business Intelligence Info
Power BI Training | G Com Solutions Limited