SQL 2012:USAGE OF
NEW FUNCTIONS
Asanka Padmakumara
Senior BI Engineer
(CAMMS Group)
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 .Net CLR
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 boolean expression 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';
DateTime Result 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.
