New Analytic Functions in SQL Server 2012

Analytic Functions

First_Value Function
It returns the first value in ordered set of values.

Syntax:
First_Value([scalar_expression])
OVER([partition_by_clause] order_by_clause[rows_range_clause])

•Scalar_expression: can be a column, sub query, or other expression that results in a single value.
•OVER: Specify the order of the rows.
•ORDER BY: Provide sort order for the records.
•Partition By: Partition by clause is optional part of First_Value function and if you don’t use it all the records of the result-set will be considered as a part of single group or a single partition and then ranking functions are applied.

Example:
We create a table named student
Create table student
(
Sid int,
Sname varchar(30),
Marks int
)
Insert the values into table
Insert into student values(1,akil,320)
Insert into student values(1,balu,520)
Insert into student values(1,chini,580)
Insert into student values(1,devid,570)
Insert into student values(1,etil,471)
Assume the following query:
Select *,first_value(Marks) OVER (order by sid) as First_valueResult from student

Output:

Last_Value Function
It returns the last value in ordered set of values in database.

Syntax:
Last_Value([scalar_expression])
OVER([partition_by_clause] order_by_clause[rows_range_clause])

•Scalar_expression: can be a column, sub query, or other expression that results in a single value.
•OVER: Specify the order of the rows.
•ORDER BY: Provide sort order for the records.
•Partition By: Partition by clause is optional part of Last_Value function and if you don’t use it all the records of the result-set will be considered as a part of single group or a single partition and then ranking functions are applied.

Example:
Select *,last_value(Marks) OVER (order by sid) as First_valueResult from student

Output:

New String Functions in SQL Server 2012

String Functions

FORMAT Function
The format function is used to format how a field is to be displayed.

Syntax:
Format(Column_name,format)
Format converts the first argument to a specified format and returns the string value.

Example:
Declare @d DateTime=’20/03/2011’
Select Format(@d,’d’,’en-US’) as US_Result

Output:
This function formats the datetime. this function is used in the server .NET Framework and CLR. The function will solve many formatting issues for developers.

CONCAT Function
It’s the same concatenate function that we use in Excel; it will concatenate two or more strings to make it single string. It implicitly converts all arguments to string types.

Syntax:
CONCAT(string_value1,string_value2,- - - - ,[string_valueN])
String_value: A String value to concatenate to the other values.

Example:
Select concat(‘rock’,’-’,’smith’) as Concatestring

Output:

This function expects at least two parameters and a maximum of 254 parameters.

New Date and time Functions in SQL Server 2012

Date and time Functions

DATEFROMPARTS Function
The DATEFROMPARTS function returns a date value for the specified year, month, and day.

Syntax:
DATEFROMPARTS(year,month,day)
The above function contains 3 parameters as follows
• Year: Integer expression specifying a year.
• Month: Integer expression specifying a month from 1 to 12.
• Day: Integer expression specifying a day.

Example:
Declare @year as int=2014
Declare @month as int=02
Declare @day as int=20
Select DATEFROMPARTS(@year,@month,@day)

Output:

TIMEFROMPARTS Function

The TIMEFROMPARTS function returns time values for the specified time and with the specified precision.

Syntax:
TIMEFROMPARTS(hour, minute, seconds, fractions, precision)
If the arguments are invalid, then an error is raised. If any of the parameters are null, then null is returned.

Example:
Declare @hour as int=5
Declare @minute as int=46
Declare @seconds as int=20
Declare @fractions as int=0
Declare @precision as int=0
Select TIMEFROMPARTS(@hour, @minute, @seconds, @fractions, @precision)

DATETIMEFROMPARTS Function
The DATETIMEFROMPARTS function return a DateTime value for the specified date and time.

Syntax:
DATETIMEFROMPARTS(year, month, day, hour, minute, seconds, milliseconds)
If the arguments are invalid, then the error is raised. If any of the parameters are null, null is returned.

Example:
Declare @year as int=2014
Declare @month as int=12
Declare @day as int=20
Declare @hour as int=8
Declare @minute as int=46
Declare @seconds as int=0
Declare @milliseconds as int=0
Select DATETIMEFROMPARTS (@Year, @Month, @Day, @hour , @minute , @seconds, @milliseconds)

