.NET Development, IT Blog,

SQL Server 2012 – what’s new for developers?

2 m read
QArea Expert
QArea Expert Marketing Manager
May 14, 2012
Share on
Reading Time: 2 minutes

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.

Share on
Privacy Preference Center
Your Privacy

When you visit any web site, it may store or retrieve information on your browser, mostly in the form of cookies. This information might be about you, your preferences or your device and is mostly used to make the site work as you expect it to. The information does not usually directly identify you, but it can give you a more personalized web experience. Because we respect your right to privacy, you can choose not to allow some types of cookies. Click on the different category headings to find out more and change our default settings. However, blocking some types of cookies may impact your experience of the site and the services we are able to offer.

Strictly Necessary Cookies Always Active

These cookies are necessary for the website to function and cannot be switched off in our systems. They are usually only set in response to actions made by you which amount to a request for services, such as setting your privacy preferences, logging in or filling in forms.

You can set your browser to block or alert you about these cookies, but some parts of the site will not then work. These cookies do not store any personally identifiable information.

Performance Cookies

These cookies allow us to count visits and traffic sources so we can measure and improve the performance of our site. They help us to know which pages are the most and least popular and see how visitors move around the site.

All information these cookies collect is aggregated and therefore anonymous. If you do not allow these cookies we will not know when you have visited our site, and will not be able to monitor its performance.

Cookies used

Google Analytics
Functional Cookies

When you visit any web site, it may store or retrieve information on your browser, mostly in the form of cookies. This information might be about you, your preferences or your device and is mostly used to make the site work as you expect it to. The information does not usually directly identify you, but it can give you a more personalized web experience. Because we respect your right to privacy, you can choose not to allow some types of cookies. Click on the different category headings to find out more and change our default settings. However, blocking some types of cookies may impact your experience of the site and the services we are able to offer.

Cookies used

Test Second Test
Targeting Cookies

When you visit any web site, it may store or retrieve information on your browser, mostly in the form of cookies. This information might be about you, your preferences or your device and is mostly used to make the site work as you expect it to. The information does not usually directly identify you, but it can give you a more personalized web experience. Because we respect your right to privacy, you can choose not to allow some types of cookies. Click on the different category headings to find out more and change our default settings. However, blocking some types of cookies may impact your experience of the site and the services we are able to offer.

More Information

When you visit any web site, it may store or retrieve information on your browser, mostly in the form of cookies. This information might be about you, your preferences or your device and is mostly used to make the site work as you expect it to. The information does not usually directly identify you, but it can give you a more personalized web experience. Because we respect your right to privacy, you can choose not to allow some types of cookies. Click on the different category headings to find out more and change our default settings. However, blocking some types of cookies may impact your experience of the site and the services we are able to offer.