SQL 2012:Usage o f New Functions

Asanka Padmakumara Senior BI Engineer (CAMMS Group) SQL 2012:Usage of New Functions

Finding the trend??? (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 THEN 'DOWN' ELSE 'NA' END AS Trand FROM CTELEFT OUTER JOIN CTE AS PREVCTE ON PREVCTE.rownum = CTE.rownum– 1

Finding the trend??? (2012) SELECT CASE WHEN OrderQty-LAG(OrderQty) OVER(order by WorkOrderID)>0 THEN 'UP' WHEN OrderQty-LAG(OrderQty) OVER(order by WorkOrderID)<0 THEN 'DOWN' ELSE 'NA' END AS Trand FROM [AdventureWorks].[Production].[WorkOrder]

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. • About Performance ??? • I used LAG to rewrite a self-join query and besides being much smaller and simpler, the query time dropped from 2.6 sec to 1 sec. (Or from 40 sec to 1 sec if you count the bad behavior of the query optimizer). Obviously this is just one anecdote, but the performance difference was shocking and highly convincing to me. – agentnega • http://stackoverflow.com/questions/12953231/lag-and-lead-functions

Paging a result set • 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

Paging a result set SELECT TransactionID , ProductID , TransactionDate , Quantity , ActualCost FROM Production.TransactionHistory ORDER BY TransactionDate DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY • 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. Will Show in Demo

Convert ‘100.000’ as INT SELECT CONVERT(INT, '100.000') AS MyINT; • Conversion failed when converting the varchar value '100.000' to data type int. SELECT CAST('100.000' AS INT) AS MyINT • Conversion failed when converting the varchar value '100.000' to data type int.

Convert ‘100.000’ as INT SELECT PARSE('100.000' AS INT) AS MyINT • PARSE function tries to parse a string and returns the value that is parsed from that string, if it can. • CONVERT function tries to convert the string to a specified data type. • can only convert a string to INT,NUMERIC and DATETIME • Uses the .NetCLR • performance overhead and requires the presence of .NET CLR on the database Server

Convert ‘A100.000’ as INT SELECT CONVERT(INT, 'A100.000') AS MyINT; • Conversion failed when converting the varchar value 'A100.000' to data type int. SELECT PARSE('A100.000' AS INT) AS MyINT • Error converting string value 'A100.000' into data type int using culture ''.

Convert ‘A100.000’ as INT SELECT TRY_PARSE('A100.000' AS INT) AS MyINT • Return NULL SELECT TRY_CONVERT(INT, 'A100.000') AS MyINT; • Return NULL • Still uses the .Net CLR.

IIF SELECT IIF([SalesYTD]>[SalesLastYear],'TRUE','FALSE') FROM [AdventureWorks].[Sales].[SalesPerson] • Immediate If aka Inline If • Takes a booleanexpression and returns one of two values. • Shorthand for CASE. • Has the same limitations as CASE • Can only be nested to 10 levels

CHOOSE SELECT CHOOSE ( 3, 'Manager', 'Director', 'Developer', 'Tester' ) AS Result; • Returns a value from a list based on a specified index. • If the specified index is not in the list NULL is returned. • Returns the data type based on data type precedence.

Today is 2014/02/19. But what is end of this month? • Get first date of next month, then add minus 1 day to that date SELECT DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, ‘2014/02/19’) + 1, 0))

Today is 2014/02/19. But what is end of this month? SELECT EOMONTH( '2014/02/19') • Can specify a month_to_add argument to increment or decrement result.

Concatenate string with null select NULL+'Yahoo' AS NullYahoo • Return NULL select CONCAT(NULL,'Yahoo') AS NullYahoo • Return Yahoo • NULLs are automatically converted to empty strings. • Can pass other data types for concatenation.

FROMPARTS Functions SELECT DATEFROMPARTS ( 2010, 12, 31 ) AS Result • DATEFROMPARTS ( year, month, day) • DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision ) • DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds ) • DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision ) • SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute ) • TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )

Format DECLARE @d DATETIME = GETDATE(); SELECT FORMAT( @d, 'dd/MM/yyyy', 'en-US' ) AS 'DateTime Result' ,FORMAT(123456789,'###-##-####') AS 'Custom Number Result'; • DateTimeResult Custom Number Result 27/09/2012 123-45-6789

THROW 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; • Raises an exception and transfers execution to a CATCH block of a TRY…CATCH construct • Reduces the need to use RAISERROR in TRY/CATCH blocks. • Can provide custom error messages.

SQL 2012:Usage o f New Functions