Tag Archives: New Feature in Sql Server 2012

TRY_PARSE CONVERSION FUNCTION IN SQL SERVER 2012

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:
TRY_PARSE_CONVERSION_FUNCTION_IN_SQL_SERVER_2012

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

You may like to read the below new built-in function’s introduced in Sql Server 2012:

New Built in Functions introduced in Sql Server
CONVERSION FUNCTIONS
PARSE TRY_PARSE
TRY_CONVERT
STRING FUNCTIONS
CONCAT FORMAT
LOGICAL FUNCTIONS
CHOOSE IIF
DATE AND TIME FUNCTIONS
EOMONTH
DATEFROMPARTS DATETIMEFROMPARTS
SMALLDATETIMEFROMPARTS DATETIME2FROMPARTS
TIMEFROMPARTS DATETIMEOFFSETFROMPARTS

PARSE CONVERSION FUNCTION IN SQL SERVER 2012

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:
PARSE_SQL_SERVER_2012_CONVERSION_FUNCTION

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.

You may like to read the below new built-in function’s introduced in Sql Server 2012:

New Built in Functions introduced in Sql Server
CONVERSION FUNCTIONS
PARSE TRY_PARSE
TRY_CONVERT
STRING FUNCTIONS
CONCAT FORMAT
LOGICAL FUNCTIONS
CHOOSE IIF
DATE AND TIME FUNCTIONS
EOMONTH
DATEFROMPARTS DATETIMEFROMPARTS
SMALLDATETIMEFROMPARTS DATETIME2FROMPARTS
TIMEFROMPARTS DATETIMEOFFSETFROMPARTS

TRY_CONVERT CONVERSION FUNCTION IN SQL SERVER 2012

TRY_CONVERT is one of the new built-in conversion function introduced as a Part of Sql Server 2012. TRY_CONVERT function is similar to the CONVERT  function, but if CONVERT function fails to convert the value to the requested type then throws an exception where as TRY_CONVERT function returns NULL value.

Note:  TRY_CONVERT function raises an exception if we try to an convert expression to a type which is not explicitly permitted.

Syntax:  TRY_CONVERT (data_type [ ( length ) ], expression [, style ])

Parameter Details:

data_type     : The data type into which to cast expression.
expression  : The value to be cast.
style               : Optional integer value that specifies how the TRY_CONVERT function is to translate expression. The Values for this integer parameter are same as the one used by CONVERT function.

Now let us understand this TRY_CONVERT function with examples:

TRY_CONVERT – Succeeds

SELECT TRY_CONVERT(INT, '100')
SELECT TRY_CONVERT(NUMERIC(8,2), '1000.06')
SELECT TRY_CONVERT(INT, 100)
SELECT TRY_CONVERT(NUMERIC(8,2), 1000.06)
SELECT TRY_CONVERT(DATETIME, '05/18/2013')
SELECT TRY_CONVERT(DATETIME, '05/18/2013',111)

RESULT:

TRYCONVERTSUCCEEDS

TRY_CONVERT – Fails

In the below example we are trying to convert an invalid date i.e. 31st Feburary, 2013. In such cases TRY_CONVERT function will return NULL value instead of throwing exception.

SELECT TRY_CONVERT(DATETIME, '02/31/2013') 
            AS 'TRY_CONVERT Function Fails'

Result:
TRY_CONVERT Function Fails
————————–
NULL

TRY_CONVERT – Throws Exception

In the below example we are trying to convert an integer value to XML type which is not permitted explicitly, in such cases TRY_CONVERT function raises an exception.

SELECT TRY_CONVERT(XML, 10)

Result:
Msg 529, Level 16, State 2, Line 1
Explicit conversion from data type int to xml is not allowed.

But on the other-hand conversion of an string value to an XML type is supported. Let us see this with an example:

SELECT TRY_CONVERT(XML, '10') AS 'XML Output'

Result:

XML Output
——————————-
10

Difference Between CONVERT and TRY_CONVERT

Both CONVERT and TRY_CONVERT function converts the expression to the requested type. But if the CONVERT function fails to convert the value to the requested type then raises an exception, on the other hand if TRY_CONVERT function returns a NULL value if it fails to convert the value to the requested type. Below example demonstrates this difference

SELECT CONVERT(DATETIME, '02/31/2013')
            AS 'CONVRT Function Result'
SELECT TRY_CONVERT(DATETIME, '02/31/2013') 
            AS 'TRY_CONVERT Function Result'

Result:
CONVRT Function Result
———————–
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

TRY_CONVERT Function Result
————————-
NULL

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

IF TRY_CONVERT(DATETIME,'Basavaraj') IS NULL
        PRINT 'TRY_CONVERT: Conversion Successful'
ELSE
        PRINT 'TRY_CONVERT: Conversion Unsuccessful'

Result:
TRY_CONVERT: Conversion Successful

You may like to read the below new built-in function’s introduced in Sql Server 2012:

