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)
Posted on October 12, 2015, in Best Practices, Code Snippets, Development Tips, SQL Server, SQL Server BI, T-SQL, T-SQL Snippets, T-SQL Tips, Tips 'N' Tricks and tagged Best Practices, Databases, Microsoft SQL Server, SQL Server, T-SQL, Tips, Tips and Tricks, Transact-SQL, Tricks. Bookmark the permalink. Leave a comment.