Tag Archives: New Feature in Sql Server 2012

New Features in Sql Server 2012

Following are the some of the new features of the Sql Server 2012 which I have blogged. Click on the feature name to know it in detail with extensive examples:

[ALSO READ] New features in Sql Server 2014

SEQUENCE OBJECT
THROW
EXECUTE WITH RESULT SETS Option
CONVERSION FUNCTIONS STRING FUNCTIONS
LOGICAL FUNCTIONS DATE AND TIME FUNCTIONS

[ALSO READ] New features in Sql Server 2014
New Features in Sql Server 2008

EXECUTE WITH RESULT SETS IN SQL SERVER 2012

In SQL SERVER 2012 the EXECUTE statement is enhanced to specify the WITH RESULT SETS options. With this new option we can change the name and data type of the column’s of the returning result set.

To understand this new feature let us create a Database with table and stored procedures as show below:

CREATE DATABASE SQLHINTSDEMO
GO
USE SQLHINTSDEMO
GO
CREATE TABLE dbo.Customer
(CustomerId INT IDENTITY(1,1) NOT NULL,
CustomerName VARCHAR(100))
GO
INSERT INTO dbo.Customer(CustomerName)
VALUES('Basavaraj Biradar'),
('ShreeGanesh Biradar')
GO

-- Stored Procedure which returns single result set
CREATE PROCEDURE dbo.GetCustomerDetails
AS
BEGIN
SELECT CustomerId, CustomerName
FROM dbo.Customer WITH(NOLOCK)
END
GO

-- Stored Procedure which returns multiple result sets
CREATE PROCEDURE dbo.GetMultipleResultSet
AS
BEGIN
SELECT CustomerId, CustomerName
FROM dbo.Customer WITH(NOLOCK)

SELECT CustomerName, CustomerId
FROM dbo.Customer WITH(NOLOCK)
END
GO

--Stored Procedure with single result set with no record
CREATE PROCEDURE dbo.WithResultSetWithoutAnyRecord
AS
BEGIN
SELECT CustomerId, CustomerName
FROM dbo.Customer WITH(NOLOCK)
WHERE 1=2
END
GO

--Stored Procedure without result set as PRINT statement result 
--is not considered as result set instead it is message
CREATE PROCEDURE dbo.WithNoResultSet
AS
BEGIN
	PRINT 'NO RESULT SET'
END
GO
EXECUTE Statement WITH RESULT SETS NONE Option:

This option specifies that the execute statement will not return any results. If any results are returned it raises an exception and the batch is aborted.

Example 1: The below statement succeeds as the stored procedure WithNoResultSet is not returning any result set. Stored Procedure has only PRINT statement and the PRINT statement response is considered as a message.

EXECUTE dbo.WithNoResultSet
WITH RESULT SETS NONE;

RESULT:
NO RESULT SET

Example 2: The below statement fails as the stored procedure returns a result set even though the result set doesn’t have any records.

EXECUTE dbo.WithResultSetWithoutAnyRecord
WITH RESULT SETS NONE;

RESULT:

Msg 11535, Level 16, State 1, Procedure WithResultSetWithoutAnyRecord, Line 6
EXECUTE statement failed because its WITH RESULT SETS clause specified 0 result set(s), and the statement tried to send more result sets than this.

Example 3: The below statement fails as the stored procedure returns a result set.

EXECUTE dbo.GetCustomerDetails
WITH RESULT SETS NONE;

RESULT:

Msg 11535, Level 16, State 1, Procedure GetCustomerDetails, Line 8
EXECUTE statement failed because its WITH RESULT SETS clause specified 0 result set(s), and the statement tried to send more result sets than this.

EXECUTE Statement WITH RESULT SETS UNDEFINED Option:
RESULT SETS UNDEFINED is the default option of the EXECUTE statement if this option is not specified.

Example 1: Examples of EXECUTE statement with WITH RESULT SETS UNDEFINED Option

EXECUTE dbo.GetCustomerDetails
WITH RESULT SETS UNDEFINED;
GO

RESULT:

CustomerId  CustomerName
----------- ----------------------------
1           Basavaraj Biradar
2           ShreeGanesh Biradar
EXECUTE dbo.WithNoResultSet
WITH RESULT SETS UNDEFINED;
GO

RESULT:
NO RESULT SET

Example 2: Examples of EXECUTE statement without WITH RESULT SETS UNDEFINED Option

EXECUTE dbo.GetCustomerDetails
GO

RESULT:
CustomerId CustomerName
———– ——————–
1 Basavaraj Biradar
2 ShreeGanesh Biradar

(2 row(s) affected)

EXECUTE dbo.WithNoResultSet
WITH RESULT SETS UNDEFINED;
GO

RESULT:
NO RESULT SET

EXECUTE Statement WITH RESULT SETS Option to redefine result sets:
Example 1: EXECUTE Statement WITH RESULT SETS Option to redfine single result. Here the stored procedure returned column names CustomerId and CustomerName are renamed to Id and Name. And also the CustomerId column datatype is converted from INT to VARCHAR(8).

EXECUTE dbo.GetCustomerDetails
WITH RESULT SETS (	
		(Id VARCHAR(8) NOT NULL, Name VARCHAR(100))
)
GO

RESULT
Id Name
——– ———————-
1 Basavaraj Biradar
2 ShreeGanesh Biradar
(2 row(s) affected)

Example 2: EXECUTE Statement WITH RESULT SETS Option to redefine two result sets

EXECUTE dbo.GetMultipleResultSet
WITH RESULT SETS (
---Redefine First Result Set
(CustomerId INT NOT NULL, CustomerName Varchar(100)), 
--comma separates the result sets definition

---Redefine Second Result Set
(Name Varchar(10), Id INT NOT NULL)
)
GO

RESULT:

CustomerId  CustomerName
----------- -------------------------
1           Basavaraj Biradar
2           ShreeGanesh Biradar

(2 row(s) affected)

Name                      Id
------------------------- -----------
Basavaraj Biradar         1
ShreeGanesh Biradar       2

(2 row(s) affected)
Example 3: WITH RESULT SETS option will not allow to have less or more no. of columns in the result re-definition than the no. of columns returned by the stored procedure. In below examples the stored procedure is returning to columns but while redefining the result set only one column is mentioned.

EXECUTE dbo.GetCustomerDetails
WITH RESULT SETS (
    (CustomerId INT NOT NULL)
)
GO

RESULT:

Msg 11537, Level 16, State 1, Procedure GetCustomerDetails, Line 8
EXECUTE statement failed because its WITH RESULT SETS clause specified 1 column(s) for result set number 1, but the statement sent 2 column(s) at run time.

EXECUTE dbo.GetCustomerDetails
WITH RESULT SETS (
     (CustomerName VARCHAR(100))
)
GO

RESULT:

Msg 11537, Level 16, State 1, Procedure GetCustomerDetails, Line 8
EXECUTE statement failed because its WITH RESULT SETS clause specified 1 column(s) for result set number 1, but the statement sent 2 column(s) at run time.

Example 4: While redefining the column data type, it will allow only to the compatible datatype. In the below example the CustomerName column type is VARCHAR but while redefiningin we are defining it as INT. So it will raise error in this case.

EXECUTE dbo.GetCustomerDetails
WITH RESULT SETS (
    (CustomerId INT NOT NULL, 
     CustomerName INT)
)
GO

RESULT:

Msg 8114, Level 16, State 2, Procedure GetCustomerDetails, Line 8
Error converting data type varchar(100) to int.

GO

Differences Between RAISERROR and THROW in Sql Server

Both RAISERROR and THROW statements are used to raise an error in Sql Server. The journey of RAISERROR started from Sql Server 7.0, where as the journey of THROW statement has just began with Sql Server 2012. obviously, Microsoft suggesting us to start using THROW statement instead of RAISERROR. THROW statement seems to be simple and easy to use than RAISERROR.

This is the third article in the series of articles on Exception Handling in Sql Server. Below is the complete list of articles in this series.

Part   I: Exception Handling Basics – MUST Read Article
Part  II: TRY…CATCH (Introduced in Sql Server 2005)
Part III: RAISERROR Vs THROW (Throw: Introduced in Sql Server 2012)
Part IV: Exception Handling Template

Raiserror Vs Throw

Below table lists-out 10 major difference between RAISERROR and THROW with examples:

RAISERROR THROW
Version of the Sql Server in which it is introduced?
Introduced in SQL SERVER 7.0. And as per BOL, Microsoft is suggesting to start using THROW statement instead of RAISERROR in New Applications.

RAISERROR can’t be used in the Sql Server 2014’s Natively compiled Stored Procedures.

Introduced in SQL SERVER 2012. THROW statement seems to be simple and easy to use than RAISERROR.

THROW statement can be used in the Sql Server 2014’s Natively Compiled Stored Procedure.

SYNTAX
RAISERROR 
 ( { error_number | message 
        | @local_variable }
    { ,severity ,state }
    [ ,argument [ ,...n ] ] )
    [ WITH option [ ,...n ] ]
