DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
  1. DZone
  2. Data Engineering
  3. Databases
  4. Avoid Broken Views in SQL Server with Schemabinding

Avoid Broken Views in SQL Server with Schemabinding

Anders Abel user avatar by
Anders Abel
·
Jan. 13, 13 · Interview
Like (0)
Save
Tweet
Share
4.96K Views

Join the DZone community and get the full member experience.

Join For Free

SQL Server views can get really weird when an underlying table is updated. I’ll show some examples and also the one must do for views to avoid problems.

The broken views problem is something I discovered when working on a production system. When I first observed it I was shocked. When I dug deeper into it I was even more shocked at the pure evilness this behaviour could cause. But let’s take it from the start with a simple table of colours and a view on top of it to start with.

CREATE TABLE [dbo].[Colours] (
    [Id]     INT           IDENTITY (1, 1) NOT NULL,
    [Colour] NVARCHAR (10) NOT NULL,
    CONSTRAINT [PK_Colours] PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO
CREATE VIEW [dbo].[BadView]
	AS SELECT * FROM Colours

Populate the table with some data and test the view.

SELECT * FROM BadView

Id          Colour
----------- ----------
1           Red
2           Green
3           Blue
4           White
5           Black

Now, let’s break havoc to the view. I’ll recreate the table with another column RGB which is inserted right after the Id column. The Colour column is renamed to ColourName. Before running the next test, I’ve inserted the appropriate RGB values in the new column.

SELECT * FROM BadView

Id          Colour
----------- ------
1           FF0000
2           00FF00
3           0000FF
4           FFFFFF
5           000000

This is what I consider weird behaviour. The output of the view still contains two columns with the names Id and Colour. That would be good – if it contained the right data. It doesn’t. The data is taken from the second column of the underlying table. If you’re not scared enough by this I have some more, even worse things to show…

When misaligned column names and data is discovered it must of course be handled. All problems can be solved in different ways. One way I found in a production system was a select clause like this.

SELECT Id, Colour AS RGB FROM BadView

It works. It produces output with the right column name for the data. My feelings and personal thoughts of that approach are not suitable for printing out.

Id          RGB
----------- ------
1           FF0000
2           00FF00
3           0000FF
4           FFFFFF
5           000000

It was very obvious at this point that the system was brittle and that I would have to be extremely careful during the necessary clean up. Scripting out the existing definition of a view and rerunning it should be safe. Scripting a database object is a way to capture the current definition of it, to be able to restore it. It’s an no-op, right. RIGHT? So that’s what I did (on a backup copy of the DB).

ALTER VIEW [dbo].[BadView]
	AS SELECT * FROM Colours

Then I once again tested the view.

SELECT * FROM BadView

Id          RGB    ColourName
----------- ------ ----------
1           FF0000 Red
2           00FF00 Green
3           0000FF Blue
4           FFFFFF White
5           000000 Black

I now was not only scared. I was horrified. The database is not just showing some really weird behaviour. It keeps information about the view, that is not included in the scripted definition of the object. Recreating it with exactly the same script won’t give the same result.

To me this is a horror story. Walking down the road of legacy code on the way to maintenance hell.

Time to look at the cure for this weirdness.

The SCHEMABINDING cure

When I created the BadView I also created a GoodView that applied WITH SCHEMABINDING. When a view is schema bound all objects (tables, functions) that the view is dependent on are locked against modification.

First I tried to create the GoodView just like BadView.

CREATE VIEW [dbo].[GoodView]
	WITH SCHEMABINDING
	AS SELECT * FROM Colours

That failed.

Msg 1054, Level 15, State 6, Procedure GoodView, Line 3
Syntax '*' is not allowed in schema-bound objects.

With schema binding, SQL server requires a more specific and safer select statement.

CREATE VIEW [dbo].[GoodView]
	WITH SCHEMABINDING
	AS SELECT Id, Colour FROM dbo.Colours

When doing the rename of the Colour column to ColourName the rename operation fails.

Msg 5074, Level 16, State 1, Line 3
The object 'GoodView' is dependent on column 'ColourName'.
Msg 4922, Level 16, State 9, Line 3
ALTER TABLE DROP COLUMN ColourName failed because one or more objects access this column.

The message is from the SQL server engine itself. The schemabound view is protected from breaking changes of the underlying table regardless of what client tool is used.

Tooling Support is Required

I found an article: SCHEMABINDING – use or not to use that discusses the consequences of schema binding. I think that the points are valid, but it fails to mention that good tooling support can handle the problems by automatically dropping and recreating any views that are schema bound to a table that is changed. The database deployment support in Visual Studio 2010 and 2012 (much improved in 2012) is an example of a tool that handles this.

What about stored procedures?

So far this post has been about views. Probably more common than views are stored procedures. They at least doesn’t use stale column definitions.

CREATE PROCEDURE [dbo].[MyProcedure]
AS
	SELECT * FROM Colours

When the Colours table is updated, the stored procedure output is adjusted to include the new column and the new name of the renamed column.

Recommendations

My recommendation is to schema bind all views as the default. If it becomes to hard to change the underlying tables when there are multiple schema bound views a better tool is needed. Dropping the schema binding is not a good solution. I prefer views over stored procedures for simple queries as they can be reused in other queries. There used to be a performance benefit for stored procedures but in modern SQL server versions all queries are compiled and the query plan cached.

sql Database

Published at DZone with permission of Anders Abel, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • What Was the Question Again, ChatGPT?
  • How to Submit a Post to DZone
  • How To Check Docker Images for Vulnerabilities
  • A Brief Overview of the Spring Cloud Framework

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: