Share via

CREATE OR ALTER - another great language enhancement in SQL Server 2016 SP1

  • Article

We are happy to announce that SQL Server 2016 SP1 and SQL Server v.Next have new T-SQL language statement - CREATE [OR ALTER]. This statement combines CREATE and ALTER statements and creates object if it does not exist, or alter it if it is already there. CREATE OR ALTER can be applied on the following object:

This is one of the most voted language feature requests on SQL Server connect site with more than 500 votes. A code that uses CREATE OR ALTER is shown in the following example:

 create or alter procedure procTest
as
begin
 print (1)
end;
go
create or alter function fnTest()
returns int
as
begin
 return(1)
end;
go
create or alter view vwTest
as
 select 1 as col;
go
create or alter trigger trTest 
on Product 
after insert, update 
as
 RAISERROR ('We love CREATE OR ALTER!', 1, 10);

We hope that this statement would help you to to write easier T-SQL code.

EDIT (11/17/2016): This and other SQL Server 2016 SP1 topics are also available in the SQL Server Tiger blog.

Comments

  • Anonymous
    November 17, 2016
    view sql source havn't '' OR ALTER "...
  • Anonymous
    November 17, 2016
    Hi Jovan!This is so awesome!!Niels
  • Anonymous
    November 18, 2016
    Will this be made available to SQL Server 2014 in SP3 (if there will be)?
  • Anonymous
    November 18, 2016
    Oh man, I've been asking for this for about 15yrs. and it's finally here. This is WONDERFUL. Now backport it to a couple other versions and we'll be golden.
    • Anonymous
      November 28, 2016
      ^ At least SQL Server 2012... And 2008 R2...
    • Anonymous
      November 29, 2016
      Hahahaha
  • Anonymous
    November 22, 2016
    Woot! This is really a nice to have.. I second the back-porting it a couple of versions :)
  • Anonymous
    November 23, 2016
    does alter preserve permissions?
    • Anonymous
      December 02, 2016
      Hello George, it does not change existing permissions for existing objects.
  • Anonymous
    November 23, 2016
    Finally!Common sense arrives in the SQL world, bit by bit!
  • Anonymous
    November 24, 2016
    This is in oracle since old age, and MS is providing now
  • Anonymous
    November 25, 2016
    I hope it won't take another 15 years to see an easy to use INSERT OR UPDATE statement! The MERGE synthax is orrible.
    • Anonymous
      December 01, 2016
      The comment has been removed
  • Anonymous
    November 28, 2016
    Welcome feature that's been needed for a while. No more if exists(...) ... Love it.
  • Anonymous
    November 30, 2016
    It does not seem to accept the CREATE or ALTER format in the SSDT database projects and shows error when I checkin the procedure with the new format.Can you confirm if it has been part of SSDT as well?
  • Anonymous
    November 30, 2016
    I hope the "Modify" and "Script... to" menu options will automatically add " or alter" to existing objects.
  • Anonymous
    December 01, 2016
    We need this in SQL Server 2005, 2008, 2012 and 2014 too.
    • Anonymous
      December 15, 2016
      Duh!SQL Server 2005 is no longer supported.And SQL Server is out of Standard support.Perhaps you should focus on upgrading SQL Server to the later editions instead of getting Microsoft to backport it...No?
  • Anonymous
    December 02, 2016
    Very late but welcome, thanks.
  • Anonymous
    January 17, 2017
    Finally, awesome stuff!
  • Anonymous
    January 20, 2017
    GreatNot sure why it took so long but it's a definite logical improvement
  • Anonymous
    January 31, 2017
    Excellent to know. Its great when these small but time saving features are introduced.
  • Anonymous
    February 06, 2017
    Gosh...I have to wait for few more years before my organisation upgrade their DB from 2012 to 2016. phew...
  • Anonymous
    February 28, 2017
    Should we be getting red underlines from IntelliSense when using create or alter on an object that exists? We're using SQL 2016 SP1 and SSMS 2016 version 13.0.16106.4.
  • Anonymous
    May 16, 2017
    Thanks
  • Anonymous
    November 28, 2017
    It's a great feature... but who's idea was it to add it in a service pack???Now we have no idea if we can run it in based on compatibility mode or not...130 with SP1 can run it, and 130 without SP1 can't... what a mess...