Output:


Eomonth Function
The Eomonth function returns the last day of the month that contains the specified date.

Syntax:
The syntax of the "Month" built-in date function is as follows:
MONTH ( startdate [,month_to_add ] )

The above function contains 2 parameters.
• "startdate" parameter can be an expression specifying the date for which to return the last day of the month.
• "month_to_add" is optional.

Example:
Select getdate()asCurrentDate
Go
SelectEomonth(getdate())asMonth
Go
SelectEomonth('09/12/2012',2)as Month
Go
SelectEomonth('09/12/2012')asMonth

Output:

New Conversion Functions in SQL Server 2012

Conversion Functions

Parse Function
This function converts a string to Numeric, date and time formats. It will raise error if translation isn’t possible. That time you may still use CAST or CONVERT for general conversions. It depends on the presence of the CLR.

Syntax:  To demonstrate this new conversion function the following defines the syntax as follows

PARSE(string_value AS data_type[Using culture])

The above function contains 3 parameters.
• String_value: String value to parse into the Numeric, Date and Time format.
• Data_type: returns datatype, numeric or datetime type.
• Culture: it is optional string that identifies the culture in which string_value is formatted. If it is not specified, then it takes the language of the current session.

Example 1: Convert string to datetime

Select parse(‘06/08/2014’ AS DateTime2) as Datetimevalue

Output:

Example 2: Convert string to int

Select parse(‘100.000’ AS int) as intvalue

Output:


Try_Parse Function
This function works similarly to the parse function except if the conversion is successfully then it will return the value as the specified data type. Otherwise it will return a NULL value.

Syntax:

TRY_PARSE(string_value AS date_type[Using Culture])

Example: Using Try_Parse

Select Try_Parse(‘Sunday, 05 august 2012’ AS Datetime2 Using ‘en-US’) as [TryParseFunctionResult]

Output:


Try_Convert Function
This is similar to the covert function except it returns null when the conversion fails.

Syntax:

TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

Example 1: Convertion failed and gives null value

SELECT
CASE WHEN TRY_CONVERT(float, 'test') IS NULL
THEN 'Cast failed'
ELSE 'Cast succeeded'
END AS Result;
GO

Output:

Example 2: The Expression must be in the Excepted format.

SET DATEFORMAT mdy;
SELECT TRY_CONVERT(datetime2, '12/31/2010') AS Result;
GO

Output:

New Logical Functions in SQL Server 2012

Logical Functions

IIF( ) Function:
            The IFF function is used for check a condition. Suppose A>B, In this condition ‘A’ is the first expression and ‘B’ is the second expression. If the first expression evaluates to TRUE then the first value is displayed, if not the second value is displayed.

Syntax:

IIF (booleanexpression, true_value, false_value)

Example: Depends on student marks, it displays result as pass or fail.

Declare @M int
Set @M=45
Select iif(@M>35,’Pass’,’Fail’)

In this example student marks is @M=45; If the condition is true it returns ‘Pass’ or Result is false it returns ‘Fail’.

Output:

Choose() Function
This function returns a value out of a list based on its index number. You can think of it as an array king of thing. The index number here starts from 1.

Syntax:

Choose(index, value1, value2, value3, value4 ......... valueN)


The above function contains two parameters,
• Index: index is an integer expression that represents an index into the list of the items. The list index always stats at 1.
• Value: List of values of any data type.

Example 1: If an index inside the bound of the array

Declare @indexnumber int
Set @indexnumber=4
Select choose(@indexnumber,55,42,35,25,26,35,88,45,22)

In the preceding example we use index=4. It will start at 1. Choose() returns 25 as output since 25 is present at @index location 4.

Output:

Example 2: If an index exceeds the bound of the array it returns NULL

Declare @indexnumber int
Set @indexnumber=10
Select choose(@indexnumber,55,42,35,25,26,35,88,45,22)

Output:

In this example we use index=10. It will start at 1.Choose() returns Null as output because in the item list the index value exceeds the bounds of the array; the last index=9