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

TOP 5 Exciting New Features of SQL Server 2012

Microsoft understands the importance of sql server and is continually striving to make powerful yet easy-to-use tool for Sqlserver developers and end users.
In this Part I will discuss exciting new features related to developer and SQL Server enhancement.

#1 Column Store Indexes
SQL Server 2012 introduced a new feature Column store indexes that can be used to improve query performance from 10x to 100x times. The xVelocity technology makes the columnstore index more efficient than a traditional index.

In a regular index, all indexed data from each row is kept together on a single page, and the data in each column is spread across all pages in an index. In a columnstore index, the data from each column is kept together so that each data page contains data only from a single column. In addition, the indexed data for each column is compressed (means many columns contains repetitive values).The compression ration can be very high and It also reduces the number of pages in memory.

Building a columnstore index is simple. You use the same index creation syntax and specify the keyword COLUMNSTORE. But once you add a columnstore index to a table, the table becomes read-only, so inserts, updates, and deletes are not allowed. If you need to insert or update rows, you can disable the index, make the modifications, and rebuild the columnstore index.

It is greatly reducing I/O and memory utilization on large queries and creating aggregate tables.

Syntax:
CREATE NONCLUSTERED COLUMNSTORE INDEX ON (Col1,Col2........Coln)

Example:
CREATE NONCLUSTERED INDEX INDEX_1 ON EmployeeDetail(ID, Name)

#2 Sequence Objects 
A Sequence is just an object that is a counter same as similar functionality of an identity column, there is an interesting option to utilize called Sequence. Sequence is a user-defined object that creates a sequence number. A good example of its use would be to increment values in a table, based a trigger.

Syntax:
CREATE SEQUENCE [schema_name . ] sequence_name
[ AS [ built_in_integer_type | user-defined_integer_type ] ]

[ START WITH ]
[ INCREMENT BY ]
[ { MINVALUE [ ] } | { NO MINVALUE } ]
[ { MAXVALUE [ ] } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [ ] } | { NO CACHE } ]
[ ; ]

• START WITH: Starting number in the sequence
• INCREMENT BY: The incrementing value of the sequence
• MINVALUE: The minimum value the sequence can produce.
• MAXVALUE: The maximum value the sequence can produce.
• CYCLE: If the MAXVALUE is set with the CYCLE, when the MAXVALUE is reached the sequence will cycle to the MINVALUE and start again.
• CACHE: If a CACHE argument is provided, SQL Server will cache (store in memory) the amount of values specified.

#3 Error Handling
If you have programming in languages like C# or other similar languages then you are probably know how to handle errors by using try, catch and throw statements. Transact-SQL also gives you this option to find an exception using a try/catch block.
In SQL Server 2005/2008, RAISERROR has remained the only mechanism for generating your own errors. In SQL Server 2012, a new THROW statement is used to raise exceptions in your T-SQL code instead of RAISERROR.

Example:
We divide a number by zero:

In SQL Server 2005/2008, if you want to re-throw an error in a catch block of TRY CATCH statement, you need to use RAISERROR with ERROR_MESSAGE() AND ERROR_SEVERITY().

BEGIN TRY
DECLARE @VALUE INT
SET @VALUE = 12/ 0
END TRY
BEGIN CATCH
DECLARE @ErrorMessage nvarchar(4000), @ErrorSeverity int
SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY()
RAISERROR ( @ErrorMessage, @ErrorSeverity, 1 )
END CATCH

But in SQL Server 2012 you can use only a THROW statement to re-throw an error in a catch block of a TRY CATCH statement.

BEGIN TRY
DECLARE @VALUE INT
SET @VALUE = 12/ 0
END TRY
BEGIN CATCH
throw
END CATCH

#4 New T-SQL Functions
There are many new functions added to SQL Server 2012 as shown below

Logical Functions
• CHOOSE (Transact-SQL)
• IIF (Transact-SQL)       ....................................More>>

Conversion Functions
• PARSE (Transact-SQL)
• TRY_PARSE (Transact-SQL)
• TRY_CONVERT (Transact-SQL)   ..........................More>>

Date and time Functions
• DATEFROMPARTS Function
• TIMEFROMPARTS Function
• DATETIMEFROMPARTS Function
• EMONTH Function
....... and so on                     .............................More>>

String Functions
• FORMAT (Transact-SQL)
• CONCAT (Transact-SQL)   ..................................More>>

Analytic Functions
• First_Value Function
• Last_Value Function        ..................................More>>

#5 Pagination
In the earlier versions of SQL Server, if you use a Gridview then you set its pagination property. However if you want to do this from the server side then you need to use the row_number() function and supply the specific range of rows and also retrieve the current page data from the database using a temporary table. The ORDER BY OFFSET & FETCH NEXT ONLY keywords are one of the major features introduced in SQL Server 2012.

Briefly explain as follows

Creating a Table in SQL Server

create table Userdetails
(id int identity (1,1),
firstname varchar(30),
lastname varchar(30))

insert into Userdetails values('John','K')
insert into Userdetails values('Crown','P')
insert into Userdetails values('Lee','B')
insert into Userdetails values('Khan','S')
insert into Userdetails values('Amith','N')
insert into Userdetails values('Nelson','M')
insert into Userdetails values('Maro','T')
insert into Userdetails values('Alijibit','R')
insert into Userdetails values('Francko','S')

select * from Userdetails

The table looks like as follows


To get rows in between 4 to 8

In SQL Server 2008, we have been doing this data paging by writting a complex query as follows.

SELECT *
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY id) AS number, *
FROM Userdetails) AS TempTable
WHERE number > 4 and number <= 8 

 In SQL Server 2012,now using OFFSET and FETCH NEXT keywords to get data paging as follows.

SELECT * 
FROM Userdetails 
ORDER BY id 
OFFSET 4 ROWS
FETCH NEXT 4 ROWS ONLY; 

In the above example, we used OFFSET 4 ROWS, so SQL will skip first 4 records from the result and display the next 4 records in the defined order.