SQL Server 2012 – what’s new for developers?

sql server 2012The 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

 

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

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:

  • TRY_PARSE
  • TRY_CONVERT
  • PARSE

Time and date functions:

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

Logical functions:

  • IIF
  • CHOOSE

String functions:

  • FORMAT
  • CONCAT

These are definitely just aesthetic improvements.

FileTables

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.


QArea has 11 years experience in providing high-quality services in Microsoft website development (e.g. Business Intelligence, AQL Server, Silverlight, Visual Basic .NET, Sharepoint and more). Whether you are in healthcare, games, accounting or other domain of business, QArea team can find suitable solution for you.


VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)

    Tagged:


    Facebook Comments

    comments

    'SQL Server 2012 – what’s new for developers?' have no comments

    Be the first to comment this post!

    Would you like to share your thoughts?

    Your email address will not be published.

    Software development and outsourcing blog by QArea © 2016

    Яндекс.Метрика