RAISERROR vs THROW

	RAISERROR	THROW
Use	RAISERROR generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the SYS.messages catalog view or build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY…CATCH construct.	THROW raises an exception and transfers execution to a CATCH block of a TRY…CATCH construct in SQL Server 2012 and above versions.
Version Support	RAISERROR introduced in SQL Server 7.0. It supports up to SQL Server 2012. RAISERROR can’t be used in the SQL Server 2014’s Natively compiled Stored Procedures.	THROW introduced in SQL Server version 2012. THROW can be used in the SQL Server 2014 and above versions. Microsoft suggests using THROW instead of using RAISERROR.
Syntax	RAISERROR ( { msg_id | msg_str | @local_variable }      { ,severity ,state }      [ ,argument [ ,...n ] ] )      [ WITH option [ ,...n ] ]  NOTE: RAISERROR WITH NOWAIT statement can also be used to flushes all the buffered PRINT/SELECT Statement Messages within a batch.	THROW [ { error_number | @local_variable },         { message | @local_variable },         { state | @local_variable } ] [ ; ] NOTE: The SQL statement before the THROW statement must be followed by the semicolon (;) statement terminator but the RAISERROR statement may or may not have end with (;).
Can catch original Exception details?	NO. RAISERROR always generates new exception and results in the loss of the original exception details specify with appropriate parameters. EXAMPLE: DECLARE @result INT BEGIN TRY    SET @result = 1/0 -- divide-by-zero error END TRY BEGIN CATCH  DECLARE   @ErrorNumber INT,   @ErrorSeverity INT,   @ErrorState INT,   @ErrorProcedure NVARCHAR(2048),   @ErrorMessage NVARCHAR(2048)    SELECT  --get the error details   @ErrorNumber = ERROR_NUMBER(),   @ErrorSeverity = ERROR_SEVERITY(),   @ErrorState = ERROR_STATE(),   @ErrorProcedure=ERROR_PROCEDURE(),   @ErrorMessage = ERROR_MESSAGE()  PRINT 'BEFORE RAISERROR';  RAISERROR (@ErrorMessage,     @ErrorSeverity, @ErrorState) --WITH NOWAIT          PRINT 'AFTER RAISERROR';              END CATCH PRINT 'AFTER CATCH'; OUTPUT: BEFORE RAISERROR Msg 50000, Level 16, State 1, Line 22 Divide by zero error encountered. AFTER RAISERROR AFTER CATCH NOTE: The actual line number of the code which generated Divided By Zero error here is 3, but the exception message returned by RAISERROR is showing it as 22. 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. THROW the original exception caught in the TRY block; we can just specify the THROW statement without any parameters in the CATCH block. EXAMPLE: BEGIN TRY   DECLARE @RESULT INT = 55/0    END TRY BEGIN CATCH   PRINT 'BEFORE THROW';   THROW;   PRINT 'AFTER THROW' END CATCH   PRINT 'AFTER CATCH' OUTPUT: BEFORE THROW Msg 8134, Level 16, State 1, Line 2 Divide by zero error encountered. NOTE: THROW returns correct error number and line number and also the PRINT statement before the THROW statement must be followed by the semicolon (;).
Execution Termination	In the above example output you can see the PRINT statement executed after the RAISERROR statement.	In the above example output you can see the PRINT statement not executed after the THROW statement.
Setting SEVERITY LEVEL	The severity parameter must set to RAISERROR the severity of the exception.	There is no severity parameter set to THROW. The exception severity is always set to 16. (unless re-throwing in a CATCH block)
RAISE SYSTEM ERROR MESSAGE	With RAISERROR we can raise the system error messages which stored in SYS.Messages table. SELECT * FROM sys.messages WHERE message_id <=50000 - System Messages message_id >50000 - User Defined Messages EXAMPLE: RAISERROR (17051,16,1) OUTPUT: Msg 17051, Level 16, State 1, Line 1 SQL Server evaluation period has expired.	With THROW we can’t raise the System Exception. EXAMPLE: THROW 17051, 'SQL Server evaluation period has expired.', 1; OUTPUT: Msg 35100, Level 16, State 10, Line 7 Error number 17051 in the THROW statement is outside the valid range. Specify an error number in the valid range of 50000 to 2147483647.
sp_add message	sp_addmessage is used for adding user defined message to SYS.Messages table. EXAMPLE: -- Create a user-defined message in U.S. English  EXEC sp_addmessage       @msgnum = 60000,      @severity = 16,      @msgtext = N'The item named %s already exists in %s.',       @lang = 'us_english'; -- Create a user-defined message in French    EXEC sp_addmessage       @msgnum = 60000,      @severity = 16,      @msgtext = N'L''élément nommé %1! existe déjà dans %2!',      @lang = 'French';  GO  SELECT @@LANGUAGE  -- Get to know the system language    SET LANGUAGE 'us_english' –- Change language to us_english    SET LANGUAGE 'French'   -– Change language to French    SET LANGUAGE 'us_english'–- Change language back to us_english
sp_drop message	sp_dropmessage is used for deleting user defined messages from SYS.Messages table. EXAMPLE: -- This statement will fail as long as the localized version  -- of the message exists.  EXEC sp_dropmessage 60000;  OUTPUT: Msg 15280, Level 16, State 1, Procedure sp_dropmessage, Line 64 All localized versions of this message must be dropped before the us_english version can be dropped. -- This statement will drop the message.  EXEC sp_dropmessage      @msgnum = 60000,      @lang = 'all';  OUTPUT: Command(s) completed successfully. -- This statement will remove only the localized version of the   -- message.  EXEC sp_dropmessage      @msgnum = 60000,      @lang = 'French';  OUTPUT: Command(s) completed successfully.
RAISE USER DEFINED  ERROR MESSAGE	With RAISERROR we can raise the user defined error messages which stored in SYS.Messages table. SELECT * FROM sys.messages WHERE message_id <=50000 - System Messages message_id >50000 - User Defined Messages EXAMPLE: RAISERROR (60000,16,1, 'salary', 'emp table') OUTPUT: Msg 60000, Level 16, State 1, Line 4 The item named salary already exists in emp table. NOTE: The msg_str parameter can contain printf formatting styles.	With THROW we can raise the user defined error messages which stored in SYS.Messages table. EXAMPLE: DECLARE @msg NVARCHAR(2048) = FORMATMESSAGE(60000, N'salary', N'emp table'); THROW 60000, @msg, 1; OUTPUT: Msg 60000, Level 16, State 1, Line 15 The item named salary already exists in emp table. NOTE: The message parameter can’t support printf formatting styles. Alternative way is that manipulates the message string using FORMATMESSAGE as shown above example and pass this message string to THROW statement as a parameter.
Summary	If a msg_id is passed to RAISERROR, the ID must be defined in sys.messages. The msg_str parameter can contain printf formatting styles. The severity parameter specifies the severity of the exception.	The error_number parameter does not have to be defined in sys.messages. The message parameter does not accept printf style formatting. There is no severity parameter. The exception severity is always set to 16.

