Tag Archives: SQL SERVER 2012

Sequence Cache management and Internals – Sql Server 2012

This article basically focuses on the Sequence Cache Management and it’s internals. To know Sequence basics and it’s limitations you may like to visit the articles: Sequence Introduction with Extensive List of examples and Sequence Limitations and restrictions with Extensive List of examples

Sql Server provides option to specify the CACHING options while Creating/Altering Sequence. Basically Sequence created with CACHE option improves the performance by reducing the number of disk IOs that are required to generate sequence numbers.

SEQUENCE CACHE Property:

[ CACHE [<constant> ] | NO CACHE ]

WHERE:

CACHE [<constant> ]: This property can be specified if we want the Sequence with caching enabled, where <constant> is the CACHE size. If the Sequence is Created without specifying CACHE Property then by DEFAULT Sql Server creates the Sequence with CACHING option enabled and as per MSDN the Database Engine will select a size. However, users should not rely upon the selection of the CACHE size being consistent, so it is always advisable to define the required CACHE size if we want to have Sequence Caching instead of relying on the default.

NO CACHE: We can specify this option if we don’t want the Sequence to use the CACHING.

[ALSO READ] :New Features in Sql Server 2012

Let us understand in detail the Performance implications of Creating a Sequence with NO CACHE option or with CACHE enabled option.

SEQUENCE WITHOUT CACHING (i.e. Sequence created with NO CACHE option):

Below diagram demonstrates the steps are performed by Sql Server whenever Next Value is requested from a Sequence object created with NO CACHE option

Sequence with NO CACHE

So, it is clear from the above diagram that, if Sequence object created with NO CACHE option each time a next sequence value is requested it will write the calculated value to the system table before returning it to the calling statement.

[ALSO READ] Introduction to Sequence

SEQUENCE WITH CACHING:

Sequence created with CACHE option improves the performance by minimizing the no of writes to the System table for the generation of the Sequence number. In case of a sequence object created with NO CACHE option each time a next sequence value is requested, it will write the calculated value to the system table before returning it to the calling statement. But if sequence is created with CACHE of Size 50, it will write to the System table only after each 50 sequence values are served.

[ALSO READ] Sequence Vs Identity

Internals of Cache management
For a sequence object which is created with CACHE option, it will not pre-allocate the number of sequence numbers specified by the CACHE argument. Instead CACHE is maintained by two In-Memory variables one for tracking the Sequence Current Value another for the number of values left in the cache. So for sequence CACHE management the memory used will always be two instances of the data type of the Sequence object. For example, if a cache size of 50 is chosen, SQL Server does not keep 50 individual values cached. It only caches the current value and the number of values left in the cache. This means that the amount of memory required to store the cache is always two instances of the data type of the sequence object.

CACHE Management By Two In-Memory Variables

Following steps are performed by Sql Server whenever Next Value is requested from a Sequence object created with CACHING enabled option:

Sequence With CACHING Enabled

[ALSO READ] SEQUENCE Limitations and Restrictions

Let us understand this Sequence Cache Management Internals with an example:

Create the Sequence object SequenceExample with CACHE size as 50 as below:

CREATE SEQUENCE dbo.SequenceExample AS INT
    START WITH 1
    INCREMENT BY 1 
    CACHE 50;
GO

Let us request the first value from the Sequence object SequenceExample and verify that the last cached value 50 (because cache size is 50 and start value is 1 and increment value of the sequence is 1) is written to the system tables on the disk.

--Get First Sequence Value
SELECT (NEXT VALUE FOR dbo.SequenceEx) As NextSeqValue
--Verify the CACHE size 
SELECT * FROM SYS.SYSSCHOBJS 
WHERE ID = OBJECT_ID('dbo.SequenceEx')
--Verify the Last CACHED value written to the system table
SELECT * FROM SYS.SYSOBJVALUES 
WHERE OBJID = OBJECT_ID('dbo.SequenceEx')

Sequence Last CACHED VALUE

From the above queries it is clear that when the first value is requested from the sequence, values 1 through 50 are made available from the two in-memory variables. The last cached value 50 is written to the system tables on the disk.

Now let us verify whether next sequence value 2 through 50 are made available from the two in-memory variables and no updates happen to the system table by using the below queries.


Second Sequence Value

From the above queries results it is clear that no updates happened to the system tables when next sequence value is requested. This holds for all the next values till the sequence value 50.

By using the below queries read the sequence values from 3 through 50.

--Get sequence values from 3 to 50
SELECT (NEXT VALUE FOR dbo.SequenceEx) As NextSeqValue
GO 48

Now the request for the next sequence value i.e. 51 will cause the cache to be allocated again. The new last cached value 100 will be written to the system tables. Let us verify this using the below queries:

Sequence New Last Cached Value

From the results of the above queries it is clear that the request for the sequence value 51 causes the cache to be allocated again. The new last cached value 100 is written to the system tables.

If the Database Engine is stopped after you use 55 numbers, the next intended sequence number in memory 56 is written to the system tables, replacing the previously stored number 100 before database engine is stopped.