THROW 
 [ { error_number 
     | @local_variable },
   { message | @local_variable },
   { state | @local_variable } ] 
[ ; ]
Can re-throw the original exception that invoked the CATCH block?
NO. It always generates new exception and results in the loss of the original exception details. Below example demonstrates this:

BEGIN TRY
  DECLARE @result INT
--Generate divide-by-zero error
  SET @result = 55/0
END TRY
BEGIN CATCH
--Get the details of the error
--that invoked the CATCH block
 DECLARE 
   @ErMessage NVARCHAR(2048),
   @ErSeverity INT,
   @ErState INT

 SELECT
   @ErMessage = ERROR_MESSAGE(),
   @ErSeverity = ERROR_SEVERITY(),
   @ErState = ERROR_STATE()

 RAISERROR (@ErMessage,
             @ErSeverity,
             @ErState )
END CATCH

RESULT:
Msg 50000, Level 16, State 1, Line 19
Divide by zero error encountered.

NOTE:The actual line number of the code which generated Divided By Zero error here is 4, but the exception message returned by RAISERROR is showiung it as 19. Also the error number corresponding to divide by zero error is 8134 in the SYS.Messages table, but the one returned by RAISERROR is 50000.

YES. To Re-THROW the original exception caught in the TRY Block, we can just specify the THROW statement without any parameters in the CATCH block. Below example demonstrates this:

BEGIN TRY
  DECLARE @result INT
--Generate divide-by-zero error
  SET @result = 55/0
END TRY
BEGIN CATCH
    THROW
END CATCH

RESULT:
Msg 8134, Level 16, State 1, Line 4
Divide by zero error encountered.

With above example it is clear that THROW statement is very simple for RE-THROWING the exception. And also it returns correct error number and line number.

Causes the statement batch to be ended?
Example 1: In the below Batch of statements the PRINT statement after RAISERROR statement will be executed.

BEGIN
 PRINT 'BEFORE RAISERROR'
 RAISERROR('RAISERROR TEST',16,1)
 PRINT 'AFTER RAISERROR'
END

RESULT:

BEFORE RAISERROR
Msg 50000, Level 16, State 1, Line 3
RAISERROR TEST

AFTER RAISERROR

Example 2: In the below example all the statement’s after RAISERROR statement are executed.

BEGIN TRY
 DECLARE @RESULT INT = 55/0	
END TRY
BEGIN CATCH
 PRINT 'BEFORE RAISERROR';

--Get the details of the error
--that invoked the CATCH block
 DECLARE 
  @ErMessage NVARCHAR(2048),
  @ErSeverity INT,
  @ErState INT

 SELECT
  @ErMessage = ERROR_MESSAGE(),
  @ErSeverity = ERROR_SEVERITY(),
  @ErState = ERROR_STATE()

 RAISERROR (@ErMessage,
             @ErSeverity,
             @ErState )

 PRINT 'AFTER RAISERROR'
END CATCH
 PRINT 'AFTER CATCH'

RESULT:
BEFORE RAISERROR
Msg 50000, Level 16, State 1, Line 19
Divide by zero error encountered.

AFTER RAISERROR
AFTER CATCH

Example 1: In the below Batch of statements the PRINT statement after THROW statement will not executed.

BEGIN
	PRINT 'BEFORE THROW';
	THROW 50000,'THROW TEST',1
	PRINT 'AFTER THROW'
END

RESULT:

BEFORE THROW
Msg 50000, Level 16, State 1, Line 3
THROW TEST

Example 2: In the below example no PRINT statement’s after THROW statement are executed.

BEGIN TRY
  DECLARE @RESULT INT = 55/0	
END TRY
BEGIN CATCH
  PRINT 'BEFORE THROW';
  THROW;
  PRINT 'AFTER THROW'
END CATCH
  PRINT 'AFTER CATCH'

RESULT:
BEFORE THROW
Msg 8134, Level 16, State 1, Line 2
Divide by zero error encountered.

CAN SET SEVERITY LEVEL?
YES. The severity parameter specifies the severity of the exception. NO. There is no severity parameter. The exception severity is always set to 16. (unless re-throwing in a CATCH block)
Requires preceding statement to end with semicolon (;) statement terminator?
NO. YES. The statement before the THROW statement must be followed by the semicolon (;) statement terminator.
CAN RAISE SYSTEM ERROR MESSAGE?
The SYS.MESSAGES Table will have both system-defined and user-defined messages. Message IDs less than 50000 are system messages.
YES. With RAISERROR we can raise the System Exception.
Example:
RAISERROR (40655,16,1)RESULT:
Msg 40655, Level 16, State 1, Line 1
Database ‘master’ cannot be restored.
NO. With THROW we can’t raise the System Exception. But when it used in CATCH BLOCK it can Re-THROW the system exception.Example: Trying to raise system exception (i.e. exception with ErrorNumber less than 50000).

