SqlHints.com

Microsoft just 14 Months back released Microsoft Sql Server 2012, and in such short span of time Microsoft announced it’s plan for the release of next version of Sql Server. This shows Microsoft continued commitment and focus to mitigate the challenges posed by the competitors and new array of DataBase Technology.

On June 3, 2013 at TechEd North America , Microsoft has officially made announcement about Sql Server’s new Version i.e. Sql Server 2014. But they have not mentioned when the trial version will be avilable for evaluation. But we can register at MicroSoft Site to get notified when the trial version is ready for evaluation.

Some of the key features of Sql Server 2014 which I like about are as below. For detailed information on the features and extensive feature list please keep visiting the MicroSoft Site. And at this link they have couple of white papers and the Sql Server 2014 Datasheet too. I will write more articles on it as and when I explore/get to know more about it and when MS provides the Trial version for Evaluation.

1) In-memory, online transaction processing (OLTP).

It greatly improves transaction processing speeds and reduces latency by virtue of working with in-memory data, as opposed to disk-based data. This technology is a result of a Project that has been in works for several years and project code-named ‘Hekaton‘.

2) Column Store Indexes:

Update-able Clustered Column Store Indexes.

3) Buffer Pool Extension to SSDs:

This can definitely improve query performance by allowing the use of non-volatile solid-state drives to reduce SQLServer memory pressure with no risk of data loss.

4) High Availability

SQL Server 2014 will have Enhanced AlwaysON and it is going to support up to 8 Secondaries. This AlwaysOn high Availability technology is based on the principals of database mirroring has been introduced in Sql Server 2012, where it was supporting max 4 Secondaries.  With AlwaysOn, users will be able to fail over multiple databases in groups instead of individually. Also, secondary copies will be readable, and can be used for database backups.

5) Extended Online Operations

SQL Server 2014 is going to Support Online Index Operations on the Partition Level, which will be a very nice feature for Large Databases where table data is partitioned.

6) Hadoop Connectivity

7) Simplified Cloud Back-Up, Cloud Disaster Recovery, Extended on-Premises apps to the cloud, New Cloud Migration wizard to easily move on-premises Sql Server to Windows Azure, Sql Server in a Windows Azure Virtual Machine and so. on.

CHOOSE is one of the new built-in logical function introduced as a Part of Sql Server 2012. It returns the value at the specified index position from the list of values.

SYNTAX: CHOOSE ( index, val_1, val_2 [, val_n ] )

Example 1: CHOOSE Basic Example

SELECT CHOOSE(1,'Spring','Summer','Autumn','Winter')
SELECT CHOOSE(2,'Spring','Summer','Autumn','Winter')
SELECT CHOOSE(3,'Spring','Summer','Autumn','Winter')
SELECT CHOOSE(4,'Spring','Summer','Autumn','Winter')

Result:

Example 2: CHOOSE function with variables as index and values.

DECLARE @SeasonId INT = 2, @Season1 Varchar(10) = 'Spring',
 @Season2 Varchar(10) = 'Summer',@Season3 Varchar(10) = 'Autumn',
 @Season4 Varchar(10) = 'Winter'

SELECT CHOOSE(@SeasonId, @Season1, @Season2, @Season3, @Season4)

Result: Summer

Example 3: CHOOSE will return NULL if INDEX position is outside the range of values.

SELECT CHOOSE(-10,'Spring','Summer','Autumn','Winter')
SELECT CHOOSE(0,'Spring','Summer','Autumn','Winter')
SELECT CHOOSE(5,'Spring','Summer','Autumn','Winter')

Result:

Example 4: If index value is numeric, it will be implicitly converted to INT.

SELECT CHOOSE(3.1,'Spring','Summer','Autumn','Winter')
SELECT CHOOSE(3.85,'Spring','Summer','Autumn','Winter')

Result:

Example 5: CHOOSE function Return Datatype will be the one with highest precedence from the Datatypes of the list of values

SELECT CHOOSE(3, 40.58, 50, 60, 70)

Result: 60.00

Example 6: CHOOSE function with Index Type as Varchar

SELECT CHOOSE('TEST','Spring','Summer','Autumn','Winter')

Result:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘TEST’ to data type int.

SELECT CHOOSE('3','Spring','Summer','Autumn','Winter')

Result: Autumn

You may also like to read other LOGICAL FUNCTION IIF() introduced in SQL SERVER 2012

Please correct me, if my understanding is wrong. Comments are always welcome.

IIF() is one of the new built-in logical function introduced as a Part of Sql Server 2012. IIF() is the shorthand way of writing CASE statement or IF-ELSE statement. In-fact if we see the execution plan the IIF() function internally translates to a CASE statement.

