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)

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

I had written a blog post on An Elegant way to CREATE or ALTER Stored Procedures in SQL Server in One-Go for Easy Maintenance and it received an overwhelming response and also a couple of questions around how to go about doing the same thing for Functions and Views in SQL Server. In this post, let us look at how to CREATE or ALTER User Defined Functions in SQL Server in One-Go so that it is easy to maintain the scripts.

Usual approach that developers tend to take is the easy route and put a check to see if the Function Exists. If Function Exists, drop it and then create it with updated schema/logic. If it does not exist, create the Function. As discussed in the previous post on Stored Procedure, here are the drawbacks of DROP and RE-CREATE/CREATE approach:

DROP FUNCTION will fail if in the following scenarios:

• If there are other Transact-SQL functions or views in the database that reference this function and were created by using SCHEMABINDING
• If there are computed columns, CHECK constraints, or DEFAULT constraints in the database that reference the function

In addition to that, following are few drawbacks of using DROP and CREATE approach:

• Permissions associated with the object, like GRANT EXECUTE, SELECT etc., are lost when we drop and re-create the User Defined Functions.
• If ALTER FUNCTION Script is given during maintenance (to apply changes) then, different Script needs to be given while Creating the User Defined Function (on a new environment or while re-building an existing environment) and while Modifying the User Defined Function.
• If DROP FUNCTION and CREATE FUNCTION approach is used, then all the permissions previously present on the User Defined Function 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 Function 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 Function.

Here is a sample script to CREATE/ATLER a User Defined Function.

IF OBJECT_ID('dbo.udfGetSumOfIntegers') IS NULL -- Check if UDF Exists
	EXEC('CREATE FUNCTION dbo.udfGetSumOfIntegers (@inputInt1 INT) RETURNS INT AS BEGIN RETURN 1 END;')	-- Create Dummy UDF
GO

ALTER FUNCTION dbo.udfGetSumOfIntegers	-- Alter the UDF Always
(
	@inputInt1 INT
	, @inputInt2 INT
)
RETURNS VARCHAR(255)
AS

BEGIN
 
	DECLARE @outputSum INT

	SET @outputSum = @inputInt1 + @inputInt2;

	RETURN ('Number ' + CAST(@outputSum AS VARCHAR(255)))
  
END
GO

SELECT dbo.udfGetSumOfIntegers(1,2) AS OutputSum
GO

-- © Dattatrey Sindol (http://dattatreysindol.com

The output of the above function would be as shown below.

Please note that, in the above example, I have intentionally kept only one input variable in the CREATE FUNCTION statement just to demonstrate that the number of variables can be increased as part of ALTER FUNCTION statement. Similarly, the data type of output can be changed like in the above example, it was changed from INT in CREATE FUNCTION statement to VARCHAR(255) in ALTER FUNCTION statement.

Following are few highlights of this approach:

• This approach cannot be used to ALTER a scalar-valued function in to a table-valued function, or vice versa
• This approach cannot be used to ALTER an inline function in to a multi-statement function, or vice versa
• This approach cannot be used to ALTER a Transact-SQL function to a CLR function or vice-versa

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 🙂

References:

• https://msdn.microsoft.com/en-us/library/ms190290.aspx
• https://msdn.microsoft.com/en-us/library/ms186967.aspx

SSIS – SQL Server Integration Services Connection Manager Tips and Tricks

Connection Managers and Package Configurations are among the most critical components of SQL Server Integration Services (SSIS) Packages. It is very essential to have a thorough understanding of these components while working with SSIS.

In this post, we will see following tips and tricks related to Connection Managers and Package Configurations:

• Adding an “Application Name” property to the connection string
• Creating Two Connection Managers for each Database Connection
• Capturing Connection Manager details in Package Configurations

To continue reading, catch the full article here: SQL Server Integration Services Connection Manager Tips and Tricks.

• SSIS – Quickest way to add an existing SSIS Package to a Project
• SSIS – Update Properties of Multiple Tasks in One-Go
• SSIS – Define you own Keyboard Shortcuts in SSIS Package Designer
• SSIS – Ensure Consistency across Packages using Template Packages