THROW 40655, ‘Database master cannot be restored.’, 1

RESULT:
Msg 35100, Level 16, State 10, Line 1
Error number 40655 in the THROW statement is outside the valid range. Specify an error number in the valid range of 50000 to 2147483647

CAN RAISE user-defined message with message_id greater than 50000 which is not defined in SYS.MESSAGES table?
NO. If a msg_id is passed to RAISERROR, the ID must be defined in sys.messages.Example:

RAISERROR (60000, 16, 1)

RESULT:
Msg 18054, Level 16, State 1, Line 1
Error 60000, severity 16, state 1 was raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage.

Now add the Message to SYS.MESSAGES Table by using the below statement:

EXEC sys.sp_addmessage 60000, 16, ‘Test User Defined Message’

Now try to Raise the Error:
RAISERROR (60000, 16, 1)

RESULT:
Msg 60000, Level 16, State 1, Line 1
Test User Defined Message

YES. The error_number parameter does not have to be defined in sys.messages.Example:
THROW 60000, ‘Test User Defined Message’, 1RESULT:
Msg 60000, Level 16, State 1, Line 1
Test User Defined Message
Allows substitution parameters in the message parameter?
By using the below statement add a sample test message with parameteres to the SYS.Messages Table:
EXEC sp_addmessage 70000,16,‘Message with Parameter 1: %d and Parameter 2:%s’
YES.The msg_str parameter can contain printf formatting styles.Example 1:

RAISERROR (70000, 16, 1, 505,‘Basavaraj’ )

RESULT:
Msg 70000, Level 16, State 1, Line 1
Message with Parameter 1: 505 and Parameter 2:Basavaraj

NO.The message parameter does not accept printf style formatting.Example 1:

THROW 70000, ‘Message with Parameter 1: %d and Parameter 2:%s’, 1, 505,’Basavaraj’

RESULT:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘,’.

Alternative Way of doing this is:

DECLARE @ErrorMsg NVARCHAR(2048) = FORMATMESSAGE(70000, 505, ‘Basavaraj’ );
THROW 70000, @ErrorMsg, 1

Example 2: Message manipulation is not allowed in the THROW statement

Below statement will fail

THROW 58000,‘String1’ + ‘ String2’,1

RESULT:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘+’.

We can solve such problems, we can prepare the message prior to the THROW statement and then pass it to throw statement as a variable. Below example illustrates this.

DECLARE @message NVARCHAR(2048)
SET @message = ‘String1’ + ‘ String2’;
THROW 58000, @message, 1

RESULT:
Msg 58000, Level 16, State 1, Line 3
String1 String2

RAISERROR WITH NOWAIT statement can also be used to flushes all the buffered PRINT/SELECT Statement Messages within a batch.

[ALSO READ] You may like to read below other popular articles on differences

1. Varchar vs NVarchar
2. Varchar vs Varchar(MAX)
3. Char vs Varchar
4. Text vs Varchar(Max)
5. Union vs Union All
6. DateTime vs DateTime2
7. SET QUOTED_IDENTIFIER ON vs SET QUOTED_IDENTIFIER OFF
8. Stored Procedure vs User Defined Function
9. Primary Key vs Unique Key
10. RAISERROR vs THROW
11. Temporary Table vs Table Variable
12. Len() vs Datalength()
13. Sequence vs Identity
14. DATEDIFF vs DATEDIFF_BIG

DATETIMEFROMPARTS FUNCTION IN SQL SERVER 2012

DATETIMEFROMPARTS is one of the new built-in Date and Time Function introduced as a Part of Sql Server 2012. Returns a DATETIME value for the specified date and time. It is a Sql Server native function not dependent on the .NET CLR.

SYNTAX: DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )

WHERE: year, month, day, hour, minute, seconds and milliseconds are integer expressions representing valid year, month, day, hour, minute, seconds and milliseconds respectively.
Return Type: DATETIME

Example 1: DATETIMEFROMPARTS – with valid date and time part integer constants

