SQL Server 2012 - what's new for the developer?

On April 1, 2012, the final version of Microsoft SQL Server 2012 became available.

The new version has a large number of innovations.

In this article I would like to dwell in more detail on the review of a number of innovations for a database developer.



1. New OFFSET and FETCH sentences to limit the number of rows returned

Finally, normal paging appeared in SQL Server.

Now the request for getting the second 10 records of the set (2nd page) looks like this

SELECT DepartmentID, Name, GroupName FROM HumanResources.Department ORDER BY DepartmentID OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY; 

In SQL 2005/2008, we had to write something like this:

 WITH CTE AS ( SELECT DepartmentID, Name, GroupName, ROW_NUMBER() OVER (ORDER BY DepartmentID) AS RowNum FROM HumanResources.Department ) SELECT DepartmentID, Name, GroupName FROM CTE WHERE RowNum BETWEEN 10 AND 19 ORDER BY RowNum 

Learn more here: msdn.microsoft.com/ru-ru/library/ms188385.aspx#Offset



2. In the OVER clause, support for window functions has been extended.

Using the ROWS or RANGE clauses, you can further restrict the rows within the section, indicating the start and end points.

For example, a query to display a cumulative total:

 SELECT DepartmentID, SaleDate, SUM(SalesYTD) OVER (PARTITION BY DepartmentID ORDER BY SaleDate ROWS UNBOUNDED PRECEDING) AS Total FROM dbo.Sales 

Learn more here: msdn.microsoft.com/ru-ru/library/ms189461.aspx



3. Added analytic functions

• FIRST_VALUE
• LAST_VALUE
• CUME_DIST
• PERCENTILE_DISC
• PERCENT_RANK
• PERCENTILE_CONT
• LEAD
• Lag

For example, in one of the projects I need to calculate four prices for a candle (open, high, low, close).

Now the query turns into a trivial one:

 SELECT MIN(Ask) OVER (PARTITION BY Pair, Candle) AS Low, MAX(Ask) OVER (PARTITION BY Pair, Candle) AS High, FIRST_VALUE(Ask) OVER (PARTITION BY Pair, Candle) AS Open, LAST_VALUE(Ask) OVER (PARTITION BY Pair, Candle) AS Close FROM dbo.Quotes WHERE Pair='EURUSD' and Candle = 100 

4. Sequences appeared (SEQUENCE)

Another welcome improvement. What kind of crutches did not have to fence in order to implement the numbering of documents from the beginning of the year.

Example of use:

 CREATE SEQUENCE Schema.SequenceName AS int START WITH 1 INCREMENT BY 1 ; DECLARE @NextID int ; SET @NextID = NEXT VALUE FOR Schema.SequenceName; 

')

5. Replaced RAISERROR instruction THROW

 THROW 51000, 'The record does not exist.', 1; BEGIN TRY SET @a = 10 / 0 END TRY BEGIN CATCH PRINT 'In catch block.'; THROW; END CATCH; 

6. 14 new features

Conversion functions:

• PARSE
• TRY_CONVERT
• TRY_PARSE

Date and time functions:

• DATEFROMPARTS
• DATETIME2FROMPARTS
• DATETIMEFROMPARTS
• DATETIMEOFFSETFROMPARTS
• EOMONTH
• SMALLDATETIMEFROMPARTS
• TIMEFROMPARTS

Logical functions:

• CHOOSE
• IIF

String functions:

• CONCAT
• FORMAT

I would refer this to aesthetic improvements.

For example, earlier the last day of the month was given as follows:

 SET @LastDayOfMonth = dateadd(month,1,dateadd(day,1-day(@d),@d))-1 

In SQL Server 2012:

 SET @LastDayOfMonth = EOMONTH (@d) 

Or, instead of:

  CASE WHEN (@a > @b) THEN 'TRUE' ELSE 'FALSE' END 

Now you can write:

  IIF ( @a > @b, 'TRUE', 'FALSE' ) 

7. FileTable Tables

Now SQL Server provides storage of files and documents in special tables, while accessing them is possible from Windows applications in the same way as if they were stored in the file system, without making any changes to Windows applications.

Learn more here: msdn.microsoft.com/ru-ru/library/ff929144.aspx



8. Statistical semantic search

Together with the FileTable function, statistical semantic search provides in-depth analysis of unstructured documents.

Semantic search is built on the basis of the existing full-text search component in SQL Server and implements new scenarios, providing search not by keywords, but by the value of the document.

For example, by the index of key phrases, you can build a classification or a set of technical documentation. Or, using the document similarity index, find a resume best suited to the job description.

Learn more here: msdn.microsoft.com/ru-ru/library/gg492075.aspx