Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


NEW T-SQL FEATURES IN SQL SERVER 2016- III [ CREATE OR ALTER ]

In this post we shall check out the new features Microsoft introduced in SQL Server 2016.

New feature – III | [ CREATE OR ALTER ]

Microsoft has introduced a new T-SQL language feature – CREATE OR ALTER. This statement unites the CREATE and ALTER statements and creates object if it does not exist, or alter the object if it is already there.

CREATE OR ALTER can be applied on following objects-

Stored Procedures
Functions
Views
Triggers

Sample example for [ CREATE OR ALTER ]

Generate Sample Data

--

USE [Pawan]
GO
/****** Object:  Table [dbo].[SplitStrings]    Script Date: 1/7/2018 9:36:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SplitStrings](
	[Id] [int] NULL,
	[Vals] [varchar](100) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[SplitStrings] ([Id], [Vals]) VALUES (1, N'a,b')
GO
INSERT [dbo].[SplitStrings] ([Id], [Vals]) VALUES (2, N'a')
GO
INSERT [dbo].[SplitStrings] ([Id], [Vals]) VALUES (3, N'b,3')
GO
INSERT [dbo].[SplitStrings] ([Id], [Vals]) VALUES (4, N'c,u')
GO


--

TableData

Id Vals
1 a,b
2 a
3 b,3
4 c,u

Stored Procedure

--

CREATE OR ALTER PROC TestPawan
AS
BEGIN
		
	SELECT Id , Vals
	FROM [SplitStrings] 
	
END
GO
                                    
--

Output

--

EXEC TestPawan

Id          Vals
----------- --------
1           a,b
2           a
3           b,3
4           c,u

(4 rows affected)

                                    
--

VIEW

--

CREATE OR ALTER VIEW Vw_SplitString
AS
	SELECT Id , Vals
	FROM [SplitStrings] 
GO
                                    
--

Output

--

SELECT * FROM Vw_SplitString

Id          Vals       
----------- ---------- 
1           a,b        
2           a          
3           b,3        
4           c,u        
                       
(4 rows affected)      
                 

                                    
--

FUNCTION

--

CREATE OR ALTER FUNCTION fnSplitstring()
RETURNS INT
AS
BEGIN
   RETURN ( SELECT COUNT(1) FROM [SplitStrings] )
END;
GO
                                    
--

Output

--

SELECT dbo.fnSplitstring()


-----------
4

(1 row affected)

                                    
--

TRIGGER

--

CREATE OR ALTER TRIGGER trTest 
ON [SplitStrings] 
AFTER INSERT, UPDATE 
AS
 RAISERROR ('Pawan is Here - MSBISkills.com', 1, 10);
                                    
--

Output

--

INSERT INTO [SplitStrings] VALUES
(5,'Alpha,Beta')

Pawan is Here - MSBISkills.com
Msg 50000, Level 1, State 10

(1 row affected)

                                    
--

Refer below for more details-

1.https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/11/17/create-or-alter-another-great-language-enhancement-in-sql-server-2016-sp1/

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com