/* */ 90 Days to MCSA - Week 2 - New functions for 2012 |SQL Works

Friday, October 18, 2013

90 Days to MCSA - Week 2 - New functions for 2012

In studying for the Querying Microsoft SQL Server 2012 (70-461) exam I am trying out a couple new items, below are a few that are mentioned in a few different training programs, probably good to know these.

-- NEW FOR 2012
-- IIF(<boolean expr>, <value if true>, <value if false>)
   SELECT IIF(1 = 1, 'True Value', 'False Value')
-- TRY_CONVERT(<expr>, <datatype>, <style>)
   SELECT TRY_CONVERT(DATETIME, '1/1/12330', 101)
-- TRY_PARSE(<string val> AS <datatype> USING <culture>)
   SELECT TRY_PARSE('String' AS INT USING 'cy')
-- OFFSET...FETCH 
   SELECT name
       FROM sys.objects
       OFFSET 5 ROWS
       FETCH NEXT 5 ROWS ONLY

IIF works just like the instant IF in Excel
TRY_CONVERT and TRY_PARSE both perform the same function as CONVERT and PARSE respectively, but these new version will return a NULL instead of raising an error if the conversion or parse fails
OFFSET and FETCH are good for pagination, in the example above it would return rows 6-10
Happy studying!