How to avoid broken views in SQL Server?

SQL Views will break if you change the schema of the underlying table.

• Create a table as shown below

CREATE TABLE [dbo].[Employee](
 [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
 [EmployeeFirstName] [varchar](50) NOT NULL,
 [EmployeeLastName] [varchar](50) NOT NULL,
 [EmployeeDOB] [date] NOT NULL,
 [CreatedDateTime] [datetime] NOT NULL,
 [ModifiedDateTime] [datetime] NOT NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
 [EmployeeID] ASC
)
GO

• Populate the table with some records
  
  
• Create a View as below

CREATE VIEW [dbo].[EmployeeView]
AS
SELECT        EmployeeID, EmployeeFirstName, EmployeeLastName, EmployeeDOB, CreatedDateTime, ModifiedDateTime
FROM            dbo.Employee

GO

• Do a select from the view.

SELECT * FROM [dbo].[EmployeeView]

• Everything looks fine now.
  
• Include a EmployeeMiddleName column in the Employee table and do the select from the view again.

• Can you see the difference? EmployeeMiddleName column doesn’t appear but the EmployeeMiddleName data appears under the EmployeeLastName column and EmployeeDOB has the EmployeeLastName data and so son.

What happened here? SQL Server doesn’t bind the schema to the view and therefore when the schema changes the view is not affected. To mitigate this issue
a) Don’t use * instead denote the column names in the view.

b) Use Schema binding when creating a view but it will keep the schema of table and view intact and cannot change the underlying table schema until the view definition is modified or view is dropped otherwise the DB engine will throw an error. Schema Binding will improve stability and performance but also increases developers work.

Technorati Tags: SQL Server,Views