After SQL Server restarts and a sequence number is needed, the starting number is read from the system table 56. The cache amount of 50 numbers (56-106) is allocated to memory and the new last cached value 106 is written to the system tables.

If the Database Engine stops abnormally say for example due to power failure (To simulate this you can go to task manager and kill the Sql Server process, please don’t try this on production database). As the last sequence value we have read is the 56, so next sequence value expected is 57 but instead of 57 it will return 107. Any sequence numbers allocated to memory (but never requested by a user or application) are lost. This functionality may leave gaps, but guarantees that the same value will never be issued two times for a single sequence object unless it is defined as CYCLE or is manually restarted.

[ALSO READ] :
SEQUENCE IN SQL SERVER 2012
SEQUENCE Limitations and Restrictions
Sequence Vs Identity
New Features in Sql Server 2012

SEQUENCE Limitations and restrictions – Sql Server 2012

This article basically focuses on the Sequence Limitations and Restrictions with extensive list of example. To know Sequence basics and Sequence Cache Management and internals you may like to visit the articles: Sequence Introduction with Extensive List of examples and Sequence Cache Management and Internals with examples.

In this article let us understand on the limitations and restrictions on the usage of Sequences when using it in queries. Let us understand some of these major limitations with below extensive list of examples.

To demonstrate the limitations let us create a Sequence and a table as below:

--Create A DataBase for Demo 
CREATE DATABASE SqlHintsDemoDB
GO
USE SqlHintsDemoDB
GO
--Create a Sequence with Initial value 1 and Increment by 1
CREATE SEQUENCE [DBO].[SequenceExample] AS INT
 START WITH 1
 INCREMENT BY 1
GO
--Create Employee Table
CREATE TABLE dbo.Employee(ID INT,Name VARCHAR(100))
GO
-- INSERT RECORDS to the Employee table with Sequence object
INSERT INTO dbo.Employee VALUES
 (NEXT VALUE FOR DBO.SequenceExample,'BASAV'),
 (NEXT VALUE FOR DBO.SequenceExample,'SHREE'),
 (NEXT VALUE FOR DBO.SequenceExample,'PRATHAM')
GO

The NEXT VALUE FOR function cannot be used in the following situations:
1) In a statement using the DISTINCT, UNION, UNION ALL, EXCEPT or INTERSECT operator.

SELECT (NEXT VALUE FOR DBO.SequenceExample)
UNION
SELECT (NEXT VALUE FOR DBO.SequenceExample)
GO

RESULT:

Msg 11721, Level 15, State 1, Line 1
NEXT VALUE FOR function cannot be used directly in a statement that uses a DISTINCT, UNION, UNION ALL, EXCEPT or INTERSECT operator.

2) In a statement using the ORDER BY clause unless NEXT VALUE FOR … OVER (ORDER BY …) is used.

SELECT NAME, (NEXT VALUE FOR DBO.SequenceExample) SeqValue
FROM dbo.Employee
ORDER BY NAME
GO

RESULT:

Msg 11723, Level 15, State 1, Line 1
NEXT VALUE FOR function cannot be used directly in a statement that contains an ORDER BY clause unless the OVER clause is specified.

3) In a statement using TOP, OFFSET, or when the ROWCOUNT option is set.

SELECT TOP 10  (NEXT VALUE FOR DBO.SequenceExample)
GO

RESULT:

Msg 11739, Level 15, State 1, Line 1
NEXT VALUE FOR function cannot be used if ROWCOUNT option has been set, or the query contains TOP or OFFSET.

4) In conditional expressions using CASE, CHOOSE, COALESCE, IIF, ISNULL, or NULLIF.

DECLARE @v INT
SELECT ISNULL(@v,(NEXT VALUE FOR DBO.SequenceExample))
GO

RESULT:

Msg 11741, Level 15, State 1, Line 2
NEXT VALUE FOR function cannot be used within CASE, CHOOSE, COALESCE, IIF, ISNULL and NULLIF.

5) In the WHERE clause of a statement.

SELECT *
FROM dbo.Employee
WHERE ID = (NEXT VALUE FOR DBO.SequenceExample)
GO

RESULT:

Msg 11720, Level 15, State 1, Line 3
NEXT VALUE FOR function is not allowed in the TOP, OVER, OUTPUT, ON, WHERE, GROUP BY, HAVING, or ORDER BY clauses.

Below table lists few more situations where NEXT VALUE FOR function cannot be used:

Sl. No. Limitation/Restriction of using NEXT VALUE FOR function
1 In views, in user-defined functions, or in computed columns.
2 As an argument to a table-valued function.
3 As an argument to an aggregate function.
4 In the following clauses: FETCH, OVER, OUTPUT, ON, PIVOT, UNPIVOT, GROUP BY, HAVING, COMPUTE, COMPUTE BY, or FOR XML.
5 When a database is in read-only mode.
6 In subqueries including common table expressions and derived tables.
7 In a MERGE statement. (Except when the NEXT VALUE FOR function is used in a default constraint in the target table and default is used in the CREATE statement of the MERGE statement.)
8 In the definition of a check constraint.
9 In the definition of a rule or default object. (It can be used in a default constraint.)
10 As a default in a user-defined table type.
11 In a VALUES clause that is not part of an INSERT statement.

