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 2005, 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.
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 2005. And as per BOL, Microsoft is suggesting to start using THROW statement instead of RAISERROR in New Applications. | Introduced in SQL SERVER 2012. THROW statement seems to be simple and easy to use than RAISERROR. | ||||
| SYNTAX | |||||
|
|
||||
| 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:
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:
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.
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.
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.
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.
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’, 1 RESULT: 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 |
||||
Difference Between Primary Key and Unique Key In Sql Server
Both PRIMARY KEY and UNIQUE KEY
enforces the Uniqueness of the values (i.e. avoids duplicate values) on
the column[s] on which it is defined. Also these key’s can Uniquely
identify each row in database table.
Below table lists out the major difference between PRIMARY KEY and UNIQUE KEY:
Below table lists out the major difference between PRIMARY KEY and UNIQUE KEY:
| PRIMARY KEY | UNIQUE KEY | |
| NULL | It doesn’t allow Null values. Because of this we refer PRIMARY KEY = UNIQUE KEY + Not Null CONSTRAINT |
Allows Null value. But only one Null value. |
| INDEX | By default it adds a clustered index | By default it adds a UNIQUE non-clustered index |
| LIMIT | A table can have only one PRIMARY KEY Column[s] | A table can have more than one UNIQUE Key Column[s] |
| CREATE SYNTAX | Below is the sample example for defining a single column as a PRIMARY KEY column while creating a table:
CREATE TABLE dbo.Customer ( Id INT NOT NULL PRIMARY KEY, FirstName VARCHAR(100), LastName VARCHAR(100), City VARCHAR(50) ) Below is the Sample example for defining multiple columns as PRIMARY KEY. It also shows how we can give name for the PRIMARY KEY: CREATE TABLE dbo.Customer ( Id INT NOT NULL, FirstName VARCHAR(100) NOT NULL, LastName VARCHAR(100), City VARCHAR(50), CONSTRAINT PK_CUSTOMER PRIMARY KEY (Id,FirstName) ) |
Below is the sample example for defining a single column as a UNIQUE KEY column while creating a table:
CREATE TABLE dbo.Customer ( Id INT NOT NULL UNIQUE, FirstName VARCHAR(100), LastName VARCHAR(100), City VARCHAR(50) ) Below is the Sample example for defining multiple columns as UNIQUE KEY. It also shows how we can give name for the UNIQUE KEY: CREATE TABLE dbo.Customer ( Id INT NOT NULL, FirstName VARCHAR(100) NOT NULL, LastName VARCHAR(100), City VARCHAR(50), CONSTRAINT UK_CUSTOMER UNIQUE (Id,FirstName) ) |
| ALTER SYNTAX | Below is the Syntax for adding PRIMARY KEY CONSTRAINT on a column when the table is already created:
ALTER TABLE dbo.Customer ADD CONSTRAINT PK_CUSTOMER PRIMARY KEY (Id) |
Below is the Syntax for adding UNIQUE KEY CONSTRAINTon a column when the table is already created:
ALTER TABLE dbo.Customer ADD CONSTRAINT UK_CUSTOMER UNIQUE (Id) |
| DROP SYNTAX | Below is the Syntax for dropping a PRIMARY KEY:
ALTER TABLE dbo.Customer DROP CONSTRAINT PK_CUSTOMER |
Below is the Syntax for dropping a UNIQUE KEY:
ALTER TABLE dbo.Customer DROP CONSTRAINT UK_CUSTOMER |
Difference Between Sql Server VARCHAR and VARCHAR(MAX) Data Type
Ideally, it is better to compare
Text and Varchar(MAX) data types, as in Sql Server 2005 Varchar(MAX)
data type was introduced as an alternate for Text data type.
Varchar(Max) data type provides multiple advantages over Text data type.
Like many initially when Varchar(MAX)
datatype was introduced in Sql Server 2005, I too was not clear about
the difference between Varchar and Varchar(Max) and which one to use
when. Hope the differences listed in the below table clarifies these
queries.
| Varchar[(n)] | Varchar(Max) | |
| Basic Definition | Non-Unicode Variable Length character data type.Example: DECLARE @FirstName AS VARCHAR(50) = ‘BASAVARAJ’ SELECT @FirstName |
Non-Unicode large Variable Length character data type.Example: DECLARE @FirstName AS VARCHAR(Max)= ‘BASAVARAJ’ SELECT @FirstName |
| Storage Capacity | It can store maximum 8000 Non-Unicode characters (i.e. maximum storage capacity is 8000 bytes of storage). Optional Parameter n value can be from 1 to 8000. | It can store maximum of 2 147 483 647 Non-Unicode characters (i.e. maximum storage capacity is: 2GB). |
| Index? | You can create index on Varchar column data type. Example: CREATE TABLE dbo.Employee (id INT identity(1,1) PRIMARY KEY, FirstName VARCHAR(50)) GO CREATE INDEX IX_EmployeeFirstName ON dbo.Employee(FirstName) GO |
Index can’t be created on a Varchar(Max) data type columns. Example: CREATE TABLE dbo.Employee (id INT identity(1,1) PRIMARY KEY, FirstName VARCHAR(Max)) GO CREATE INDEX IX_EmployeeFirstName ON dbo.Employee(FirstName) GO Error Message: Msg 1919, Level 16, State 1, Line 1 Column ‘FirstName’ in table ‘dbo.Employee’ is of a type that is invalid for use as a key column in an index. |
| How data is stored Physically? | It uses the normal data pages to store the data i.e. it stores the value ‘in a row’. | Sql server will try to store the value ‘in a row’ but if it could not then it will store the value ‘out of row’. i.e. It uses the normal data pages until the content actually fills 8k of data.When overflow happens, data is stored as old TEXT Data Type and a pointer is replacing the old content. |
| No. of Bytes required for each character | It takes 1 byte per character
Example: DECLARE @FirstName AS VARCHAR(50) = ‘BASAVARAJ’ SELECT @FirstName AS FirstName, DATALENGTH(@FirstName) AS Length Result: FirstName Length BASAVARAJ 9 |
It takes 1 byte per characterExample: DECLARE @FirstName AS VARCHAR(MAX)= ‘BASAVARAJ’ SELECT @FirstName AS FirstName, DATALENGTH(@FirstName) AS Length Result: FirstName Length BASAVARAJ 9 |
| Which one to use? | If we know that data to be stored in the column or variable is less than or equal to 8000 characters, then we can use this data type.For example First Name, Last Name etc, columns value can’t cross the max 8000 characters limit, in such scenario’s it is better to use this data type. | If we know that the data to be stored in the column or variable can cross a 8KB Data page, then we can use this data type. |
| Performance | There
is not much performance difference between Varchar[(n)] and
Varchar(Max). Varchar[(n)] provides better performance results compared
to Varchar(Max). If we know that data to be stored in the column or
variable is less than or equal to 8000 characters, then using this
Varchar[(n)] data type provides better performance compared to
Varchar(Max).Example: When I ran the below script by changing the
variable @FirstName type to Varchar(Max) then for 1 million assignments
it is consistently taking double time than when we used data type as
Varchar(50) for variable @ FirstName. DECLARE @FirstName VARCHAR(50), @COUNT INT=0, @StartTime DATETIME = GETDATE() WHILE(@COUNT < 1000000) BEGIN SELECT @FirstName = ‘BASAVARAJ’, @COUNT = @COUNT +1 END SELECT DATEDIFF(ms,@StartTime,GETDATE()) ‘Time Taken in ms’ GO 6 Note: Here GO 6 statement executes the statements above it 6 times. |
|
Difference between Stored Procedure and User Defined Function in Sql Server
Below are the some of the major differences between User Defined Function and Stored Procedure in Sql Server.
To know more on the User-Defined functions with examples please visit the article: User-Defined function.
To know more on the Stored Procedure with examples please visit the article: Stored Procedure.
SET ANSI_NULLS ON
To know more on the User-Defined functions with examples please visit the article: User-Defined function.
To know more on the Stored Procedure with examples please visit the article: Stored Procedure.
| Sl. No. | User Defined function | Stored Procedure |
| 1 | Function must return a value. | Stored procedure may or not return values. |
| 2 | Will allow only Select statement, it will not allow us to use DML statements. | Can have select statements as well as DML statements such as insert, update, delete etc |
| 3 | It will allow only input parameters, doesn’t support output parameters. | It can have both input and output parameters. |
| 4 | It will not allow us to use try-catch blocks. | For exception handling we can use try catch blocks. |
| 5 | Transactions are not allowed within functions. | Can use transactions within Stored procefures. |
| 6 | We can use only table variables, it will not allow using temporary tables. | Can use both table variables aswell as temporary table in it. |
| 7 | Stored procedures can’t be called from function. | Stored Procedures can call functions. |
| 8 | Functions can be called from select statement. | Procedures can’t be called from Select/Where/Having etc statements. Execute/Exec statement can be used to call/execute stored procedure. |
| 9 | UDF can be used in join clause as a result set. | Procedures can’t be used in Join clause |
You may also like to read below other popular articles:
SET ANSI_NULLS ON/OFF Setting in Sql Server
While creating Stored Procedures,
User Defined Functions etc, most of us use the SET ANSI_NULLS ON/OFF and
SET QUOTED_IDENTIFIER ON/OFF Settings. In this article will discuss on
the SET ANSI_NULLS { ON | OFF } Setting.
To understand this with an example, let us create table Name and insert three records in this table as below:
CREATE TABLE dbo.Name(FirstName VARCHAR(50),LastName Varchar(50)) GO INSERT INTO dbo.Name VALUES('BASAVARAJ','BIRADAR'), ('KALPANA','PATIL'), ('MONTY', NULL) GODepending on the ANSI_NULLS setting value either ON or OFF, SQL Server behaves differently while comparing with NULL value.
SET ANSI_NULLS ON
When this setting value is ON (i.e. SET
ANSI_NULLS ON) then comparison with NULL value using = and <>
comparison operator will return false. Below script demonstrates this
fact.
SET ANSI_NULLS ON GO SELECT * FROM dbo.Name WITH(NOLOCK) WHERE LastName = NULL SELECT * FROM dbo.Name WITH(NOLOCK) WHERE LastName <> NULL Result: FirstName LastName ----------------------- -------------------------- (0 row(s) affected) FirstName LastName ----------------------- -------------------------- (0 row(s) affected)
So, when this setting value is ON we need
to use IS NULL or IS NOT NULL instead of comparison operator = and
<>. Below script demonstrates this fact.
SET ANSI_NULLS ON GO SELECT * FROM dbo.Name WITH(NOLOCK) WHERE LastName IS NULL SELECT * FROM dbo.Name WITH(NOLOCK) WHERE LastName IS NOT NULL Result: FirstName LastName ------------------------ ------------------------ MONTY NULL (1 row(s) affected) FirstName LastName ------------------------ ------------------------ BASAVARAJ BIRADAR KALPANA PATIL (2 row(s) affected)
SET ANSI_NULLS OFF
On the other hand if this setting value
is OFF (i.e. SET ANSI_NULLS OFF) then comparison with NULL value using =
and <> comparison operator returns TRUE if the value to
be compared is NULL and NON NULL value respectively. Below Script
demonstrates this fact.
SET ANSI_NULLS OFF GO SELECT * FROM dbo.Name WITH(NOLOCK) WHERE LastName = NULL SELECT * FROM dbo.Name WITH(NOLOCK) WHERE LastName <> NULL Result: FirstName LastName ----------------------- --------------------- MONTY NULL (1 row(s) affected) FirstName LastName ----------------------- --------------------- BASAVARAJ BIRADAR KALPANA PATIL (2 row(s) affected)
Another important point to note is that,
the SET ANSI_NULLS { ON| OFF } setting with which we create Stored
Procedure/UDF etc will be stored in the meta data. So, whenever Stored
Procedure/UDF executes, it will use these setting stored in the meta
data. It will ignore the settings of the client or the calling
application.
Below query can be used to find the objects which are created with SET ANSI_NULLS setting as OFF.
SELECT OBJECT_NAME (object_id) FROM sys.sql_modules WHERE uses_ansi_nulls = 0 -- 0 means OFF and 1 means ON
As per BOL for SQL Server 2008 or 2012,
in future versions this setting value will always be ON and explicitly
setting it to OFF will result in error. So, it is better avoid
explicitly setting this in future development work.
Please correct me, if my understanding is wrong. Comments are always welcome.
Note: All the examples in this article are tested on Sql Server 2008 version
No comments:
Post a Comment