New T-SQL programming enhancements in SQL Server 2012

In SQL Server 2012 and 2014 Microsoft introduced many new features and enhancements. In fact there were 14 new in-built functions introduced in SQL Server 2012.  However I noticed that for some reason I don’t see many people using them.  In my company we upgraded all our servers to 2014 from 2008R2 almost a year back but I see none of the developers using them or even aware.  Also in last 6 months I interviewed many people and one of the question I asked to candidates that are you aware of any new features of SQL Server 2012/14?  While most of the people talked about the changes in SSIS, project deployment etc but very few knew about the 14 new in-built functions introduced in SQL Server 2012. 

So I thought of writing about these new in-built functions. In this post I’m not going to write the details of these function.  This is just going to be a reminder that now we have 14 more in-built functions that can help us in writing SQL code. 

Conversion functions

1. PARSE (Transact-SQL) : PARSE function converts the string expression to the requested data type. And if it fails to translate then raises an exception.

2. TRY_CONVERT (Transact-SQL): Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.

3. TRY_PARSE (Transact-SQL): Returns the result of an expression, translated to the requested data type, or null if the cast fails

You might wonder what is the difference between PARSE and CONVERT or may be PARSE and TRY_PARSE; I’ll try to explain that in my next few posts.

 

Date and time functions

4. DATEFROMPARTS (Transact-SQL): Returns a date value for the specified year, month, and day.

5. DATETIME2FROMPARTS (Transact-SQL): Returns a datetime2 value for the specified date and time and with the specified precision.

6. DATETIMEFROMPARTS (Transact-SQL): Returns a datetime value for the specified date and time.

7. DATETIMEOFFSETFROMPARTS (Transact-SQL): Returns a datetimeoffset value for the specified date and time and with the specified offsets and precision.

8. EOMONTH (Transact-SQL): Returns the last day of the month that contains the specified date, with an optional offset.

9. SMALLDATETIMEFROMPARTS (Transact-SQL): Returns a smalldatetime value for the specified date and time.

10. TIMEFROMPARTS (Transact-SQL): Returns a time value for the specified time and with the specified precision.

 

Logical functions

11. CHOOSE (Transact-SQL): Returns the item at the specified index from a list of values.

12. IIF (Transact-SQL): Returns one of two values, depending on whether the Boolean expression evaluates to true or false.

 

String functions

13. CONCAT (Transact-SQL): Returns a string that is the result of concatenating two or more string values.

14. FORMAT (Transact-SQL): Returns a value formatted with the specified format and optional culture.

To know more about each of these new functions you can click on the names of the functions.  In my future posts I’ll try explain each of these functions in details with some examples and situation where they can be useful.

 

To know more about the new features introduced in the SQL Server please follow below links:

What's New in SQL Server 2012
What's New in SQL Server 2014
A Sneak Peek into the Future: What's New in SQL Server 2016

Thanks
Mangal Pardeshi

ORDER BY Enhancements in SQL Server

Today I am going to talk about one new feature that is been introduced in the SQL Server Denali.   And that is:  one interesting enhancement in ORDER BY clause.  Now with ORDER BY clause you can also specify the OFFSET and FETCH options. 

From Books Online of Denali:
OFFSET: Specifies the number of rows to skip before it starts to return rows from the query expression. The value can be an integer constant or expression that is greater than or equal to zero.

FETCH: Specifies the number of rows to return after the OFFSET clause has been processed. The value can be an integer constant or expression that is greater than or equal to one.

This is how the new syntax of ORDER BY clause looks like in SQL Server Denali:
------------------------------------------------------------------------------------------------------
ORDER BY order_by_expression
    [ COLLATE collation_name ]
    [ ASC | DESC ]
    [ ,...n ]
[ <offset_fetch> ]
<offset_fetch> ::=
{
    OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
    [
      FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY
    ]
}
------------------------------------------------------------------------------------------------------

Take a look at following example:

------------------------------------------------------------------------------------------------------
USE AdventureWorks
GO
SELECT VendorID
    ,Name
FROM Purchasing.Vendor
ORDER BY VendorID OFFSET 5 ROWS
GO
------------------------------------------------------------------------------------------------------

Output:

In the example I wrote 5 as OFFSET.  You can see in the output 1st five rows are skipped and we got VendorIds starting from 6.  Here SQL Server 1st orders the data on the Column specified in ORDER BY clause(i.e. VendorID).  The next query uses the clause OFFSET 5 ROWS to skip the first 5 rows and return all remaining rows.

Now lets see the example of FETCH:

------------------------------------------------------------------------------------------------------
USE AdventureWorks
GO
SELECT VendorID
    ,Name
FROM Purchasing.Vendor
ORDER BY VendorID OFFSET 5 ROWS
    FETCH NEXT 3 ROWS ONLY
GO
------------------------------------------------------------------------------------------------------

Output:

As you can SQL Server has fetched only 3 rows and that also after skipping 1st five rows.  That is because I specified 5 as OFFSET and 3 as FETCH NEXT.  Here FETCH NEXT 3 ROWS ONLY to limit the rows returned to 3 rows from the sorted result set.

Also:
1. offset_row_count_expression can be a variable, parameter, or constant scalar subquery. When a subquery is used, it cannot reference any columns defined in the outer query scope. That is, it cannot be correlated with the outer query.

2. ROW and ROWS are synonyms and are provided for ANSI compatibility.

3. In query execution plans, the offset row count value is displayed in the Offset attribute of the TOP query operator.

You can find all this information and more on: ORDER BY Clause (Transact-SQL)

Thanks
Mangal