[ALSO READ A FEW MORE...]

• PRIMARY KEY vs UNIQUE KEY
• @@IDENTITY vs SCOPE_IDENTITY()
• SEQUENCE vs IDENTITY
• TINYINT vs SMALLINT
• SMALLINT vs INT
• INT vs BIGINT
• DECIMAL/NUMERIC vs FLOAT
• FLOAT vs REAL
• DECIMAL/NUMERIC vs REAL
• SMALLMONEY vs MONEY
• SMALLDATETIME vs DATETIME
• DATETIME vs DATETIME2(N)
• DATETIMEOFFSET vs DATE
• DATETIMEOFFSET vs TIME
• SYSUTCDATETIME vs SYSUTCDATE
• CHAR vs NCHAR
• CHAR or VARCHAR
• VARCHAR vs NVARCHAR
• VARCHAR vs VARCHAR(MAX)
• TEXT vs VARCHAR(MAX)
• BINARY vs VARBINARY
• IMAGE vs VARBINARY(MAX)
• CAST vs CONVERT
• LEN() vs DATALENGTH()
• UNION vs UNION ALL
• DELETE vs TRUNCATE
• Temporary Table vs Temporary Variable
• LEFT JOIN vs RIGHT JOIN
• LEFT OUTER JOIN vs RIGHT OUTER JOIN
• RAISERROR vs THROW
• Function vs Stored Procedure
• QUOTED_IDENTIFIER ON vs QUOTED_IDENTIFIER OFF

