Implementing Error handling raising User Defined Errors ( part 3):

In the last post we seen how to manage UDF errors (Add , Update , delete)  UDF error messages

in SYS.MESSAGES catalog. In this post we will look in-detail in to the Use RAISERROR function

in SQL Server to raise UDF errors. When I decided to write this, I then remembered that I written

a post for SQL server 2o12 Throw statement which will explain the differences of  raiserror and throw

, disadvantages of raiserrors and how to implement raiserror and throw, and how to raise UDF

errors , so have look at this post


but I will go through  some aspects of Raiserror that I didn’t cover  in the above post

lets start with syntax of raiseerror

RAISERROR ( { msg_id | msg_str | @local_variable }
{,severity,state }
[,argument [,…n ] ] )
[ WITH option [,…n ] g


We talked about Msg_id in the earlier post, we will look at the msg_str

Is a user-defined message with formatting similar to the printf function in the C standard library. The error message can have a maximum of 2,047 characters. If the message contains 2,048 or more characters, only the first 2,044 are displayed and an ellipsis is added to indicate that the message has been truncated.


msg_str is a string of characters with optional embedded conversion specifications. Each conversion specification defines how a value in the argument list is formatted and placed into a field at the location of the conversion specification in msg_str. Conversion specifications have this format:

% [[flag] [width] [. precision] [{h | l}]] type


Is a code that determines the spacing and justification of the substituted value.

Code Prefix or justification Description
– (minus) Left-justified Left-justify the argument value within the given field width.
+ (plus) Sign prefix Preface the argument value with a plus (+) or minus (-) if the value is of a signed type.
0 (zero) Zero padding Preface the output with zeros until the minimum width is reached. When 0 and the minus sign (-) appear, 0 is ignored.
# (number) 0x prefix for hexadecimal type of x or X When used with the o, x, or X format, the number sign (#) flag prefaces any nonzero value with 0, 0x, or 0X, respectively. When d, i, or u are prefaced by the number sign (#) flag, the flag is ignored.
‘ ‘ (blank) Space padding Preface the output value with blank spaces if the value is signed and positive. This is ignored when included with the plus sign (+) flag.


Is an integer that defines the minimum width for the field into which the argument value is placed. If the length of the argument value is equal to or longer than width, the value is printed with no padding. If the value is shorter than width, the value is padded to the length specified in width.

An asterisk (*) means that the width is specified by the associated argument in the argument list, which must be an integer value.

Is the maximum number of characters taken from the argument value for string values. For example, if a string has five characters and precision is 3, only the first three characters of the string value are used.

For integer values, precision is the minimum number of digits printed.

An asterisk (*) means that the precision is specified by the associated argument in the argument list, which must be an integer value.

{h | l} type

Is used with character types d, i, o, s, x, X, or u, and creates shortint (h) or longint (l) values.

Type specification Represents
d or i Signed integer
o Unsigned octal
s String
u Unsigned integer
x or X Unsigned hexadecimal


Is a custom option for the error and can be one of the values in the following table.

Value Description
LOG Logs the error in the error log and the application log for the instance of the Microsoft SQL Server Database Engine. Errors logged in the error log are currently limited to a maximum of 440 bytes. Only a member of the sysadmin fixed server role or a user with ALTER TRACE permissions can specify WITH LOG.
NOWAIT Sends messages immediately to the client.
SETERROR Sets the @@ERROR and ERROR_NUMBER values to msg_id or 50000, regardless of the severity level.




Raiserror will not work if it is used

  • Outside the scope of any TRY block.
  • With a severity of 10 or lower in a TRY block.
  • With a severity of 20 or higher that terminates the database connection.

















DECLARE @SERVER VARCHAR(50) = ‘testserver’
–A custom error message using arguments
RAISERROR (‘This is a custom error message.
Login: %s,
Language: %s,
SPID: %u,
Server Name: %s’, 5,1,

Managing User-Defined Errors – Part 2

in the earlier post we learned about error messages in SQL Server

In this post we will look at how to Implement and Manage User Defined errors

as we learned in the first post that it is very important for any organization to Implement User defined error messages in any business logic  or for any front end application as some times user may not understand the cryptic system error messages

We can solve this problem by adding and managing our own error messages to the SYS.MESSAGES Catalog using a


To add User defined error message to SYS.MESSAGES , we use the SP_ADDMESSAGE

sp_addmessage [ @msgnum = ] msg_id

,[ @severity = ] severity

,[ @msgtext = ] ‘msg’
[, [ @lang = ] ‘language’ ]
[, [ @with_log = ] { ‘TRUE’ | ‘FALSE’ } ]
[, [ @replace = ] ‘replace’ ]

SP_ADDMESSAGE has six input parameters

@msgnum is the Message_id (int) column in SYS.MESSAGES , @msgnum can be any number between 50,001 to 2,147,483,647

1 to 50,000 are reserved for system error messages

@severity is the level of severity scope of the error message , 1 to 25

@msgtxt is a NVARCHAR(255)  text column and it supports arguments , we will talk about arguments later in this post

the above three parameters are mandatory to add a message

Message_id and langid in the sys.messages form a composite key , so there can be entries in SYS.MESSAFES

with same messageid’s in different langauges

@lang is the type of language the message will be in , sys.syslanguages has the list of languages the sql server supports , SQL Server supports 32

different languages.

@with_log this parameter accepts either ‘TRUE’ or ‘FALSE’

For a user to specify this parameter user should be a member of SYSADMIN role

There two logs that we will deal with is one windows application log and the second one is database engine log

When this parameter is set to true the error is written to windows application log and database engine log

When this parameter is set to false the error is written to only database engine log

@replace  is used to alter the existing messages

we can Severity , Message text and With log option of error messages



— To add a UDF error message to sys.messages
–@Msgnum : range 50,001 to 2,147,483,647
–@Severity: 1 to 25
–@Msgtext : NVARCHAR(255) , accpets arguments
–@with_log : TRUE/FALSE
–@lang     : by default it’s the sessions default lanuguage or language in sys.syslangusges
–@replace  : is used to alter the messages, we can alter Severity,Message text,With_log

sp_addmessage @msgnum =70000, @severity = 24, @msgtext = ‘test error log’

— Using @replace option we can update Severity
— ,with_log and message text

sp_addmessage @msgnum = 70000, @severity = 20
, @msgtext = ‘updated error log’,@with_log =’true’, @replace =’replace’

Select * from sys.messages where message_id = 70000
— SP_ALTERMESSAGE is used only to update the WITH_lOG option
— Access to only members of SYSADMIN roles

sp_altermessage 70000,’WITH_LOG’,’FALSE’
Select * from sys.messages where message_id = 70000

sp_dropmessage @msgnum = 70000 , @lang = ‘us_english’

–@lang is optional , if a message with different languages should be
— droped then ALL is specified in the lang parameter

Implimenting Error Handling in SQL Server – Part 1

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

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.

Implementing CLR User defined Aggregates in SQL Server

In this post we will learn how to implement UDF CLR Aggregates ,

In order to implement CLR UDF aggegate assembly we need to follow set of rules  or requirements in technical terms, if any of rules are broken then it is not a valid


  1. Like any other CLR assembles in SQL Server, a CLR UDF  Aggregate should have a attribute called SqlUserDefinedAggregat

  2.    In order to save the Intermediate state of Aggregation we use Four methods in the Aggregation

    1. Init
    2. Accumulate
    3. Merge
    4. Terminate

Init : 

Syntax :  public void Init();

Once this method is invoked the Query processor Initializes the aggregation , for every aggregation we need to invoke this method.


Syntax: public void Accumulate ( input-type value[, input-type value, …]);

Input type is any managed sql server data types

This where actual data aggregation occurs , it may be summing or multiplying , counting.


This method can be used to merge another instance of this aggregate class with the current instance. The query processor uses this method to merge multiple partial computations of an aggregation.


public return_type Terminate();

This method completes the aggregate computation and returns the result of the aggregation. The return_type should be a managed SQL Server data type.

The basic aggregate also has four attributes which are described in Andy Novak’s article.

Attribute Comment
Format This is a compulsory enum with options Native, Unknown and UserDefined.
IsInvariantToNulls Setting this to true tells the aggregate that it doesn’t care whether or not values are null or not, it will always produce the same result.
An example of an aggregate where the value for this is false is COUNT(field) which only counts instances that are NOT NULL
IsInvariantToDuplicates Setting this to true tells the aggregate that it will produce the same results even if there are duplicate entries.
MAX() is a good example of this as no matter how many duplicates there are the MAX value will still be the MAX value.
IsInvariantToOrder Setting this to true affects how SQL Server calls the Merge method of the aggregate.
If you had a string concatenation aggregate that was parallelised then it is vitally important that the streams merge back together in the correct order.


using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public struct CountVowels
    // count only the vowels in the passed-in strings
    private SqlInt32 countOfVowels;

    public void Init()
        countOfVowels = 0;

    public void Accumulate(SqlString value)
        // list of vowels to look for
        string vowels = "aeiou";

        // for each character in the given parameter
        for (int i=0; i < value.ToString().Length; i++)
            // for each character in the vowels string
            for (int j=0; j < vowels.Length; j++)
                // convert parameter character to lowercase and compare to vowel
                if (value.Value.Substring(i,1).ToLower() == vowels.Substring(j,1))
                    // it is a vowel, increment the count

    public void Merge(CountVowels value)

    public SqlString Terminate()
        return countOfVowels.ToString();

After deploying the aggregate, test it by executing it on the SQL Server and verifying the correct data is returned.

This query returns a result set of the vowel count for all the values in the LastNames column in the Contact table.

SELECT LastName, COUNT(LastName) AS CountOfLastName, dbo.CountVowels(LastName) AS CountOfVowels
FROM Person.Contact

Implementing CLR User defined Scalar Functions in SQL server

If you want know how to create a SQL CLR project in Visual studio

look at this post

lets create a scalar function

What is_deterministic , Userdataaccess, Systemdataaccess please use below links

Property Explanation
IsDeterministic : True if the function will always return the same value, if called with the same parameters. False if the function                                                       can return different values when called with the same parameters. An example of a deterministic function is the SayHello function.                                                                   An example of a nondeterministic function is a function that will use database data that varies.

DataAccess Specifies whether the function will read data from the context SQL Server connection. Acceptable valuesare DataAccessKind.None and DataAccessKind.Read.

SystemDataAccess Specifies whether the function will require access to system catalogs or system tables in SQL Server—for
example, sys.object. Acceptable values are System DataAccessKind.None and SystemDataAccessKind.Read.

IsPrecise Specifies whether the function will be performing imprecise computation, for example, float operations.True if the function does not involve these
operations, false if it does. SQL Server uses this information for indexing.

FillRowMethodName Used for table-valued functions. This is the name of a method in the same class that serves as the
table-valued function contract.

TableDefinition Used for table-valued functions. Defines the column list in the table returned.

Name The name of the SQL function.

Use the .DLL and register the assembly in sql server  and create a function


















using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
[Microsoft.SqlServer.Server.SqlFunction(DataAccess= DataAccessKind.None,
IsDeterministic= true,SystemDataAccess=SystemDataAccessKind.None,IsPrecise=true)]
public static SqlInt32 ScalarUdf(SqlInt32 x, SqlInt32 y)

SqlInt32 z = x + y;

return (z);


USE [test]
FROM ‘C:\Users\akhil\Documents\Visual Studio 2010\Projects\SQL CLR\CLR function\bin\Debug\CLR function.dll’



create function sumxy (@x int, @y int)

returns int


external name [CLR function].UserDefinedFunctions.ScalarUdf



select dbo.sumxy(1,10)

Implementing SQL CLR objects in SQL Server

Using  CLR integration we can create

  • CLR Stored Procedure
  • CLR User Defined Function (Scalar and Table valued)
  • CLR Trigger
  • CLR User defined data types
  • CLR Aggregates

Let see how to create a CLR object using Visual studio

Before even developing a CLR object  , we have to know what version of .Net framework is sql server is using for the CLR environment

I using the Sql sever 2008 , we can see here CLR runs in .Net framework v2

With the .net framework 4.0 we now have the ability to load two or more distinct versions of the CLR within a single process. In previous releases of the .net framework, a process could only load a single instance of the CLR. Given this restriction the CLR team recommended that hosts, such as SQL Server, use the LockClrVersion function to determine the version of the CLR to load prior to initialization. So although we are developing our objects in .Net framework v3.5 we will be still able add the assembly to the sql server






Creating SQL CLR project


FROM ‘C:\Users\akhil\Documents\Visual Studio 2010\Projects\SQL CLR\SQL CLR\bin\Debug\SQL CLR.dll’















TITLE: Microsoft SQL Server Management Studio

Create failed for SqlAssembly ‘SQL CLR’. (Microsoft.SqlServer.Smo)

For help, click:


An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)


CREATE ASSEMBLY for assembly ‘SQL CLR’ failed because assembly ‘SQL CLR’ is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission. (Microsoft SQL Server, Error: 10327)

For help, click:

Introduction to CLR in SQL Server


What is CLR ?

The common language run time(CLR) is the virtual machine component of Microsoft’s .NET framework and is responsible for managing the execution of .NET programs. In a process known as just-in-time (JIT) compilation, the CLR compiles the intermediate language code known as Common Intermediate Language (CIL)into the machine instructions that in turn are executed by the computer’s CPU. The CLR provides additional services including memory management, type safety and exception handling. All programs written for the .NET framework, regardless of programming language, are executed by the CLR. It provides exception handling, Garbage collection and thread management.

The CLR is Microsoft’s implementation of the Common Language Infrastructure (CLI) standard.

What does it means when CLR is integrated into SQL server ?

  • CLR was introduced in SQL server 2005
  • CLR integration provides you with the flexibility to define stored procedures, functions, triggers, data types, andother database objects using either Transact-SQL or any .NET Framework language , So you can extend the functionality SQL server programmable objects by using any .NET framework language

CLR and Manage code

The .NET Framework includes a compiler component for each .NET-compliant language. This component compiles the source code written in the specific high-level
language to the lower-level Microsoft Intermediate Language (MSIL). When the application is running, the CLR translates MSIL to native machine instructions. This
is done by a component known as just-in-time ( JIT) compilation. It is important to understand that no matter which .NET language you develop your application in,
it will be compiled to the one common language—MSIL.
The CLR is responsible for executing MSIL code. The CLR also manages the code that runs within it by providing the code with runtime services.
The CLR manages code execution by providing mandatory stability and security-checking engines, as well as resource management. For example, the
Garbage Collection service provided by the CLR ensures optimal memory management. Thread management provides parallel processing capabilities. The Base
Class Library support, error management, and debugging services create a consistent development environment for all .NET languages.
Code that runs within the CLR is known as managed code , as the CLR provides it with management services like those mentioned above. The term is often used to
distinguish between .NET code and native code (for example, code written using Visual Basic 6 or C++). Native code runs outside the CLR and is referred to as unmanaged code . Unmanaged code is not subject to CLR services, like code access security. Within SQL Server, all objects that use CLR integration are written as managed code. Extended stored procedures (XPs) are unmanaged code and are written using C++.

Base class library

The Base Class Library (BCL) is a standard library available to all languages using the .NET Framework. .NET includes the BCL in order to encapsulate a large number of common functions, such as file reading and writing, graphic rendering,database interaction, and XML document manipulation, which makes the programmer’s job easier. It is much larger in scope than standard libraries for most other languages, including C++, and is comparable in scope and coverage to thestandard libraries of Java. The BCL is sometimes incorrectly referred to as the Framework Class Library (FCL), which is a superset including the Microsoft.* namespaces.

Standardized namespaces

These are the namespaces that are standardized as of the ECMA 335 and ISO/IEC 23271:2006 standards.

This namespace includes the core needs for programming. It includes base types like String, DateTime, Boolean, and so forth, support for environments such as the console, math functions, and base classes for attributes, exceptions, andarrays.
Defines many common containers or collections used in programming, such as lists, queues, stacks, hashtables, and dictionaries. It includes support for generics.
Provides the ability to diagnose applications. It includes event logging, performance counters, tracing, and interaction with system processes.
Provides help for writing internationalized applications. “Culture-related information, including the language, the country/region, the calendars in use, [and] the format patterns for dates, currency, and numbers” can be defined.
Enables reading from and writing to different streams, such as files or other data streams. Also provides a connection to the file system.[6]
Provides an interface “for many of the protocols used on networks today”, such as HTTP, FTP, and SMTP. Secure communication is supported by protocols such as SSL.
Provides an object view of types, methods, and fields; and “the ability to dynamically create and invoke types”. It exposes the API to access the Reflective programming capabilities of CLR.
Allows management of the runtime behavior of an application or the CLR. Some of the included abilities are interoperable with COM or other native code, writing distributed applications, and serializing objects into binary or SOAP.
“Provides the underlying structure of the common language runtime security system.” This namespace allows security to be built into applications based on policy and permissions. It provides services such as cryptography.
Supports various encodings, regular expressions, and a more efficient mechanism for manipulating strings (StringBuilder).
Helps facilitate multithreaded programming. It allows the synchronizing of “thread activities and access to data” and provides “a pool of system-supplied threads.”
“Provides standards-based support for processing XML,” including reading, writing, schemas, serialization, searching, and transforming.

But not all base classes are supported by clr in sql server , check this post for the list of supported name spaces in SQL server

Even if the class you wish to call is in an unsupported assembly, it doesn’t mean you cannot use it. Any custom assembly that you write yourself is unsupported,
unless you can persuade the makers of SQL Server to test it for you. This unlikely event notwithstanding, you will have to use the unsupported library at your ownrisk. Unsupported libraries are not loaded by SQL Server by default, and therefore you have to register them explicitly.  When using functionality from an unsupported assembly, you are responsible for testing it and ensuring it performs well, without affecting the stability of SQL Server.

What is an assembly? and  what is this to do with CLR Stored procedures, User Defined Functions, Triggers, User Defined Data Types ?

Assemblies are the building blocks of .NET Framework applications; they form the fundamental unit of deployment, version control, reuse, activation scoping, and security permissions. An assembly is a collection of types and resources that are built to work together and form a logical unit of functionality. An assembly provides the common language runtime with the information it needs to be aware of type implementations. To the runtime, a type does not exist outside the context of an assembly. Physically it can be a Executable file or *.dll (Dynamic linked library) , basically these Assemblies consist MSIL code and these assemblies may refer other assemblies

These assemblies are stored external to SQL server and before  you can use an assembly from SQL Server you must register it. When you register the assembly, it is imported into SQL Server and configured for use. Often assemblies depend on other assemblies, which in turn depend on others, and so on, forming a dependency tree. During registration, the entire chain of dependencies for your assembly must be imported into SQL Server.

During the execution the SQL Server the brings the assembly code(CIL) in to the sql server execution environment which is a CLR and converts the MSIL code to the machine instructions

Assemblies are registered using the CREATE ASSEMBLY

uploads an assembly that was previously compiled as a .dll file from managed code for use inside an instance of SQL Server

statement as shown below

CREATE ASSEMBLY assembly_name [ AUTHORIZATION owner_name ]

FROM { <client_assembly_specifier> | <assembly_bits> [ ,…n ] }


[ ; ] <client_assembly_specifier> :: = ‘[\\computer_name\]share_name\[path\]manifest_file_name’ | ‘[local_path\]manifest_file_name’ <assembly_bits> :: = { varbinary_literal | varbinary_expression }

assembly_name                                                  :Is the name of the assembly that will be registered in the SQL server , Which should be unique with in the database

AUTHORIZATION [owner_name]                 :owner_name must either be the name of a role of which the current user is a member, or the current user must have  IMPERSONATE permission on owner_name(if username is used). If not specified, ownership is given to the current user. Recommended role to use [DBO]

<client_assembly_specifier>                            :Specifies the local path or network location where the assembly that is being uploaded is located, and also the manifest  file name that corresponds to the assembly. <client_assembly_specifier> can be expressed as a fixed string or an expression evaluating to a fixed string, with variables. CREATE ASSEMBLY does not support loading multimodule assemblies. SQL Server also looks for any dependent assemblies of this assembly in the same location and also uploads them with the same owner as the root level assembly


Before talking about these three options we need to know little about CLR CAS(Code Access Security)

What is CAS security?

The CLR supports a security model called code access security (CAS) for managed code. In this model, permissions are granted to assemblies based on the identity of the code, as opposed to the identity of the user or process running the code. CAS permissions are granted based on evidence and policies. For example, the origin of an assembly is a form of evidence, and a policy could specify that code downloaded from the Internet should be granted a very limited set of permissions. System administrators set policies, removing security decisions from users and developers. The security policy that determines the permissions granted to assemblies is defined in three different places:

  • Machine policy: This is the policy that is in effect for all managed code running in the machine on which SQL Server is installed.
  • User policy: This is the policy that is in effect for managed code hosted by a process. For SQL Server, the user policy is specific to the Windows account on which the SQL Server service is running.
  • Host policy: This is the policy that is set up by the host of the CLR (in this case, SQL Server) that is in effect for managed code running in that host.

What is CAS security?














How does CAS security works?





















The set of CAS permissions that are granted to managed code when running inside SQL Server is the intersection of the set of permissions granted by the above three

policy levels. Even if SQL Server grants a set of permissions to an assembly loaded in SQL Server, the eventual set of permissions given to user code may be restricted

further by the user and machine-level policies. The security model of the Microsoft SQL Server integration with the Microsoft .NET Framework common language

runtime (CLR) manages and secures access between different types of CLR and non-CLR objects running within SQL Server. These objects may be called by a Transact-

SQL statement or another CLR object running in the server.






Permission Sets:

The SQL Server host policy level is determined by the permission set specified when creating the assembly.

There are three Permissions

  1. SAFE

SAFE:  Only internal computation and local data access are allowed. SAFE is the most restrictive permission set. Code executed by an assembly with SAFE permissions cannot access external system resources such as files, the network, environment variables, or the registry, and this is the default permission set.

If assembly is set to safe and during the execution if it tries to access external resources like file system or network , it will fail.

EXTERNAL_ACCESS:  is the permission set you will assign to your assembly if you require it to access external resources. Assemblies registered with the
EXTERNAL_ACCESS permission set can access and modify the file system, write to event logs, use network services like DNS and Active Directory, create outbound
network connections including Web access (port 80 and 443) and SMTP mail access  (port 25), and access HKEY_CLASSES_ROOT, HKEY_LOCAL_MACHINE,

UNSAFE: UNSAFE allows assemblies unrestricted access to resources, both within and outside SQL Server. Code executing from within an UNSAFE assembly can call unmanaged code.


Accessing External Resources

If a user-defined type (UDT), stored procedure, or other type of construct assembly is registered with the SAFE permission set, then managed code executing in the construct is unable to access external resources. However, if either the EXTERNAL_ACCESS or UNSAFE permission sets are specified, and managed code attempts to access external resources, SQL Server applies the following rules:

If Then
The execution context corresponds to a SQL Server login. Attempts to access external resources are denied and a security exception is raised.
The execution context corresponds to a Windows login and the execution context is the original caller. The external resource is accessed under the security context of the SQL Server service account.
The caller is not the original caller. Access is denied and a security exception is raised.

Calling Code Through Links

The SQL Server CLR integration security model is intended to govern code executing inside of SQL Server, and is intended to prevent elevation of privilege attacks against protected system resources from managed code. User-defined code can call other code inside of SQL Server, either in Transact-SQL or in one of the managed languages. The relationships between these objects are referred to as links. The three types of links are described in the following table.

Link type Description
Invocation Corresponds to a code invocation, from a user calling either an object (such as a Transact-SQL batch calling a stored procedure), or a CLR stored procedure or function.
Table access Corresponds to retrieving or modifying values in a table, view, or a table-valued function. Similar to invocation links, except they have a finer-grained access control in terms of SELECT, INSERT, UPDATE, and DELETE permissions.
Gated between two objects Permissions are not checked across that relationship, once it has been established.
selecting the proper Permission set for the assembly code  is very important :
What is ISTRUSTWORTHY option?
Check this link

How does the Buffer manager writes the data in the sql server

How does sql server writes the data , how does it enforces ACID property ,  why does  it use a LOG file ? what is write a head? What is a Dirty page ?

Are you looking answers for the above questions , then you don’t need to worry because you came to right place


Before answering the above questions , you need to know what is logical write and physical write

What is Logical write?

When the page is modified in the Buffer cache it is called a Logical write

What is Physical write?

When a page is written from a Buffer cache to a physical disk it is called a Physical write


How it works

when a user runs an update query after it is parsed successfully the query engine finds the efficient way to access the data by comparing the different execution plans, then these keys are passed to the  storage engine which finds the exact pages in the file and brings these pages into the buffer cache(Physical read) , once the pages are in the buffer cache , the data inside the page/s will be updated in the buffer cache(logical write), once these pages inside the buffer cache are updated the data is not directly written into the database file , first the updated pages are marked as dirty pages, a dirty page can under go any number of logical writes and for each logical write the logrecord is sent to the log cache as a log record , the log record is then inserted into the log file , after log record written into the disk(log file) the dirty page is then moved from buffer cache into the disk(data file) this is called flushing the page. SQL Server uses a technique known as write-ahead logging that prevents writing a dirty page before the associated log record is written to disk(data file)


When the buffer manager writes a page, it searches for adjacent dirty pages that can be included in a single gather-write operation. Adjacent pages have consecutive page IDs and are from the same file; the pages do not have to be contiguous in memory. The search continues both forward and backward until one of the following events occurs:

  • A clean page is found.
  • 32 pages have been found.
  • A dirty page is found whose log sequence number (LSN) has not yet been flushed in the log.
  • A page is found that cannot be immediately latched.

    In this way, the entire set of pages can be written to disk with a single gather-write operation.


How Buffer Manager reads the data from database files

What is a logical read in SQL Server ?

When Query engine processor requests page from Buffer cache

What is a Physical read in SQL server?

When a requested page is not available in Buffer pool a Physical read then  reads the page from physical disk and copies into the Buffer cache

When there is a request to access the data the most effective to access the data is controlled by the relational engine(table scan or index scan) and once it is determined

then the Buffer manager in the storage engine optimizes the best way to get the data


  • The Database Engine supports a performance optimization mechanism called read-ahead
  • Read-ahead anticipates the data and index pages needed to fulfill a query execution plan and brings the pages into the buffer cache before they are actually used by the query, so that processor starts utilizing the data and IO will be still reading the data , so CPU and IO both work simultaneously.
  • The read-ahead mechanism allows the Database Engine to read up to 64 contiguous pages (512KB) from one file
  • The read is performed such way that that IO can read non-contiguous pages still write in to one buffer or IO can still read from multiple buffer and write into a single stream of data this is called  scatter/gather I/O

Reading Data Pages

Table scans used to read data pages are very efficient in the Database Engine. The index allocation map (IAM) pages in a SQL Server database list the extents used by a table or index. The storage engine can read the IAM to build a sorted list of the disk addresses that must be read. This allows the storage engine to optimize its I/Os as large sequential reads that are performed in sequence, based on their location on the disk

Reading Index Pages

  Clustered Indexes

The storage engine uses the information in the intermediate index page above the leaf level to schedule serial read-aheads.

Non- Clustered Indexes

The storage engine uses prefetching to speed base table lookups from nonclustered indexes. The leaf rows of a nonclustered index contain pointers to the data rows that contain each specific key value. As the storage engine reads through the leaf pages of the nonclustered index, it also starts scheduling asynchronous reads for the data rows whose pointers have already been retrieved. This allows the storage engine to retrieve data rows from the underlying table before it has completed the scan of the nonclustered index. Prefetching is used regardless of whether the table has a clustered index


Advanced scanning:


the advanced scan feature allows multiple tasks to share full table scans. If the execution plan of a Transact-SQL statement requires a scan of the data pages in a table and the Database Engine detects that the table is already being scanned for another execution plan, the Database Engine joins the second scan to the first, at the current location of the second scan. The Database Engine reads each page one time and passes the rows from each page to both execution plans. This continues until the end of the table is reached.

At that point, the first execution plan has the complete results of a scan, but the second execution plan must still retrieve the data pages that were read before it joined the in-progress scan. The scan for the second execution plan then wraps back to the first data page of the table and scans forward to where it joined the first scan. Any number of scans can be combined like this. The Database Engine will keep looping through the data pages until it has completed all the scans. This mechanism is also called “merry-go-round scanning” and demonstrates why the order of the results returned from a SELECT statement cannot be guaranteed without an ORDER BY clause.

Buffer management in SQL Server


Its very important to know how buffer manager works in the sql server to understand the query processing engine

Buffer management is a key component in achieving this efficiency. The buffer management component consists of two mechanisms:

  • The buffer manager to access and update database pages
  •  buffer cache (also called the buffer pool), to reduce database file I/O.

How Buffer management works in SQL server

  • Buffer cache is divided into  8 Kb pages
  • Buffer management performs two important functions
    • Reading data or index pages from the database disk files into the buffer cache
    • Writing modified pages back to disk
  • A page remains in the buffer cache until the buffer manager needs the buffer area to read in more data.
  •  Data is written back to disk only if it is modified. Data in the buffer cache can be modified multiple times before being written back to disk

    How is buffer cache is allocated:

When SQL Server starts, it computes the size of virtual address space for the buffer cache based on a number of parameters such as the amount of physical memory on the system, the configured number of maximum server threads, and various startup parameters

The two important counters for buffer management are BPOOL_COMMITTED & BPOOL_COMMIT_TARGET in the catalog view SYS.DM_OS_SYS_INFO

SQL Server reserves this computed amount of its process virtual address space (called the memory target) for the buffer cache, but it acquires (commits) only the required amount of physical memory for the current load


Number of 8-KB buffers in the buffer pool. This amount represents committed physical memory in the buffer pool. Does not include reserved memory in the buffer pool.

BPOOL_COMMIT_TARGET (No pages SQL server acquired for buffer management but not commited) represents

Number of 8-KB buffers needed by the buffer pool. The target amount is calculated using a variety of inputs such as the current state of the system, including its load, the memory requested by current processes, the amount of memory installed on the computer, and configuration parameters. If the bpool_commit_target is larger than the bpool_committed value, the buffer pool will try to obtain additional memory. If the bpool_commit_target is smaller than the bpool_committed value, the buffer pool will shrink.

The time from sql server started and the buffer cache acquires memory target is called Ramp up

  •  single-page read request fills a single buffer page
  •  Ramp-up is expedited by transforming single-page read requests into aligned eight-page requests

Because the buffer manager uses most of the memory in the SQL Server process, it cooperates with the memory manager to allow other components to use its buffers. The buffer manager interacts primarily with the following components:

  • Resource manager to control overall memory usage and, in 32-bit platforms, to control address space usage.
  • Database manager and the SQL Server Operating System (SQLOS) for low-level file I/O operations.
  • Log manager for write-ahead logging.

Buffer manager features :

  • The buffer manager supports Hot Add Memory, which allows users to add physical memory without restarting the server.
  • The buffer manager supports large pages on 64-bit platforms. The page size is specific to the version of Windows
  • The buffer manager is non-uniform memory access (NUMA) aware

The buffer manager only performs reads and writes to the database. Other file and database operations such as open, close, extend, and shrink are performed by the database manager and file manager components.

Disk I/O operations by the buffer manager have the following characteristics:

  • All I/Os are performed asynchronously, which allows the calling thread to continue processing while the I/O operation takes place in the background.
  • All I/Os are issued in the calling threads unless the affinity I/O option is in use. The affinity I/O mask option binds SQL Server disk I/O to a specified subset of CPUs. In high-end SQL Server online transactional processing (OLTP) environments, this extension can enhance the performance of SQL Server threads issuing I/Os.
  • Multiple page I/Os are accomplished with scatter-gather I/O, which allows data to be transferred into or out of noncontiguous areas of memory. This means that SQL Server can quickly fill or flush the buffer cache while avoiding multiple physical I/O requests.

Long I/O Requests

The buffer manager reports on any I/O request that has been outstanding for at least 15 seconds. This helps the system administrator distinguish between SQL Server problems and I/O subsystem problems. Error message 833 is reported and appears in the SQL Server error log as follows:

SQL Server has encountered %d occurrence(s) of I/O requests taking longer than %d seconds to complete on file [%ls] in database [%ls] (%d). The OS file handle is 0x%p. The offset of the latest long I/O is: %#016I64x.

A long I/O may be either a read or a write; it is not currently indicated in the message. Long-I/O messages are warnings, not errors. They do not indicate problems with SQL Server. The messages are reported to help the system administrator find the cause of poor SQL Server response times more quickly, and to distinguish problems that are outside the control of SQL Server. As such, they do not require any action, but the system administrator should investigate why the I/O request took so long, and whether the time is justifiable.

Causes of Long-I/O Requests

A long-I/O message may indicate that an I/O is permanently blocked and will never complete (known as lost I/O), or merely that it just has not completed yet. It is not possible to tell from the message which scenario is the case, although a lost I/O will often lead to a latch time-out.

Long I/Os often indicate a SQL Server workload that is too intense for the disk subsystem. An inadequate disk subsystem may be indicated when:

  • Multiple long I/O messages appear in the error log during a heavy SQL Server workload.
  • Perfmon counters show long disk latencies, long disk queues, or no disk idle time.

Long I/Os may also be caused by a component in the I/O path (for example, a driver, controller, or firmware) continually postponing servicing an old I/O request in favor of servicing newer requests that are closer to the current position of the disk head. The common technique of processing requests in priority based upon which ones are closest to the current position of the read/write head is known as “elevator seeking.” This may be difficult to corroborate with the Windows System Monitor (PERFMON.EXE) tool because most I/Os are being serviced promptly. Long I/O requests can be aggravated by workloads that perform large amounts of sequential I/O, such as backup and restore, table scans, sorting, creating indexes, bulk loads, and zeroing out files.

Isolated long I/Os that do not appear related to any of the previous conditions may be caused by a hardware or driver problem. The system event log may contain a related event that helps to diagnose the problem.