SELECT
 DATETIMEFROMPARTS(2013,6,23,1,20,14,8) AS 'DATETIME',
 DATETIMEFROMPARTS(1753,1,1,0,0,0,0) AS 'MIN DATETIME',
 DATETIMEFROMPARTS(9999,12,31,23,59,59,997) AS 'MAX DATETIME'

Result:

DATETIME                               MIN DATETIME                           MAX DATETIME
———————–                      ———————–                        ———————–
2013-06-23 01:20:14.007       1753-01-01 00:00:00.000        9999-12-31 23:59:59.997

Example 2: DATETIMEFROMPARTS – with valid date and time part integer variables

DECLARE @year INT = 2013, @month INT = 6, @day INT = 23,
        @hour INT = 1, @minute INT = 20, @seconds INT = 14, 
        @milliseconds INT = 878
SELECT DATETIMEFROMPARTS( @year, @month, @day, 
        @hour , @minute, @seconds, @milliseconds) AS 'DATETIME'

Result:

DATETIME
———————–
2013-06-23 01:20:14.877

Example 3: DATETIMEFROMPARTS – with Invalid part

DECLARE @year INT = 2013, @invalidmonth INT = 15, @day INT = 23,
        @hour INT = 1, @minute INT = 20, @seconds INT = 14, 
        @milliseconds INT = 878
SELECT DATETIMEFROMPARTS( @year, @invalidmonth, @day, 
        @hour , @minute, @seconds, @milliseconds) AS 'DATETIME'

Result:
Msg 289, Level 16, State 3, Line 3
Cannot construct data type datetime, some of the arguments have values which are not valid.

Example 4: DATETIMEFROMPARTS – with one of the part as NULL

DECLARE @year INT = 2013, @month INT = 6, @day INT = 23,
        @hour INT = 1, @minute INT = 20, @seconds INT = NULL, 
        @milliseconds INT = 878
SELECT DATETIMEFROMPARTS( @year, @month, @day, 
        @hour , @minute, @seconds, @milliseconds) AS 'DATETIME'

Result:
DATETIME
———————–
NULL

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

SMALLDATETIMEFROMPARTS FUNCTION IN SQL SERVER 2012

SMALLDATETIMEFROMPARTS is one of the new built-in Date and Time Function introduced as a Part of Sql Server 2012. Returns a SMALLDATETIME value for the specified date and time. It is a Sql Server native function not dependent on the .NET CLR.

SYNTAX: SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )

WHERE: year, month, day, hour and minute are integer expressions representing valid year, month, day, hour and minute respectively.
Return Type: SMALLDATETIME

Example 1: SMALLDATETIMEFROMPARTS – with valid date and time part integer constants

SELECT 
 SMALLDATETIMEFROMPARTS(2013,6,23,1,20 ) AS 'SMALLDATETIME',
 SMALLDATETIMEFROMPARTS(1900,1,1,0,0) AS 'MIN SMALLDATETIME',
 SMALLDATETIMEFROMPARTS(2079,6,6,23,59) AS 'MAX SMALLDATETIME'

Result:

SMALLDATETIME         MIN SMALLDATETIME         MAX SMALLDATETIME
———————–           ———————–                   ———————–
2013-06-23 01:20:00   1900-01-01 00:00:00             2079-06-06 23:59:00

Example 2: SMALLDATETIMEFROMPARTS – with valid date and time part integer variables

DECLARE @year INT = 2013, @month INT = 6, @day INT = 23,
		@hour INT = 1, @minute INT = 20
SELECT SMALLDATETIMEFROMPARTS( @year, @month, @day, 
		@hour , @minute) AS 'SMALLDATETIME'

Result:

SMALLDATETIME
———————–
2013-06-23 01:20:00

Example 3: SMALLDATETIMEFROMPARTS – with Invalid part

DECLARE @year INT = 2013, @invalidmonth INT = 15, @day INT = 23,
		@hour INT = 1, @minute INT = 20
SELECT SMALLDATETIMEFROMPARTS( @year, @invalidmonth, @day, 
		@hour , @minute) AS 'SMALLDATETIME'

Result:
Msg 289, Level 16, State 4, Line 3
Cannot construct data type smalldatetime, some of the arguments have values which are not valid.

Example 4: SMALLDATETIMEFROMPARTS – with one of the part as NULL

DECLARE @year INT = 2013, @invalidmonth INT = 15, @day INT = 23,
		@hour INT = 1, @minute INT = NULL
SELECT SMALLDATETIMEFROMPARTS( @year, @invalidmonth, @day, 
		@hour , @minute) AS 'SMALLDATETIME'

Result:
SMALLDATETIME
———————–
NULL

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