New Built in Functions introduced in Sql Server
CONVERSION FUNCTIONS
PARSE TRY_PARSE
TRY_CONVERT
STRING FUNCTIONS
CONCAT FORMAT
LOGICAL FUNCTIONS
CHOOSE IIF
DATE AND TIME FUNCTIONS
EOMONTH
DATEFROMPARTS DATETIMEFROMPARTS
SMALLDATETIMEFROMPARTS DATETIME2FROMPARTS
TIMEFROMPARTS DATETIMEOFFSETFROMPARTS

SEQUENCE IN SQL SERVER 2012

This article basically focuses towards the introduction of Sequences with extensive list of examples. To know about Sequence Limitations and Sequence Cache Management and internals you may like to visit the articles: Sequence Limitations and restrictions with Extensive List of examples and Sequence Cache Management and Internals with examples.

SEQUENCE is one of the new feature introduced in Sql Server 2012. Sequence is a user-defined object and as name suggests it generates sequence of numeric values according to the properties with which it is created. It is similar to Identity column, but there are many difference between them. Some of the major differences between them are:

  • Sequence is used to generate database-wide sequential number, but identity column is tied to a table.
  • Sequence is not associated with a table.
  • Same sequence can be used in multiple tables.
  • It can be used in insert statement to insert identity values, it can also be used in T-Sql Scripts.

[ALSO READ] Sequence Vs Identity

Now let us understand the sequence concept using below extensive list of examples:

Create a Sequence with Initial value 1 and Increment by 1

CREATE SEQUENCE [DBO].[SequenceExample] AS INT
 START WITH 1
 INCREMENT BY 1
GO

Using Sequence in an Insert Statement

CREATE TABLE dbo.Employee(ID INT,Name VARCHAR(100))
GO
--Insert records into Employee table with Sequence object
INSERT INTO dbo.Employee VALUES
(NEXT VALUE FOR DBO.SequenceExample,'BASAVARAJ BIRADAR'), 
(NEXT VALUE FOR DBO.SequenceExample,'SHREE BIRADAR'),
(NEXT VALUE FOR DBO.SequenceExample,'PRATHAM BIRADAR')
GO
-- CHECK THE RECORDS INSERTED IN THE TABLE
SELECT * FROM dbo.Employee WITH(NOLOCK)
GO

Result:
ID Name
———– —————————–
1 BASAVARAJ BIRADAR
2 SHREE BIRADAR
3 PRATHAM BIRADAR

Associate Sequence object to a table

CREATE TABLE dbo.Customer
(ID INT DEFAULT(NEXT VALUE FOR DBO.SequenceExample),
                               Name VARCHAR(100))
GO
INSERT INTO dbo.Customer(Name)
VALUES('PINKU BIRADAR'),
('MONTY BIRADAR')
GO
-- CHECK THE RECORDS INSERTED IN THE TABLE
SELECT * FROM dbo.Customer WITH(NOLOCK)
GO

Result:
ID Name
———– —————————–
4 PINKU BIRADAR
5 MONTY BIRADAR

[ALSO READ]: SEQUENCE Limitations and Restrictions

Getting Next Sequence Value in A SELECT Statement

SELECT (NEXT VALUE FOR DBO.SequenceExample) 
                            AS SequenceValue
GO 3

Note: Here GO 3 statement executes the statement above it 3 times.

Result:
Beginning execution loop
———–
6
(1 row(s) affected)

———–
7
(1 row(s) affected)

———–
8
(1 row(s) affected)
Batch execution completed 3 times.

[ALSO READ]: Sequence Cache management and Internals

Getting Sequence Next Value in a variable

DECLARE @EmpID AS INT 
        = NEXT VALUE FOR DBO.SequenceExample
SELECT @EmpID AS 'Employee Id'

Result:
Employee Id
———–
9

Re-Setting the Sequence Number

ALTER SEQUENCE DBO.SequenceExample
RESTART WITH 1 ;
GO
-- Verify whether sequence number is re-set
SELECT (NEXT VALUE FOR DBO.SequenceExample) 
                           AS SequenceValue
GO

Result:

SequenceValue
———–
1

How to get the current value of the Sequence

SELECT Current_Value 
FROM SYS.Sequences 
WHERE name='SequenceExample'
GO

Result:
Current_Value
———————————
1

Create Sequence Syntax:

CREATE SEQUENCE [schema_name . ] sequence_name
 [ AS [built_in_integer_type | user-defined_integer_type]]
 [ START WITH  ]
 [ INCREMENT BY  ]
 [ { MINVALUE [  ] } | { NO MINVALUE } ]
 [ { MAXVALUE [  ] } | { NO MAXVALUE } ]
 [ CYCLE | { NO CYCLE } ]
 [ { CACHE [  ] } | { NO CACHE } ]
 [ ; ]

Where:
Start with:             the initial value to start with sequence.
Increment by:   the step by which the values will get incremented or decremented.
Minvalue:              the minimum value of the sequence.
Maxvalue:             the maximum value of the sequence.
Cycle / No Cycle:  to recycle the sequence once it reaches to the maximum or minimum (if increment by is a negative number).
Cache / No Cache:  to pre-allocate the number of sequences specified by the given value.

[ALSO READ]:
SEQUENCE Limitations and Restrictions
Sequence Cache management and Internals