Tips ‘N’ Tricks – T-SQL – An Elegant way to CREATE or ALTER User Defined Views in One-Go in SQL Server for Easy Maintenance

We have seen the approach for creating or altering a Stored Procedure in SQL Server in One-Go for Easy Maintenance. In this post, let us look at how to CREATE or ALTER User Defined Views in SQL Server in One-Go so that it is easy to maintain the scripts.

Usual approach taken by developers is to check to see if the View Exists. If View Exists, drop it and then create it with updated schema/logic. If it does not exist, create the View. As discussed in the previous posts on Stored Procedure and Functions, here are the drawbacks of DROP and RE-CREATE/CREATE approach:

• Permissions associated with the object, like GRANT SELECT etc., are lost when we drop and re-create the User Defined Views.
• If ALTER VIEW Script is given during maintenance (to apply changes) then, different Script needs to be given while Creating the User Defined View (on a new environment or while re-building an existing environment) and while Modifying the User Defined View.
• If DROP VIEW and CREATE VIEW approach is used, then all the permissions previously present on the User Defined View need to be given again with the help of necessary scripts.

To address the drawbacks highlighted above, there is an elegant way of Creating and Modifying a User Defined View similar to Stored Procedures using a Single T-SQL Script and the same script can be executed multiple times on any environment – whether it is a new environment or an existing environment and you are applying some changes to the View.

Here is a sample script to CREATE/ALTER a User Defined View.

IF OBJECT_ID('dbo.vwEmployee') IS NULL
	EXEC('CREATE VIEW dbo.vwEmployee AS SELECT 1 AS ID;') -- Create dummy/empty View
GO

ALTER VIEW dbo.vwEmployee
AS
	SELECT
		EmployeeKey
		, FirstName
		, LastName
		, Title
		, EmailAddress
	FROM dbo.DimEmployee
GO

SELECT TOP 5 * FROM dbo.vwEmployee
GO
-- © Dattatrey Sindol (http://dattatreysindol.com

Let me know if you know of a better way to handle this. Would be happy to learn different ways of doing it.

Take a look at the other Tips and Tricks in this Series on Tips, Tricks, Techniques, and Shortcuts to Improve Productivity, and Design and Coding Skills.

Until next time, Happy Learning 🙂

Further Reading: Dattatrey Sindol (http://dattatreysindol.com)