Below table lists out the list of error message that Sql Server may return while working with sequences:

We can use query like below to get this list:

SELECT message_id 'ERROR No.',text 'ERROR Description'
FROM SYS.messages
WHERE language_id = 1033 
  AND message_id between 11700 and 11742
ERROR No. ERROR Description
11700 The increment for sequence object ‘%.*ls’ cannot be zero.
11701 The absolute value of the increment for sequence object ‘%.*ls’ must be less than or equal to the difference between the minimum and maximum value of the sequence object.
11702 The sequence object ‘%.*ls’ must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, or any user-defined data type that is based on one of the above integer data types.
11703 The start value for sequence object ‘%.*ls’ must be between the minimum and maximum value of the sequence object.
11704 The current value ‘%.*ls’ for sequence object ‘%.*ls’ must be between the minimum and maximum value of the sequence object.
11705 The minimum value for sequence object ‘%.*ls’ must be less than its maximum value.
11706 The cache size for sequence object ‘%.*ls’ must be greater than 0.
11707 The cache size for sequence object ‘%.*ls’ has been set to NO CACHE.
11708 An invalid value was specified for argument ‘%.*ls’ for the given data type.
11709 The ‘RESTART WITH’ argument cannot be used in a CREATE SEQUENCE statement.
11710 Argument ‘START WITH’ cannot be used in an ALTER SEQUENCE statement.
11711 Argument ‘AS’ cannot be used in an ALTER SEQUENCE statement.
11712 Argument ‘%.*ls’ can not be specified more than once.
11714 Invalid sequence name ‘%.*ls’.
11715 No properties specified for ALTER SEQUENCE.
11716 NEXT VALUE FOR function does not support the PARTITION BY clause.
11717 NEXT VALUE FOR function does not support the OVER clause in default constraints, UPDATE statements, or MERGE statements.
11718 NEXT VALUE FOR function does not support an empty OVER clause.
11719 NEXT VALUE FOR function is not allowed in check constraints, default objects, computed columns, views, user-defined functions, user-defined aggregates, user-defined table types, sub-queries, common table expressions, or derived tables.
11720 NEXT VALUE FOR function is not allowed in the TOP, OVER, OUTPUT, ON, WHERE, GROUP BY, HAVING, or ORDER BY clauses.
11721 NEXT VALUE FOR function cannot be used directly in a statement that uses a DISTINCT, UNION, UNION ALL, EXCEPT or INTERSECT operator.
11722 NEXT VALUE FOR function is not allowed in the WHEN MATCHED clause, the WHEN NOT MATCHED clause, or the WHEN NOT MATCHED BY SOURCE clause of a merge statement.
11723 NEXT VALUE FOR function cannot be used directly in a statement that contains an ORDER BY clause unless the OVER clause is specified.
11724 An expression that contains a NEXT VALUE FOR function cannot be passed as an argument to a table-valued function.
11725 An expression that contains a NEXT VALUE FOR function cannot be passed as an argument to an aggregate.
11726 Object ‘%.*ls’ is not a sequence object.
11727 NEXT VALUE FOR functions for a given sequence object must have exactly the same OVER clause definition.
11728 The sequence object ‘%.*ls’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.
11729 The sequence object ‘%.*ls’ cache size is greater than the number of available values; the cache size has been automatically set to accommodate the remaining sequence values.
11730 Database name cannot be specified for the sequence object in default constraints.
11731 A column that uses a sequence object in the default constraint must be present in the target columns list, if the same sequence object appears in a row constructor.
11732 The requested range for sequence object ‘%.*ls’ exceeds the maximum or minimum limit. Retry with a smaller range.
11733 Parameter ‘%.*ls’ must be a positive integer.
11734 NEXT VALUE FOR function is not allowed in the SELECT clause when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement.
11735 The target table of the INSERT statement cannot have DEFAULT constraints using the NEXT VALUE FOR function when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement.
11736 Only one instance of NEXT VALUE FOR function per sequence object is allowed in SET or SELECT with variable assignment.
11737 NEXT VALUE FOR function does not support the ROWS and RANGE clauses.
11738 The use of NEXT VALUE FOR function is not allowed in this context.
11739 NEXT VALUE FOR function cannot be used if ROWCOUNT option has been set, or the query contains TOP or OFFSET.
11740 NEXT VALUE FOR function cannot be used in a default constraint if ROWCOUNT option has been set, or the query contains TOP or OFFSET.
11741 NEXT VALUE FOR function cannot be used within CASE, CHOOSE, COALESCE, IIF, ISNULL and NULLIF.
11742 NEXT VALUE FOR function can only be used with MERGE if it is defined within a default constraint on the target table for insert actions.

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