There are two types of Messages SQL Server returns

1. Information Message
2. Error Message

An informational Message as the name suggests SQL server sends the messages to the End user or client,

this can be anything including the output of DBCC commands or status of the server or it can also be a

output from the print command An Error message can be a warning message or error message from

SYS.MESSAGES catalog or from a RAISEEROR statement

As we go ahead we will be concentrating mostly on Error Messages in SQL server

There two types of Error Messages in SQL server

1. System
2. User Defined

if there is any exception during the execution,

The database engine picks up the information in the SYS.MESSAGES and sends it to the end user.

As there is need to customize the error messages so that errors are better understood at business

level these are called user defined error.

All the  System error messages and User defined error messages are stored in SYS.MESSAGES

SQL Server stores system and user-defined errors inside a catalog view called sys.messages.

This catalog contains all system error messages, which are available in many languages.

 

Each message has five properties arranged in columns:

■■ message_id:

This column stores the ID of a message and its value.  Together with language_id, this is unique across the instance. Messages with
IDs less than 50000 are system error messages.
■■ language_id:

This column indicates the language used in the message text, for example English or Spanish, as defined in the name column of the
sys.syslanguages system table. This is unique for a specified message_id.
■■ severity:

This column contains the severity level of the message. Whe you are creating errors, keep in mind that the severity must be the same for
all message languages within the same message_id.
■■ is_event_logged:

When you want to log the event when an error israised, set the value of this column equal to1. Like severity, this column must
be the same for all message languages within the same message_id.

■■ text: 

This column stores the text of the message. This text is written in the language indicated by the language_id property.

Severity Levels :  This indicates the severity of the error and this will help us the scope of the problem and how quick you need to respond

these levels are categorized into three main levels

1.   Information Messages:  Level (0 to 10 )

Errors in this group are purely informational and are not severe. Status information or error reports are returned with a specific error code. SQL Server doesn’t use levels 1–9 these levels are available for user-defined errors.

2.     User Errors Level (11- 16)

These do not effect service or System or Server, most of these are corrected by the user and these are errors are raised when database Engine parses the query or Tsql statement for execution

3.  Software Errors (Levels 17–19):

Errors in this group are severe and require system administrator attention as well as yours. They are related to
problems in the Database Engine service and can’t be solved by users.

4.System Errors (Levels 20–25):

Errors in this group are critical since they indicate system problems. They are fatal errors that force the end of
the statement or batch in the Database Engine. Errors in this group record information about what occurred and then terminates. You must be aware that these errors can close the application connection to the instance of SQL Server. Error messages in this severity level group are written to the error log.

Mr  Pinal Dave had explained it in detail take a look at this post

http://blog.sqlauthority.com/2007/04/25/sql-server-error-messages-sysmessages-error-severity-level/

More detailed explanation from BOL

Severity Levels 0 through 19

Error messages with a severity level of 10 are informational. Error messages with severity levels from 11 through 16 are generated by user and can be corrected by the user. Severity levels from 17 and 18 are generated by resource or system errors; the user’s session is not interrupted.

Using sp_addmessage, user-defined messages with severities from 1 through 25 can be added to sysmessages. Only the system administrator can add messages with severities from 19 through 25.

Error messages with severity levels 17 and higher should be reported to the system administrator.

Severity Level 10: Status Information

Severity level 10 is an informational message and indicates a problem caused by mistakes in the information you have entered. Severity level 10 is not visible in SQL Server 7.0.

Severity Levels 11 through 16

These messages indicate errors that can be corrected by the user.

Severity Level 17: Insufficient Resources

These messages indicate that the statement caused SQL Server to run out of resources (such as locks or disk space for the database) or to exceed some limit set by the system administrator.

Severity Level 18: Nonfatal Internal Error Detected

These messages indicate that there is some type of internal software problem, but the statement finishes, and the connection to SQL Server is maintained. For example, a level 18 message occurs when the SQL Server query processor detects an internal error during query optimization.The system administrator should be informed every time a severity level 18 message occurs.

Severity Level 19: SQL Server Error in Resource

These messages indicate that some nonconfigurable internal limit has been exceeded and the current batch process is terminated. Level 19 errors occur rarely; however, they must be corrected by the system administrator or your primary support provider.The administrator should be informed every time a severity level 19 message occurs.

Severity Levels 20 through 25

Severity levels from 20 through 25 indicate system problems. These are fatal errors, which means that the process (the program code that accomplishes the task specified in your statement) is no longer running. The process freezes before it stops, records information about what occurred, and then terminates. The client connection to SQL Server closes, and depending on the problem, the client might not be able to reconnect.

Error messages with a severity level of 19 or higher stop the current batch. Errors messages with a severity level of 20 or higher are considered fatal errors and terminate the client connection. Errors messages in this range may affect all of the processes in the database, and may indicate that a database or object is damaged. Error messages with a severity level from 19 through 25 are written to the error log.

Severity Level 20: SQL Server Fatal Error in Current Process

These messages indicate that a statement has encountered a problem. Because the problem has affected only the current process, it is unlikely that the database itself has been damaged.

Severity Level 21: SQL Server Fatal Error in Database (dbid) Processes

These messages indicate that you have encountered a problem that affects all processes in the current database. However, it is unlikely that the database itself has been damaged.

Severity Level 22: SQL Server Fatal Error Table Integrity Suspect

These messages indicate that the table or index specified in the message has been damaged by a software or hardware problem.Level 22 errors occur rarely; however, if you should encounter one, run DBCC CHECKDB to determine if other objects in the database are also damaged. It is possible that the problem is in the cache only and not on the disk itself. If so, restarting SQL Server corrects the problem. To continue working, you must reconnect to SQL Server. Otherwise, use DBCC to repair the problem. In some cases, it may be necessary to restore the database.If restarting does not help, the problem is on the disk. Sometimes it can be solved by destroying the object specified in the error message. For example, if the message tells you that SQL Server has found a row with a length of 0 in a nonclustered index, delete the index and rebuild it.

Severity Level 23: SQL Server Fatal Error: Database Integrity Suspect

These messages indicate that the integrity of the entire database is suspect due to damage caused by a hardware or software problem.Level 23 errors occur rarely; however, if you should encounter one, run DBCC CHECKDB to determine the extent of the damage. It is possible that the problem is in the cache only and not on the disk itself. If so, restarting SQL Server corrects the problem. To continue working, you must reconnect to SQL Server. Otherwise, use DBCC to repair the problem. In some cases, it may be necessary to restore the database.

Severity Level 24: Hardware Error

These messages indicate some type of media failure. The system administrator might have to reload the database. It might also be necessary to call your hardware vendor.