Well, i have decided to write on technologies finally, it was one of the good sessions from SQL meetup which was held at Microsoft srilanka on this month. I learnt some useful and simple sql functions which is present in sql server 2012.
SQL Server 2012 introduces many new rich built-in functions that offer more solutions to common T-SQL challenges.Some of the main functions which am going to write about are,
Conversion functions
- PARSE (Transact-SQL)
- TRY_PARSE (Transact-SQL)
- TRY_CONVERT (Transact-SQL)
Date and time functions
- DATEFROMPARTS (Transact-SQL)
- DATETIMEFROMPARTS (Transact-SQL)
- DATETIME2FROMPARTS (Transact-SQL)
- SMALLDATETIMEFROMPARTS (Transact-SQL)
- DATETIMEOFFSETFROMPARTS (Transact-SQL)
- TIMEFROMPARTS (Transact-SQL)
- EOMONTH (Transact-SQL)
Logical functions
- CHOOSE (Transact-SQL)
- IIF (Transact-SQL)
String functions
- CONCAT (Transact-SQL)
- FORMAT (Transact-SQL)
In sql 2008 , in order to find a trend we were using CTE functions, For example to find the trend in 2008,
WITH CTE AS (
SELECT rownum = ROW_NUMBER() OVER (order by WorkOrderID),OrderQtyFROM
[AdventureWorks].[Production].[WorkOrder])
SELECT CASEWHEN CTE.OrderQty-PREVCTE.OrderQty >0 THEN 'UP'
WHEN CTE.OrderQty-PREVCTE.OrderQty <0 p="" then=""> ELSE 'NA'
END AS Trand
FROM CTELEFT OUTER JOIN CTE AS PREVCTE ON PREVCTE.rownum = CTE.rownum – 1
but SQL 2012 made it more easier, I have used AdventureWorkds2012 database,
LAG and LEAD
No longer need to use a self-join or CTE.
LAG: Access data from previous rows in the result set.
LEAD: Access data from future rows in the result set
Advantage:
Increase in performance
Paging Data:
Again in order to do paging with sql 2008 we have to write a cte function, for example to get the first 20 rows from a table, the function will be as follows,
Start with 0, Page Size:20
WITH Paging_CTE AS ( SELECT TransactionID , ProductID , TransactionDate , Quantity , ActualCost , ROW_NUMBER() OVER (ORDER BY TransactionDate DESC) AS RowNumber FROM Production.TransactionHistory ) SELECT TransactionID , ProductID , TransactionDate , Quantity , ActualCost FROM Paging_CTE WHERE RowNumber > 0 AND RowNumber <= 20
SQL 2012
OFFSET & FETCH
OFFSET provides a starting row from which to display the result set.
FETCH instructs the query to display the number of rows you want in your result set from the OFFSET point
FIRST_VALUE and LAST_VALUE
FIRST_VALUE: Retrieves the first value in a partition.
LAST_VALUE: Retrieves the last value in a partition.
PARSE:
Parse a value and returns the result of an expression, translated to specified data type. It will raise error if translation isn’t possible. Since this function relies on CLR. It has better performance.
Syntax:
PARSE ( string_value AS data_type [ USING culture ] )
PARSE(), expects three parameters,
string_value – String value to parse into the specified data type.
data_type – Return data type, numeric or datetime type
culture – A language (English, Japanese, Spanish, Danish, French etc.) which will used by SQL Server to interpret data.
example
SELECT PARSE('18/02/2014' AS datetime2 USING 'en-GB') AS [Using PARSE Function]
GO
OUTPUT
2012-02-18 00:00:00.000
TRY_PARSE – TRY_PARSE() function is similar to PARSE() with only difference is that it returns NULL if the conversion is not possible.
Syntax:
TRY_PARSE ( string_value AS data_type [ USING culture ] )
TRY_PARSE(), expects three parameters,
string_value – String value to parse into the specified data type.
data_type – Return data type, numeric or datetime type
culture – A language (English, Japanese, Spanish, Danish, French etc.) which will used by SQL Server to interpret data.
SELECT TRY_PARSE(’10.30′ AS INT) AS [Using TRY_PARSE Function]
GO
OUPUT:
NULL
Since 10.20 is not an integer value PARSE() fails, and TRY_PARSE() returns NULL.
TRY_CONVERT – Checks whether conversion from one type to other is possible or not. It transforms the source data into the target type if the conversion is possible. Returns NULL if the conversion is not possible.
Syntax:
TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )
TRY_CONVERT(), expects three parameters,
data_type – The data type into which to cast expression.
expression – The value to be cast.
style – Optional integer expression that specifies how the TRY_CONVERT function is to translate expression.
Example,
SELECT TRY_CONVERT(datetime2, ’02/18/2012′, 103) AS [Using TRY_CONVERT Function];
GO
OUTPUT:
NULL
As you can see the both queries fail conversion but only first query that is using COVERT function throws error whereas query that is using TRY_CONVERT returns NULL indicating that conversion fails.
Date and time functions
These functions are mainly to manipulate data related to dates.
DATEFROMPARTS – The DATEFROMPARTS function, returns a date value with the date part set to the specified year, specified month and the specified day, and the time portion set to the default.
DATEFROMPARTS ( year, month, day )
DATEFROMPARTS(), expects three parameters,
Year – Integer expression specifying a year.
Month – Integer expression specifying a month, from 1 to 12.
Day – Integer expression specifying a day.
DATETIMEFROMPARTS – The DATETIMEFROMPARTS function, returns full datetime value with the date time part set to the specified year, specified month, specified day, specified hour, specified minute, specified second and the specified milliseconds.
Syntax:
DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )
DATETIMEFROMPARTS – The DATETIMEFROMPARTS function, returns full datetime value with the date time part set to the specified year, specified month, specified day, specified hour, specified minute, specified second and the specified milliseconds.
Syntax:
DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )
DATETIMEFROMPARTS(), expects seven parameters,
Year – Integer expression specifying a year.
Month – Integer expression specifying a month, from 1 to 12.
Day – Integer expression specifying a day.
Hour – Integer expression specifying hours.
Minute – Integer expression specifying minutes.
Seconds – Integer expression specifying seconds.
Milliseconds – Integer expression specifying milliseconds.
DATETIME2FROMPARTS – The DATETIME2FROMPARTS function, returns full datetime2 value with the date time part set to the specified year, specified month, specified day, specified hour, specified minute, specified second and the specified precision.
Syntax:
DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )
DATETIME2FROMPARTS(), expects eight parameters,
Year – Integer expression specifying a year.
Month – Integer expression specifying a month, from 1 to 12.
Day – Integer expression specifying a day.
Hour – Integer expression specifying hours.
Minute – Integer expression specifying minutes.
Seconds – Integer expression specifying seconds.
Fractions – Integer expression specifying fractions.
Precision – Integer literal specifying the precision of the datetime2 value to be returned.
SMALLDATETIMEFROMPARTS – The SMALLDATETIMEFROMPARTS function, returns full datetime value with the date time part set to the specified year, specified month, specified day, specified hour and the specified minute.
Syntax:
SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )
SMALLDATETIMEFROMPARTS(), expects five parameters,
Year – Integer expression specifying a year.
Month – Integer expression specifying a month, from 1 to 12.
Day – Integer expression specifying a day.
Hour – Integer expression specifying hours.
Minute – Integer expression specifying minutes.
DATETIMEOFFSETFROMPARTS – The DATETIMEOFFSETFROMPARTS function returns a full datetimeoffset. The OFFSET argument is basically used to represent the time zone offset value hour and minutes.
Syntax:
DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )
DATETIMEOFFSETFROMPARTS(), expects ten parameters,
Year – Integer expression specifying a year.
Month – Integer expression specifying a month, from 1 to 12.
Day – Integer expression specifying a day.
Hour – Integer expression specifying hours.
Minute – Integer expression specifying minutes.
Seconds – Integer expression specifying seconds.
Fractions – Integer expression specifying fractions.
Hour_offset – Integer expression specifying the hour portion of the time zone offset.
Minute_offset – Integer expression specifying the minute portion of the time zone offset.
Precision – Integer literal specifying the precision of the datetimeoffset value to be returned.
TIMEFROMPARTS – The TIMEFROMPARTS function, returns a full time value as shown in the below query result. It is important to note that the fractions argument actually depends on the precision argument.
When fractions have a value of 5 and precision has a value of 1, then the value of fractions represents 5/10 of a second. When fractions have a value of 50 and precision has a value of 2, then the value of fractions represents 50/100 of a second. When fractions have a value of 500 and precision has a value of 3, then the value of fractions represents 500/1000 of a second.
Syntax:
TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )
TIMEFROMPARTS(), expects five parameters,
Hour – Integer expression specifying hours.
Minute – Integer expression specifying minutes.
Seconds – Integer expression specifying seconds.
Fractions – Integer expression specifying fractions.
Precision – Integer literal specifying the precision of the time value to be returned.
EOMONTH –
This is very handy function with sql server 2012. This function takes two parameters first being start_date which is mandatory and the second one is Month_to_add which is optional. This function will return the last day of the month (also termed as EOM or end of the month) for the given date.
EOMONTH ( start_date [, month_to_add ] )
EOMONTH(), expects two parameters,
Start_date – Date expression specifying the date for which to return the last day of the month.
Month_to_add – Optional integer expression specifying the number of months to add to start_date.
Example:
Execute the following:
DECLARE @STARTDATE DATETIME = GETDATE()
SELECT EOMONTH (@STARTDATE, 1) AS [Using EOMONTH Function]
GO
Here is the result set:
Using EOMONTH Function
2014-02-28
Logical Functions:
CHOOSE – This function can be used to return the value out of a list based on its index number (Note: Index no. here starts from 1).
Syntax:
CHOOSE ( index, val_1, val_2 [, val_n ] )
CHOOSE(), expects two parameters,
Index – Is an integer expression that represents a 1-based index into the list of the items following it.
Value – List of values of any data type.
IIF – Conditional function which will return the value based on the condition you specified as the first argument. This is similar to SSRS IIF expression.
Syntax:
IIF ( boolean_expression, true_value, false_value )
IIF(), expects three parameters,
Boolean_expression – A valid Boolean expression.
True_value – Value to return if boolean_expression evaluates to true.
False_value – Value to return if boolean_expression evaluates to false.
String Functions:
CONCAT – 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.
example,
select CONCAT(NULL,'Yahoo') AS NullYahoo
FORMAT – Function for locale-aware formatting of date/time and number values as strings.
Syntax:
FORMAT ( value, format [, culture ] )
FORMAT(), expects at three parameters,
Value – Expression of a supported data type to format.
Format – nvarchar format pattern.
Culture – Optional nvarchar argument specifying a culture.
THROW -
Raises an exception and transfers execution to a CATCH block of a try/catch statement.
Reduces the need to use RAISERROR in TRY/CATCH blocks.
Can provide custom error messages.
Example,
BEGIN TRY
SELECT CONVERT(INT,'A1000')
END TRY
BEGIN CATCH
THROW 51000, 'Can not convert this string(Throw an error by Asanka)', 1;
END CATCH;
These are the various SQL built in functions that are quite useful for any SQL lovers!
