TL;DR :

• Paging got simpler and more efficient
• Sequences have been introduced; better performance for auto-generated IDs and easier to have IDs unique across tables
• FileTables have been introduced : building upon the FileStream feature now we can have non-transactional access to files stored in the DB as a windows share along with transactional access via T-SQL

Lengthier version :

SQL Server 2012, in my opinion does not come with earth-shaking changes but comes with performance improvements, feature improvements and a some new features.

First of all, Management Studio has the same engine as Visual Studio which means you get a nice WPF experience, better font rendering and CTRL-scroll quick zoom-in/zoom-out.

Let’s say you want to retrieve data from the database in a paged way (that means chunks of data of a requested size or smaller). Typically you would write this in SQL Server 2008 R2 or older :

DECLARE	@Offset		AS INT = 6
DECLARE @PageSize	AS INT = 5

SELECT	Id,
		Name
FROM
(
	SELECT	Id,
			Name,
			ROW_NUMBER()	OVER (ORDER BY Id)	AS	RowNumber
	FROM	Users
) UsersSelection

WHERE	UsersSelection.RowNumber >  @Offset
	AND	UsersSelection.RowNumber <= @Offset + @PageSize

In SQL Server 2012 the T-SQL syntax has been updated introducing keywords that facilitate a simpler and more efficient paging, keywords such as OFFSET, FETCH, NEXT ROWS and ONLY. A script that would retrieve the same data would be :

DECLARE	@Offset		AS INT = 6
DECLARE @PageSize	AS INT = 5

SELECT		Id,
			Name
FROM		Users
ORDER BY	Id
OFFSET		@Offset		ROWS
FETCH NEXT	@PageSize	ROWS ONLY

Observe the simpler, clearer syntax. Also, considering that the subselect has been eliminated (the subselect was required because the ROW_NUMBER column could not be addressed in the same select – for the WHERE clause), also the query cost was improved :

2008 :

2012 :

Read more »