Site navigation

Social networks

Contact us

Welcome innovative MS SQL Server 2012

On the 1st of April 2012 the final edition of Microsoft SQL Server 2012 was released.
This new edition has a great deal of innovations.
And in this post I'd like to make a detailed overview of those innovations that can be of high importance for a DB developer.

1. New OFFSET and FETCH clauses used to limit the number of rows fetched
At long last SQL Server has a proper paging.
Now a query for fetching the second set of 10 entries (the 2nd page) looks like this:
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID
    OFFSET 10 ROWS
    FETCH NEXT 10 ROWS ONLY;
 Just compare with SQL 2005/2008, where it was as follows:
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
 You can get more details here: msdn.microsoft.com/en-us/library/ms188385.aspx#Offset

2. An OVER clause has now an extended support for window functions
Using ROWS or RANGE clauses you can further limit the rows within the partition, specifying start and end points.
Here is an example of query for getting a progressive total:
SELECT  DepartmentID,
    SaleDate,
    SUM(SalesYTD) OVER (PARTITION BY DepartmentID ORDER BY SaleDate ROWS UNBOUNDED PRECEDING) AS Total
FROM dbo.Sales
You can get more details here: msdn.microsoft.com/en-us/library/ms189461.aspx

3. Some analytical functions have been added:
    FIRST_VALUE
    LAST_VALUE
    CUME_DIST
    PERCENTILE_DISC
    PERCENT_RANK
    PERCENTILE_CONT
    LEAD
    LAG

For instance, I have to calculate four prices for candles (open, high, low, close).
And with the innovations introduced the query has turned to be a simple thing:
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 have been introduced
It's one of the most welcome innovations. Just think about all those somersaults that you had to perform to implement the numbering of documents starting from the beginning of a year.
Here is an example of usage:
CREATE SEQUENCE Schema.SequenceName
    AS int
    START WITH 1
    INCREMENT BY 1 ;

DECLARE <hh user=NextID> int ;
SET <hh user=NextID> = NEXT VALUE FOR Schema.SequenceName;
5. Instead of RAISERROR here comes THROW
THROW 51000, 'The record does not exist.', 1;

BEGIN TRY
    SET <hh user=a> = 10 / 0
END TRY
BEGIN CATCH
    PRINT 'In catch block.';
    THROW;
END CATCH;
6. And here are 14 more functions

Convert Functions:
    PARSE
    TRY_CONVERT
    TRY_PARSE

Date and Time Functions:
    DATEFROMPARTS
    DATETIME2FROMPARTS
    DATETIMEFROMPARTS
    DATETIMEOFFSETFROMPARTS
    EOMONTH
    SMALLDATETIMEFROMPARTS
    TIMEFROMPARTS

Logic Functions:
    CHOOSE
    IIF

String Functions:
    CONCAT
    FORMAT

I would consider this one an aesthetic improvement.
As far as you can remember we used to get the last day of the month like this:
SET <hh user=LastDayOfMonth> = dateadd(month,1,dateadd(day,1-day(<hh user=d>),<hh user=d>))-1
And in SQL Server 2012:
SET <hh user=LastDayOfMonth> = EOMONTH (<hh user=d>)
Or instead of: 
CASE WHEN  (<hh user=a> > <hh user=b>) THEN 'TRUE' ELSE 'FALSE' END
you can just write: 
IIF ( <hh user=a> > <hh user=b>, 'TRUE', 'FALSE' )
7. FileTable
Now SQL Server gives opportunity to store files and documents in special tables. What is worth mentioning is the fact that you can access them from Windows applications as if they are stored in the file system, without making any changes to your Windows applications.
You can get more details here: msdn.microsoft.com/en-us/library/ff929144.aspx

8. Statistical Semantic Search
Together with FileTable feature Statistical Semantic Search provides deep insight into unstructured documents.
Semantic search is based upon the existing full-text search feature in SQL Server, but enables some new scenarios and provides searching not by keywords, but by the meaning of the document.
For example, using the index of key phrases you can build a classification or a code of some technical documentation. Or you can use the document similarity index to identify resumes that perfectly match a job description.
You can get more details here: msdn.microsoft.com/en-us/library/gg492075.aspx

Andrey Kudievskiy

ABOUT

WEEZLABS

Not just another web and mobile apps development company!

WeezLabs is about dreaming big and helping our clients reach their full potential.

LEARN MORE

VISIT OUR OFFICE:

1848 Lincoln Blvd, Suite #100,
Santa Monica, CA 90404

TELL US HOW

WE CAN HELP

Call us 310.776.6234 or complete the form below.