DELETE vs TRUNCATE

What are the differences between DELETE and TRUNCATE [DELETE vs TRUNCATE]?

	DELETE	TRUNCATE
Use	DELETE is used to remove the data from the table. DELETE is a logged operation on a per row basis.  This means that the deletion of each row gets logged and physically deleted. so You can use conditions (WHERE clause) in DELETE.	TRUNCATE is also used to remove the data from the table. TRUNCATE is also a logged operation, but in a different way, This means TRUNCATE logs the de-allocation of the data pages in which the data exists. The de-allocation of data pages means that your data rows still actually exist in the data pages, but the extents have been marked as empty for reuse, so You can’t use conditions (WHERE clause) in TRUNCATE.
SPEED	DELETE is slower than TRUNCATE. Reason: When you type DELETE, all the data get copied into the Rollback Tablespace first, then delete operation get performed. Thatswhy when you type ROLLBACK after deleting a table, you can get back the data (The system gets it for you from the Rollback Tablespace). All this process takes time. But when you type TRUNCATE, it removes data directly without copying it into the Rollback Tablespace.	TRUNCATE is much faster than DELETE. Reason: When you type TRUNCATE, it removes data directly without copying it into the Rollback Tablespace.
ROLLBACK DATA	Once you DELETE, you can get back the data using Rollback.	Once you TRUNCATE, you can’t get back the data. TRUNCATE removes the record permanently.
TRIGGER FIRING	Trigger gets fired when DELETE.	Trigger doesn't get fired when TRUNCATE.
RESET IDENTITY	DELETE will not reset any identity columns to the default seed value.	TRUNCATE will reset any identity columns to the default seed value.
EXAMPLE	CREATE TABLE Parent (RecordId int IDENTITY) GO INSERT Parent DEFAULT VALUES; GO 5 BEGIN TRAN DELETE Parent WHERE RecordId = 3 ROLLBACK SELECT * FROM PARENT RESULT: RecordId 1 2 3 4 5	CREATE TABLE Parent(RecordId int IDENTITY) GO INSERT Parent DEFAULT VALUES; GO 5 TRUNCATE TABLE Parent SELECT * FROM PARENT RESULT: RecordId

[ALSO READ A FEW MORE...]

• PRIMARY KEY vs UNIQUE KEY
• @@IDENTITY vs SCOPE_IDENTITY()
• SEQUENCE vs IDENTITY
• TINYINT vs SMALLINT
• SMALLINT vs INT
• INT vs BIGINT
• DECIMAL/NUMERIC vs FLOAT
• FLOAT vs REAL
• DECIMAL/NUMERIC vs REAL
• SMALLMONEY vs MONEY
• SMALLDATETIME vs DATETIME
• DATETIME vs DATETIME2(N)
• DATETIMEOFFSET vs DATE
• DATETIMEOFFSET vs TIME
• SYSUTCDATETIME vs SYSUTCDATE
• CHAR vs NCHAR
• CHAR or VARCHAR
• VARCHAR vs NVARCHAR
• VARCHAR vs VARCHAR(MAX)
• TEXT vs VARCHAR(MAX)
• BINARY vs VARBINARY
• IMAGE vs VARBINARY(MAX)
• CAST vs CONVERT
• LEN() vs DATALENGTH()
• UNION vs UNION ALL
• DELETE vs TRUNCATE
• Temporary Table vs Temporary Variable
• LEFT JOIN vs RIGHT JOIN
• LEFT OUTER JOIN vs RIGHT OUTER JOIN
• RAISERROR vs THROW
• Function vs Stored Procedure
• QUOTED_IDENTIFIER ON vs QUOTED_IDENTIFIER OFF