There are two types of Functions in SQL Server
- System Function: These are the inbuilt functions present in SQL Server. User can’t make any change in these functions i.e Min(), Max(), UPPER()
- User Defined Functions: SQL Server allows user to create their own functions. These functions are known as User Defined Functions
SQL Server has many built in functions and that has been categorized into multiple categories i.e Aggregate Function ,String Function ,Date and Time Functions. Its very difficult to memorize all system function. If you have a rich experience then you must be remembering almost all important function.If you are new to SQL Server then this post will help you to find a place where all system functions are stored.Below I have attached the snapshot , you can also follow the same path to get system functions.
All System Function has been categorized in 13 different categories , below image can help you to find all those 13 categories in your database.
by clicking on + button you can find all functions which belongs the a particular category.If you will do mouse over on any function a message pop up will come which will tell what that function will return.
I will be explaining each and every System function with an example in future.
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:
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.
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:
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:
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:
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
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
