sql server 2012 vs 2008r2 [Resolved]

Posted by Sriharim under Sql Server on 7/15/2015 | Points: 10 | Views : 643 | Status : [Member] | Replies : 3
In terms Store Procedure, what difference/advantages are there in sql server 2012 over sql server 2008R2 ?



Posted by: Bandi on: 7/16/2015 [Member] [MVP] Platinum | Points: 50


there is no specific differences only in terms of SPs, but you can see the below for your understanding...

Mainly the differences between MsSQL 2008 R2 to 2012 at low-level are:

1. enhanced features for querying are introduces in MSSQL 2012.
FORMAT(), CONCAT(), TRY_CONVERT() and OLAP window functions such as LAG(), LEAD(), LAST_VALUE() and FIRST_VALUE() functions
you can look into the top features of Denali ( MSSQL 2012) online.

2. there was no casting for money column in SQL Server 2008 whereas MSSQL 2012 have the solution for cast a money field, this is done by FORMAT() function.

DECLARE @money money = '215600';
SELECT FORMAT ( @money, 'C') AS MoneyFormatinUSDollars;


3. Pagination can be done in SQL Server 2012 by using two keywords (OFFSET, FETCH NEXT) with Order By Clause whereas the code is lengthy and complex in SQL SERVER 2008 R2 . For your reference, http://stackoverflow.com/questions/2244322/how-to-do-pagination-in-sql-server-2008?rq=1

4. SSIS-wise, SQL Server 2012 is having SSDT tool whereas SQL server 2008 R2 is having BIDS( Business Intelligence Development Studio). SQL Server 2012 provides you 'Integration Catalog' for deploying packages onto server.

5. Sequence objects are introduced in MSSQL 2012. you look for this online..

6. Error Handling is also very comfortable in MSSQL 2012. They have introduced THROW

begin try
-- The code where error has occurred.
end try

begin catch
-- throw error to the client
end catch

refer this for high-level differences

Mark This Response as Answer

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Manub22 on: 9/22/2015 [Member] Starter | Points: 50


In terms of Stored Procedures the only change or new feature I could think of in 2012 compared to 2008 was while calling them you could use WITH RESULT SETS option.

You can change the column name, and datatype casting upto some limit without having change the Stored Procedure, like this:

EXEC [dbo].[uspGetEmployeeManagers] @BusinessEntityID = 100
Level INT,
BusinessID INT,
EmpFirstName VARCHAR(50),
EmpLastName VARCHAR(50),
OrgNode VARCHAR(20),
ManagerFirstName VARCHAR(50),
ManagerLastName VARCHAR(50)

Check here: http://sqlwithmanoj.com/2012/04/12/sql-server-2012-a-k-a-denali-new-feature-with-result-sets/

Check all the other new features available in SQL 2012 here: http://sqlwithmanoj.com/denali-2012/

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriharim on: 7/20/2015 [Member] Starter | Points: 25

I couldn't understand below Virtual window file table command point there in touchstone link,
New virtual Windows FileTable commands in T-SQL lets you manage a folder like a table of documents, but still have external control over the contents: UPDATE C:\Docs\*.* SET ReadOnly = 1 WHERE Author = ‘Bob’ AND Created < ’20100101';)


Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response