Enhanced Execute Keyword in SQL Server 2012

Today, I have provided an article showing you an improved version of the execute keyword in SQL Server 2012. The EXECUTE keyword is used to execute a command string. You cannot change the column name and datatype using the execute keyword in SQL Server 2005/2008. You have to modify the stored procedure respectively. The previous version of SQL Server only has the WITH RECOMPILE option to force a new plan to be re-compiled. The ability to do that in SQL Server 2012dramatically improves this part.  In SQL Server 2012, there is no need to modify a stored procedure. You can change the column name and datatype using the execute keyword. So let's take a look at a practical example. The example is developed in SQL Server 2012 using the SQL Server Management Studio. 

The table looks as in the following:

Create TABLE UserDetail

(

       User_Id int NOT NULL IDENTITY(1,1),     

       FirstName varchar(20),

       LastName varchar(40) NOT NULL,

       Address varchar(255),     

       PRIMARY KEY (User_Id)

)

INSERT INTO UserDetail(FirstName, LastName, Address)

VALUES ('Smith', 'Kumar','Capetown'),

      ('Crown', 'sharma','Sydney'),

      ('Copper', 'verma','Jamaica'),

      ('lee', 'verma','Sydney')

go

Now create a stored procedure for the select statement in SQL Server 2008:

Create PROCEDURE SelectUserDetail

as

begin

select FirstName,LastName, Address from UserDetail

end  

Now use an Execute command to run the stored procedure:

-- SQL Server 2008

execute SelectUserDetail

Output

SQL Server 2012

Now we see how we can change the column name and datatype using an execute keyword in SQL Server 2012. The previous version of SQL Server only has the WITH RECOMPILE option to force a new plan to be re-compiled. To do that in SQL Server 2012 dramatically improves this part. We change the FirstName to Name and the datatype varchar(20) to char(4). Now execute the following code in SQL Server 2012:

execute SelectUserDetail

WITH result SETS 

 ( 

 (   

      Name CHAR(4), 

      Lastname VARCHAR(20), 

     Address varchar(25)    

 ) 

 ); 

Now Press F5 to run the query and see the result:

How to delete duplicate rows in SQL Server (considering there is an identity column in table)?

DELETE
FROM MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)