IIF() function takes three arguments, first argument should be a Boolean expression otherwise it raises an exception. If Boolean expression evaluates to TRUE then Second argument will be the result otherwise Third argument will be the result. This functions result’s Datatype will be the one with highest precedence from the Datatypes of Second and Third Argument.

Syntax: IIF ( boolean_expression, true_value, false_value )

Now let us understand this function with examples:

Example 1: Comparing two Integer numbers

SELECT IIF(1 > 10, 'TRUE', 'FALSE' )

Result: FALSE

Example 2: Comparing two Integer Variables

DECLARE @Marks INT = 60, @Minimum INT = 35
SELECT IIF(@Marks >= @Minimum, 'PASS', 'FAIL' )

Result: PASS

Example 3: Comparing Strings

DECLARE @NAME Varchar(50) = 'Basav'
SELECT IIF(@NAME IN ('Basav','Kalpana'), 
                    'Likes SQL', 'Likes ORACLE' )

Result: Likes SQL

Example 4: Result’s Datatype will be the one with highest precedence from the Datatypes of Second and Third Argument.

SELECT IIF(1 > 10, 1.5, 40)

Result: 40.0

Example 5: Nested IIF() Statement.

DECLARE @Percentage AS NUMERIC(5,2) = 71
SELECT IIF(@Percentage >= 70, 'Distinction', 
        IIF(@Percentage>=35 AND @Percentage<70, 'Pass', 'Fail'))

Result: Pass

Note: As IIF() statement internally translates to an CASE statement, so the Max Nesting level is 10.

Example 6: IIF() with both return value arguments value as NULL constant

SELECT IIF(1 > 2, NULL, NULL)

Result:
Msg 8133, Level 16, State 1, Line 1
At least one of the result expressions in a CASE specification must be an expression other than the NULL constant

Example 7: IIF() with on of the return value argument value as NULL constant

SELECT IIF(1 > 2, NULL, 'NO')

Result: NO

Example 8: IIF() with both return value arguments are variables with value NULL

DECLARE @A INT = NULL, @B INT = NULL
SELECT IIF(1 > 2, @A, @B)

Result: NULL

You may also like to read other LOGICAL FUNCTION CHOOSE() introduced in SQL SERVER 2012

Please correct me, if my understanding is wrong. Comments are always welcome.

TRY_PARSE is one of the new built-in conversion function introduced as a Part of Sql Server 2012. TRY_PARSE function is Similar to PARSE function, but if PARSE function fails to convert the value throws an exception where as TRY_PARSE function returns a NULL value.

Important Note: TRY_PARSE function is not a native SQL SERVER function, instead it is a .NET Framework Common Language Run-time dependent function. Then obviously it will have the performance overhead and also requires the presence of .NET CLR on the database Server. Continue to use the existing CAST and CONVERT functions wherever it is possible.

Syntax:  TRY_PARSE ( string_value AS data_type [ USING culture ] )

Parameter Details:
string_value : String expression which needs to be parsed.
data_type : Output data type, e.g. INT, NUMERIC, DATETIME etc.
culture : 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.

Now let us understand this TRY_PARSE function with examples:

-- TRY_PARSE String to INT
SELECT TRY_PARSE('1000' AS INT) AS 'String to INT'
-- TRY_PARSE String to NUMERIC
SELECT TRY_PARSE('1000.06' AS NUMERIC(8,2)) 
           AS 'String to NUMERIC'
-- TRY_PARSE String to DATETIME
SELECT TRY_PARSE('05-18-2013' AS DATETIME) 
           AS 'String to DATETIME' 
-- TRY_PARSE String to DateTime
SELECT TRY_PARSE('2013/05/18' AS DATETIME) 
           AS 'String to DATETIME'
-- TRY_PARSE string value in the India date format to DATETIME
SELECT TRY_PARSE('18-05-2013' AS DATETIME using 'en-in') 
 AS 'String in the India date format to DATETIME'
-- TRY_PARSE string value is in the US currency format to Money 
SELECT TRY_PARSE('$2500' AS MONEY using 'en-US')
 AS 'String in the US currency format to MONEY'

Result:

Difference between PARSE and TRY_PARSE

Try to convert invalid value, in this case PARSE throws exception but TRY_PARSE returns NULL Value

SELECT PARSE('Basavaraj' as DATETIME) 'PARSE RESULT'
GO
SELECT TRY_PARSE('Basavaraj' as DATETIME) 'TRY_PARSE RESULT'

Result:

PARSE RESULT
———————–
Msg 9819, Level 16, State 1, Line 1
Error converting string value ‘Basavaraj’ into data type datetime using culture ”.

TRY_PARSE RESULT
———————–
NULL

Below example demonstrate how we can check the result of TRY_PARSE function in IF condition:

IF  TRY_PARSE('Basavaraj' as DATETIME) IS NULL
           PRINT 'TRY_PARSE: Conversion Successful'
ELSE
           PRINT 'TRY_PARSE: Conversion Unsuccessful'

Result:
TRY_PARSE: Conversion Successful

PARSE is one of the new built-in conversion function introduced as a Part of Sql Server 2012. PARSE function converts the string expression to the requested data type. It tries it’s best to translate the string value to requested type but if it fails to translate then raises an exception.

Important Note: This PARSE function is not a native Sql function, instead it is a .NET Framework Common Language Run-time dependent function. Then obviously it will have the performance overhead and also requires the presence of .NET CLR on the database Server. Continue to use the existing CAST and CONVERT functions wherever it is possible.

Syntax:  PARSE ( string_value AS data_type [ USING culture ] )

Parameter Details:
string_value   : String expression which needs to be parsed.
data_type      : Output data type, e.g. INT, NUMERIC, DATETIME etc.
culture        : 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.

Now let us understand this PARSE function with examples:

-- PARSE String to INT
SELECT PARSE('1000' AS INT) AS 'String to INT'
-- PARSE String to Numeric
SELECT PARSE('1000.06' AS NUMERIC(8,2)) AS 'String to Numeric'
-- PARSE String to DateTime
SELECT PARSE('05-18-2013' as DATETIME) AS 'String to DATETIME'
-- PARSE String to DateTime
SELECT PARSE('2013/05/18' as DATETIME) AS 'String to DATETIME'
-- PARSE string value in the India date format to DateTime 
SELECT PARSE('18-05-2013' as DATETIME using 'en-in') 
 AS 'String in Indian DateTime Format to DATETIME'
-- PARSE string value is in the US currency format to Money 
SELECT PARSE('$2500' as MONEY using 'en-US') 
 AS 'String in US Currency Format to MONEY'

RESULT:

Below example demonstrates how PARSE function tries it’s best convert the input string value to  a specified data type even when the specified value is not in the correct format.

SELECT PARSE('08-JUNE-2013' AS DATETIME)
SELECT PARSE('08-JUN-2013' AS DATETIME)
SELECT PARSE('2013JUNE08' AS DATETIME)
SELECT PARSE('08/JUN/2013' AS DATETIME)

All the above statements will return the same result and it is: 2013-06-08 00:00:00.000

Now try to PARSE an invalid string value to DATETIME:

This conversion fails as February month will not have 31st day.

--PARSE invalid String to DATETIME
SELECT PARSE('2012/02/31' as DATETIME)
GO

Result:
Msg 9819, Level 16, State 1, Line 1
Error converting string value ’2012/02/31′ into data type datetime using culture ”.

Below PARSE function will fail to translate the date. Because the culture parameter specified is en-us, but the string value specified is not in US format. US Date format excepts month first instead of day and then day and year.

--PARSE invalid String to DateTime
SELECT PARSE('18-05-2013' as DATETIME using 'en-us')
GO

Result:
Msg 9819, Level 16, State 1, Line 1
Error converting string value ’18-05-2013′ into data type datetime using culture ‘en-us’.

Difference between PARSE and CONVERT function.

Below example demonstrates the difference between PARSE and CONVERT function. PARSE function will successfully converts the string ‘Saturday, 08 June 2013′ to date time, but the CONVERT function fails to convert the same value. That is PARSE function tries it’s best to convert the input string value to the requested type, but CONVERT function requires the input string to be exact format no variations allowed.

--PARSE Function Succeeds
SELECT PARSE('Saturday, 08 June 2013' AS DATETIME) 
                 AS 'PARSE Function Result' GO
SELECT PARSE('Sat, 08 June 2013' AS DATETIME) 
                 AS 'PARSE Function Result' GO
SELECT PARSE('Saturday 08 June 2013' AS DATETIME) 
                 AS 'PARSE Function Result' GO

--CONVERT Function Fails
SELECT CONVERT(DATETIME, 'Saturday, 08 June 2013') 
                 AS 'CONVERT Function Result' GO
SELECT CONVERT(DATETIME, 'Sat, 08 June 2013') 
                 AS 'CONVERT Function Result' GO
SELECT CONVERT(DATETIME, 'Saturday 08 June 2013') 
                 AS 'CONVERT Function Result' GO

RESULT:

PARSE Function Result
———————–
2013-06-08 00:00:00.000

PARSE Function Result
———————–
2013-06-08 00:00:00.000

PARSE Function Result
———————–
2013-06-08 00:00:00.000

CONVERT Function Result
———————–
Msg 241, Level 16, State 1, Line 3
Conversion failed when converting date and/or time from character string.

CONVERT Function Result
———————–
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

CONVERT Function Result
———————–
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.