SQL Server 2012 – what’s new for developers?by QArea Expert on May 14, 2012
The final version of Microsoft SQL Server started to be available on April 1, 2012.
There are a huge number of innovations in the new version. This is the review of innovations for the database developers.
New clause OFFSET and FETCH to limit the number of returned lines
Finally, normal paging appeared in SQL Server. Now search for the second set of 10 records (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 much larger text.
The OVER clause has been expanded with window’s support functions
It is possible to restrict line within the section much more with the ROWS or RANGE, indicating the start and end points.
Next analytic functions have been added
For example, I have a need to count the four prices for candles (open, high, low, close) in one of the projects. Now the query turns out to be trivial:
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
The sequences has appeared
Another long-awaited improvement. What a hard time we have to pass through while implementing the numbering of the documents from the beginning of the year. Now there is no problem.
THROW have come to replace the RAISERROR statement
New 14 functions
The conversion functions:
Time and date functions:
These are definitely just aesthetic improvements.
Now, SQL Server provides storage of files and documents in special tables, at the same time they can be accessed from Windows applications in the same manner as if they were stored in the file system without making any changes to the Windows application.
The statistical semantic search.
Together with the FileTable function statistical semantic search provides a deep analysis of unstructured documents.
Semantic search is based on an existing component of the full-text search in SQL Server and implement new scenarios, providing search not by keywords, but by the value of the document.
For example, with the index of key phrases or classification a set of technical documentation can be constructed. Or to find the best suitable resume to the job description with the similarity index of documents.