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 :
Recent Comments