User Defined Functions in SQL Server

What is User Defined Functions (UDF) in SQL Server?

What are the types of User Defined Function in SQL Server?

How to create User Defined Function in SQL Server?

How to ALTER and DROP user defined functions in SQL Server?

What are the advantages of User Defined Functions in SQL Server?



As you know we have two types of blocks in TSQL programming, Anonymous Blocks and Sub-Program Blocks.

Sub Program blocks are of two types in SQL Server.

1. Procedures

2. Functions.


In this article I will be focusing on Functions.

There are two types of Functions in SQL Server

System Function: These are the inbuilt functions present in SQL Server. User can’t make any change in these functions i.e Min(), Max(), UPPER()

User Defined Functions: SQL Server allows user to create their own functions. These functions are known as User Defined Functions.



What is User Defined Functions (UDF) in SQL Server?

A user defined functions are also a Stored Block of code similar to Stored Procedure. It always returns at least a single value or a table. Function doesn't support output parameters but it can have maximum 1024 input parameters. We can’t perform DDL, DML operations in functions. There are many limitations in functions i.e we can’t use Try-catch blocks in functions. 

What are the types of User Defined Function in SQL Server?


Functions are of 3 types in SQL Server. 

Scalar Functions
Inline Table Valued Functions
Multi-Statement Table Valued Functions.


How to create User Defined Function in SQL Server?

Let’s understand each type of User Defined Functions with one simple example.


Scalar Functions

A Scalar user-defined function returns one of the scalar data types referenced in the RETURNS clause in CREATE FUNCTION statement. Text, ntext, image, Cursor and timestamp data types are not supported in Scalar User Defined. Scalar Function can have 0 to 1024 input parameters.

Below is the syntax to create Scalar Type User Defined Functions


Syntax

CREATE FUNCTION Function-Name

(@Paramter-Name Data-Type = Default, @Paramter-Name Data-Type = Default …. n)

RETURNS Data-Type

WITH Function-Options

AS

BEGIN

Function-Body

RETURN Scalar-Expression

END


Function-Name– This is the function name, you have to remember this name.

@Paramter-Name Data-Type = Default – This is the input parameter name with its data type for the function.

Function-Options – Functions options can be any of these two

Encryption – Indicates that the Database Engine encrypts the catalog view columns that contains the text of the create function statement.

Schemabinding – Indicates that Functions is bound to the database object that it references. Object can’t be dropped until you drop the function or alter the Function without Schemabinding option.


Function-Body – This is the place where we write our logic.


Example

I am creating one EMP table and populating some data in this table. We will be using this table to understand Scalar type user defined functions.

You can use below query to populate dummy data.


USE TEACHMESQLSERVER

GO

CREATE TABLE EMP (EMPID INT, EMPNAME VARCHAR(255), DEPNAME VARCHAR(255), SALARY MONEY, BONUS MONEY)

INSERT INTO EMP VALUES(1,'GHANESH','IT',2000,100)

INSERT INTO EMP VALUES(2,'PRASAD','HR',3000,1100)

INSERT INTO EMP VALUES(3,'GAUTAM','SALES',2500,400)

INSERT INTO EMP VALUES(4,'ANVIE','MARKETING',20000,NULL)

GO

SELECT * FROM EMP



Problem – Create a function which returns Employee’s salary + Bonus from EMP table based on EMPID.


SQL Code –
CREATE FUNCTION MYSCALARFUNCTION (@EMPID INT)

RETURNS MONEY

AS

BEGIN

DECLARE @TOTALSALARY MONEY

SELECT @TOTALSALARY= SALARY+ ISNULL(BONUS,0) FROM EMP WHERE EMPID=@EMPID

RETURN @TOTALSALARY

END

Congratulations you have successfully created your first user defined function which will return (SALARY + BONUS) from EMP Table based on their EMPID. You can find your recently created functions under Programmability Folder à Functions Folder à Scalar-Valued Functions. Below Images shows the path.



Now our task is to call recently created a scalar function.


Syntax for calling a Scalar Function:
SELECT <owner>.<function-name> (Parameters values)


Calling the MYSCALARFUNCTION function:

SELECT DBO.MYSCALARFUNCTION(2) as TOTALSALARY





As you can see from above result our MYSCALARFUNCTION function is returning only one scalar value which as referenced in the CREATE Function command.


Inline Table Valued Functions

Inline Table Valued Functions return a Table variable as an output. In Inline table valued functions, the Table returns value is defined through a single select statement so there is no need of BEGIN/END blocks in the CREATE FUNCTION statement. There is also no need to specify the table variable name or column definitions for the table variable because the structure of the table variable will be generated from the columns that compose the SELECT statement. In Inline Table Valued Functions there should be no duplicate columns referenced in the SELECT statement, all derived columns must have an associated alias.


Syntax

CREATE FUNCTION Function-Name

(@Parameter-Name Data-Type = Default, @Parameter-Name Data-Type = Default …. n)

RETURNS TABLE

WITH Function-Options

AS

RETURN (SELECT STATEMENT)


Problem – Create a function which returns EMPNAME, DEPNAME and SALARY from EMP table based on SALARY range.


SQL Code –

CREATE FUNCTION MYINLINETABLEVALUEDFUNCTION (@SALARY INT)

RETURNS TABLE

AS

RETURN (SELECT EMPNAME, DEPNAME, SALARY FROM EMP WHERE SALARY>@SALARY)

Congratulations you have successfully created your first Inline Table Valued user defined function which will return EMPNAME, DEPNAME and SALARY from EMP Table based on SALARY range. You can find your recently created functions under Programmability Folder à Functions Folder àTable-Valued Functions. Below Images shows the path.





Now our task is to call recently created a scalar function.


Syntax for calling a Table Valued Function:

SELECT *|<Column List> from <function-name> (Parameters values)



Calling the MYINLINETABLEVALUEDFUNCTION function:

SELECT * from MYINLINETABLEVALUEDFUNCTION (2000)




As you can see from the above result set, our table valued function is returning a table which has three columns. Structure of the table variable was generated by the select statement.



Multi-Statement Table Valued Functions

Multistatement Table Valued functions are similar to the Inline Table Valued Function but the body of the body of this function can contain multiple statements and the structure of the table can be defined by the user.

Below is the syntax to create Multistatement Table Valued Type User Defined Functions


Syntax

CREATE FUNCTION Function-Name

(@Paramter-Name Data-Type = Default, @Paramter-Name Data-Type = Default …. n)

RETURNS @Return_Variable TABLE <Table-Type-Definition>

WITH Function-Options

AS

BEGIN

Function-Body

RETURN

END


Problem – Create a function which returns SALARY AND ANNUAL SALARY from EMP table for EMPID.


SQL Code –

CREATE FUNCTION MYMSTABLEVALUEDFUNCTION (@EMPID INT )

RETURNS @MYTABLE TABLE(SALARY MONEY, ANNUALSALARY MONEY)

AS

BEGIN

DECLARE @SALARY MONEY,@ANNUALSALARY MONEY

SET @SALARY= (SELECT SALARY FROM EMP WHERE EMPID=@EMPID)

SET @ANNUALSALARY= @SALARY*12

INSERT INTO @MYTABLE VALUES(@SALARY,@ANNUALSALARY)

RETURN

END


Congratulations you have successfully created your first Multistatement Table Valued user defined function which will return SALARY and ANNUAL SALARY from EMP Table for EMPID. You can find your recently created functions under Programmability Folder à Functions Folder à Table-Valued Functions. Below Images shows the path.

Now our task is to call recently created a scalar function.


Syntax for calling a Table Valued Function:

SELECT *|<Column List> from <function-name> (Parameters values)



Calling the MYMSTABLEVALUEDFUNCTION function:

SELECT * FROM MYMSTABLEVALUEDFUNCTION(1)





As you can see from the above result set our recently created function is returning a table variable with two columns.


How to ALTER and DROP user defined functions in SQL Server?


ALTER Function

Once you have created your functions in SQL Server, you might want to make some changes into it.

You can ALTER User Defined Functions using ALTER Statement. At place of CREATE FUNCTION you have to use ALTER FUNCTION rest everything will remain similar to CREATE FUNCTION syntax.


Drop Function

Once you have created your functions in SQL Server, you might want to remove it from the database. You can drop User Defined Functions using Drop Statement.


Syntax

DROP FUNCTION FUNCTION-NAME


Example

DROP FUNCTION MYSCALARFUNCTION



What are the benefits of User-Defined Functions?

The advantages to SQL Server User-Defined functions are many. First, we can use these functions in so many different places when compared to stored procedure. The ability for a function to act like a table (for Inline table and Multi-statement table functions) gives developers the ability to break out complex logic into shorter and shorter code blocks. This will generally give the additional benefit of making the code less complex and easier to write and maintain. In the case of a Scalar UDF, the ability to use this function anywhere you can use a scalar of the same data type is also a very powerful thing. Combining these advantages with the ability to pass parameters into these database objects makes the SQL Server User Defined Function a very powerful tool.


Function

Definition: a bunch of SQL Statements which can be stored under 1 name.


Type of Function


· Scalar Function


o Returns a single value


CREATE FUNCTION Fun1 (@a int)

RETURNS int

AS

BEGIN

DECLARE @B int

SET @B = @A

RETURN(@b)

END


· Table valued Function


o Returns a table (only 1)


CREATE FUNCTION Fun2 (@a int)

RETURNS @temp table (B int)

AS

BEGIN

INSERT INTO @temp (B) VALUES (@A)

RETURN

END


· Aggregate Function

· System Function

o Mathematical functions, Date functions, etc…


Difference between SP and Function

A scalar valued function can be used in SELECT and WHERE clause and a table-valued function can be used in FROM clause. SP cannot be used anywhere
Function won’t except OUTPUT pram, SP does
You cannot call an SP, cannot create #TEMP table, cannot use DML and DDL statements inside function. SP will allow.
Function can be used as User Defined DataType, SP cannot
You cannot return text, image, timestamp from a function
Default Param


Function in WHERE clause

When we are using the function in the SELECT statement it returns data depends on the number of records retrieve by the select statement and what type of value is passed in the scalar function. The performance of the MS SQL Query is little bit degraded using the scalar function. But developer often used it for encapsulate frequently performed logic.

But if we saw some SELECT statement, we can see that the functions can be used in WHERE conditions also. Using scalar or User defines function in WHERE clause is a good idea? Is this hampering the performance?

This article is related to it.


Is it Bad?


Using function on WHERE clause causes Index scan. The reason for this is that the function value has to be evaluated for each row of data to determine it matches our criteria.


How we understand it


To understand it properly, here I am taking an example. We compare the Actual Execution plan of the Query and understand how the function in the WHERE clause effects the query by Index Scanning.


Step-1 [ Creating the Base Table ]


-- Base Table

IF OBJECT_ID('tbl_EMPDTLS') IS NOT NULL

BEGIN

DROP TABLE tbl_EMPDTLS;

END

GO


CREATE TABLE tbl_EMPDTLS

(EMPID INT NOT NULL IDENTITY PRIMARY KEY,

EMPFNAME VARCHAR(50) NOT NULL,

EMPLNAME VARCHAR(50) NOT NULL,

EMPGRADE VARCHAR(1) NOT NULL,

EMPEMAIL VARCHAR(50) NOT NULL,

DOJ DATETIME NOT NULL);

GO


Step-2 [ Creating the Index Information ]


-- Creating Non clustered Index

IF EXISTS(SELECT *

FROM sys.indexes

WHERE object_id=OBJECT_ID('tbl_EMPDTLS')

AND name ='IX_NONC_EMPFNAME')

BEGIN

DROP INDEX tbl_EMPDTLS.IX_NONC_EMPFNAME;

END

GO

CREATE NONCLUSTERED INDEX IX_NONC_EMPFNAME

ON tbl_EMPDTLS (EMPFNAME)


GO

IF EXISTS(SELECT *

FROM sys.indexes

WHERE object_id=OBJECT_ID('tbl_EMPDTLS')

AND name ='IX_NONC_EMPGRADE')

BEGIN

DROP INDEX tbl_EMPDTLS.IX_NONC_EMPGRADE;

END 

GO

CREATE NONCLUSTERED INDEX IX_NONC_EMPGRADE

ON tbl_EMPDTLS (EMPLNAME);

GO

IF EXISTS(SELECT *

FROM sys.indexes

WHERE object_id=OBJECT_ID('tbl_EMPDTLS')

AND name ='IX_NONC_EMPEMAIL')

BEGIN

DROP INDEX tbl_EMPDTLS.IX_NONC_EMPEMAIL;

END

GO


CREATE NONCLUSTERED INDEX IX_NONC_EMPEMAIL

ON tbl_EMPDTLS (EMPEMAIL);

GO


IF EXISTS(SELECT *

FROM sys.indexes

WHERE object_id=OBJECT_ID('tbl_EMPDTLS')

AND name ='IX_NONC_DOJ')

BEGIN

DROP INDEX tbl_EMPDTLS.IX_NONC_DOJ;

END

GO



CREATE NONCLUSTERED INDEX IX_NONC_DOJ

ON tbl_EMPDTLS (DOJ);

GO


Step-3 [ Inserting some records in the Table ]


-- Inserting Records


INSERT INTO tbl_EMPDTLS

(EMPFNAME, EMPLNAME, EMPGRADE, EMPEMAIL, DOJ)

VALUES ('JOYDEEP', 'DAS', 'B', 'joydeep@abc.com','03-12-2006'),

('RAJECH', 'DAS', 'C', 'rajesh@abc.com', '01-12-2006'),

('SUKAMAL', 'JANA', 'B', 'suku@abc.com', '03-12-2004'),

('TUHIN', 'SHINAH', 'B', 'tuhin@abc.com', '07-12-2001'),

('SANGRAM', 'JIT', 'B', 'sangram@abc.com','01-10-2011'),

('SUDIP', 'DAS', 'A', 'sudip@abc.com', '07-11-1990'),

('RANI', 'LAL', 'B', 'rani@abc.com', '03-12-2006'),

('JOHN', 'IBRAHAM','C', 'john@abc.com', '01-05-2007'),

('BHUPEN', 'SINGH', 'A', 'bhapu@abc.com', '03-12-2006'),

('SAIKAT', 'SREE', 'B', 'saikat@abc.com', '01-12-1906'),

('SUJATA', 'LALA', 'B', 'sujata@abc.com', '03-12-2012'),

('RAJU', 'ROSTOGU','C', 'raju@abc.com', '03-12-2006'),

('ROHIT', 'KUMAR', 'C', 'rohit@abc.com', '01-10-2012'),

('VIPIN', 'PAUL', 'B', 'vipin@abc.com', '01-11-2006'),

('VINODH', 'CHOPRA', 'C', 'vinodh@abc.com', '03-12-2006'),

('KALLU', 'SHEK', 'B', 'joydeep@abc.com','01-11-2011')

GO

Step-4 [ Exciting the Query ]


-- Example Set-1


SELECT *

FROM tbl_EMPDTLS WITH(INDEX(IX_NONC_EMPFNAME))

WHERE EMPFNAME LIKE 'J%';

GO


SELECT *

FROM tbl_EMPDTLS WITH(INDEX(IX_NONC_EMPFNAME))

WHERE LEFT(EMPFNAME,1) = 'J';







-- Example Set-2

SELECT *

FROM tbl_EMPDTLS WITH(INDEX(IX_NONC_EMPFNAME))

WHERE EMPFNAME='JOYDEEP'

AND EMPLNAME='DAS'

GO




SELECT *

FROM tbl_EMPDTLS WITH(INDEX(IX_NONC_EMPFNAME))

WHERE EMPFNAME+EMPLNAME='JOYDEEPDAS'







-- Example Set-3

SELECT *

FROM tbl_EMPDTLS WITH(INDEX(IX_NONC_DOJ))

WHERE DOJ = '03-12-2004'

GO


SELECT *

FROM tbl_EMPDTLS WITH(INDEX(IX_NONC_DOJ))

WHERE DOJ < GETDATE()

GO


SELECT *

FROM tbl_EMPDTLS WITH(INDEX(IX_NONC_DOJ))

WHERE DATEDIFF(day, DOJ, '03-12-2004') = 0

GO




Conclusion

So from execution plan we find that using function in WHERE clause is a bad idea.

Error Handling in SQL Server

How to do Error Handling in SQL Server?

How to Raise Error Manually in SQL Server?

How to ADD USER Defined Error Messages in sys.messages catalog view?



How to do Error Handling in SQL Server?

When you have a batch of SQL statements and in that batch of SQL Statements if any SQL statement causes error then it throws the error message but it never terminates the execution of program it continues the execution of SQL statements which comes after that SQL Statement which raised error.

Let’s understand the above problem with an example

I am creating a store procedure which divides two numbers and print the result. As you can see from the below query, I am passing two number in the procedure. I am dividing them and storing the result in variable named as @RESULT. Initially I am assigning 0 values for the @RESULT variable and later updating the variable value with the result.


CREATE DATABASE TEACHMESQLSERVER
USE TEACHMESQLSERVER

GO

CREATE PROCEDURE DIVISION_BY_ZERO(@FIRST INT, @SECOND INT)
AS

BEGIN

DECLARE @RESULT INT
SET @RESULT=0
SET @RESULT=@FIRST/@SECOND

PRINT 'THE OUTPUT IS:'+CAST(@RESULT AS VARCHAR)

END


Let’s execute the above procedure

EXEC DIVISION_BY_ZERO 20,10



As you can see from the result below; when we divide 20 by 10 we get 2 and our procedure prints the result as below.

Now let’s try to divide 20 by 0; its universal truth that we can’t divide any number by 0 and we will get error.


EXEC DIVISION_BY_ZERO 20,0


Here you go; we got below error message after executing the above query.





Error message clearly says that divided by zero error encountered. If you have noticed after the error message that Print Statement executed and it printed THE OUTPUT IS: 0. this is wrong right?

It should not have printed this.


To overcome this problem SQL server provides Error handling, when we do error handling the SQL statement which cause the error will be terminated and the execution of stored procedure or batch will also be stopped.

In T-SQL programming error handling is done by using TRY and CATCH construct. Errors in T-SQL code can be processed using a TRY-CATCH construct. A TRY -CATCH construct consists of two parts TRY block and CATCH block. TRY Block contains the SQL statement which may cause error, when an error situation is detected in TYRY block, control is passed to a CATCH block where it can be processed or handled. After the CATCH block handles the exception (Error), control is then transferred to the first T-SQL statement that follows the END CATCH statement. A TRY block always starts with the BEGIN TRY statement and ends with END TRY statement. A CATCH block always starts with a BEGIN CATCH statement and ends with END CATCH statement. A CATCH block must follow a TRY block. In T-SQL each TRY block is associated with only one CATCH block.

Let’s do error handling in above procedure.

ALTER PROCEDURE DIVISION_BY_ZERO(@FIRST INT, @SECCOND INT)
AS

BEGIN

DECLARE @RESULT INT
SET @RESULT=0

BEGIN TRY

SET @RESULT=@FIRST/@SECCOND

END TRY


BEGIN CATCH

SELECT ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;

END CATCH

PRINT 'THE OUTPUT IS:'+CAST(@RESULT AS VARCHAR)

END


As you can see from the above code, we have kept SET @RESULT=@FIRST/@SECOND in TRY Block. It is because this line of SQL statement is creating dividing by zero error. We have CATCH block next to this TRY block. CATCH block is handling the error raised by its TRY block.

Let’s try to divide 20 by 0.

GO

EXEC DIVISION_BY_ZERO 20,0

As you can from the above output, this time we didn't get any error message but we get a message saying Divide by Zero encountered and it also terminates the execution and didn't print THE OUTPUT IS:0 like last time.


How to Raise Error Manually in SQL Server?

We can also raise errors manually at some required situations. It is used to return messages back to application using the same format as a system error or warning message is generated. For raising an error manually we use RAISERROR statement. RAISERROR can return either a user defined error message that has been created using the SP_ADDMESSAGE system stored procedure or a message string specified in the RAISERRORstatement.


Syntax-

RAISERROR (msg_id|msg_str|@local_variable, severity, State ,argument[,..n])

WITH option [,..n]

*msg_id is user defined error message number stored in the sys.messages catalog view. Using sp_addmessage system stored procedure user defined error message can be created in sys.messages table. It should be greater than 50000.When it is not specified RAISERROR raises an error message with an error number 50000.

*msg_str is a user defined message with formatting similar to the printf function in C.

*@local_variable is a variable of any valid charter data type that contains a string formatted in the same manner as msg_str. It must be char or varchar.

*Severity is the user defined severity level associated with this message. It can be 0-18.

* State is an arbitrary integer from 1-127.The value 0 and greater than 127 will raise an error.


Let’s understand this with an example

Problem - you don’t want to divide any number by 2. If anyone tries to divide with 2 it should throw error.


SQL Code-

CREATE PROCEDURE DIVISION_BY_TWO(@FIRST INT, @SECOND INT)
AS

BEGIN

Declare @RESULT int

BEGIN TRY

If @SECOND=2

RAISERROR('I AM RAISING THIS ERROR MANUALLY, SORRY YOU ARE NOT ALLOWED TO DIVIDE BY 2',15,1)

SET @RESULT=@FIRST/@SECOND

PRINT 'The Output is: '+ Cast(@RESULT as varchar(2))

END TRY



BEGIN CATCH

PRINT Error_Message()

END CATCH

END


As you can from the above query in try block, if anyone tries to divide by 2 it raise error, catch block just after the try block catch gets the control and it prints the error message written in RAISERROR under TRY block.


Let’s try to divide 20 by 2.


EXEC DIVISION_BY_TWO 20, 2





As you can see from the above result, it raised an error which was raised manually. It printed the error message written in RAISEERROR under TRY block.

If you want to customize the error message with formatting then use the RAISERROR statement as following

RAISERROR ('We don’t want to divide %d with %d',15,1,@FIRST,@SECOND)

We can also use the WITH LOG option at the end of the string to write the error message into the SQL server Log file as following.

RAISERROR (‘We don’t want to divide %d with %d’,15,1,@FIRST,@SECOND) WITH LOG

How to ADD USER Defined Error Messages in sys.messages catalog view?

All the predefined error list of SQL server can be found in the SYS.Messages catalog view. Query on the database with the following statement where we can view the list of predefined errors.


SELECT * FROM SYS.MESSAGES

We can add new user defined error message in SYS.Messages using the system store procedure SP_AddMessage.


Syntax:

SP_AddMessage @msgid, @severity, @msgtext, @lang, @with_log, @replace


Example:

Exec SP_AddMessage 50001,16,'I AM RAISING THIS ERROR MANUALLY, SORRY YOU ARE NOT ALLOWED TO DIVIDE BY 2'


You can confirm that above error message has been added into sys.messages table this by running below query

SELECT * FROM SYS.MESSAGES WHERE TEXT LIKE 'I AM RAISING%'



Error Handling

Error Handling (Exception Handling) is one the of key strategy one should think of.

SQL Server 2005 has introduced sophisticated Error Handling approach with TRY-CATCH block which was not available in prior versions.

Here is the approach which can be used as Best practices.


TRY-CATCH BLOCK

In SP, we should put all our logic in TRY block and CATCH block can be utilized for throwing an error
Inside TRY block, we can open a transaction and same can be COMMITed at the end
CATCH block should be used to ROLLBACK a transaction
New in-built functions have been created in SQL Server 2005 like ERROR_NUMBER(), ERROR_LINE(), ERROR_PROCEDURE(), ERROR_MESSAGE() which are specially for CATCH block.
Every TRY block should be ending CATCH block. 1 TRY can have only 1 corresponding CATCH block
Nesting is allowed means one TRY-CATCH block can be part of outer TRY block.


DESIGN

One Table needs to be created to capture and store all the errors.
One master SP needs to be created which captures the log in above table. This SP will be kept under CATCH block of all the SPs
while using RAISERROR , use WITH LOG. This will log in errorlog also.



DESIGN CODE


CREATE TABLE [ErrorLog](
[ErrorLogID] [int] IDENTITY(1,1) NOT NULL,
[ErrorDescription] [nvarchar](max) NOT NULL,
[ErrorSourceName] [varchar](255) NOT NULL,
[SysMessagesErrorNumber] [int] NOT NULL,
[ErrorSourceLineNumber] [smallint] NOT NULL,
[IsDeleted] [bit] NOT NULL DEFAULT 0,
[CreatedBy] [int] NOT NULL,
[CreatedDateTime] [datetime] NOT NULL DEFAULT GETUTCDATE(),

CONSTRAINT [PK_ErrorLog] PRIMARY KEY CLUSTERED
(
[ErrorLogID] ASC
)
)


CREATE PROCEDURE [uspThrow_Error]
(
@userProfileId INT=NULL
)
AS

BEGIN

-- Return if there is no error information to retrieve.

IF ERROR_NUMBER() IS NULL

RETURN;



DECLARE 

@errorMessage NVARCHAR(MAX), 

@errorNumber INT,

@errorSeverity INT,

@errorState INT,

@errorLine INT,

@errorProcedure NVARCHAR(200)


-- Assign variables to error-handling functions that

-- capture information for RAISERROR.


SELECT

@errorNumber = ERROR_NUMBER(),

@errorSeverity = ERROR_SEVERITY(),

@errorState = ERROR_STATE(),

@errorLine = ERROR_LINE(),

@errorMessage = ERROR_MESSAGE();



-- Capturing procedure name along with Schema

SET @errorProcedure =ISNULL(Library.ufnGet_FullObjectName(ERROR_PROCEDURE(),1),'')



-- This is some technical error. So log into ErrorLog table

INSERT INTO Library.ErrorLog
( 

SysMessagesErrorNumber, ErrorSourceName,ErrorSourceLineNumber, ErrorDescription, CreatedBy, CreatedDateTime 

)

VALUES

(

@errorNumber,@errorProcedure, @errorLine,@errorMessage, ISNULL(@userProfileId,1), GETUTCDATE()

)


-- Now Raise an error. This time it will take Error number as 50000. So Front-end will show a generic Error page.

RAISERROR

(

@errorMessage,

@errorSeverity,


@errorProcedure,

@errorNumber, -- parameter: original error number.

@errorSeverity, -- parameter: original error severity.

@errorState, -- parameter: original error state.

@errorProcedure, -- parameter: original error procedure name.

@errorLine -- parameter: original error line number.


) WITH LOG;

END

GO



SAMPLE CODE


CREATE PROC SampleProc


AS


BEGIN


BEGIN TRY


BEGIN TRAN


-- Your code

SELECT 1/0

COMMIT

END TRY

BEGIN CATCH

EXEC [uspThrow_Error]

END CATCH

END



RAISERROR()


Introduction

Microsoft Error handling is an important factor for every developer. Here in this article, I am not going to explain the entire process of error handling. But here in this article I am trying to explain most important portion of Error handling is called RaisError() function.


Syntax of RaisError()

RAISERROR ( { Message ID | MessageText}

{ ,severity ,state }

[ ,argument [ ,...n ] ] )

[ WITH option [ ,...n ] ]


Setting Custom Error Messages

The user defines error message number stores on sys.messages catalog view. First have a look on the catalog view.


SELECT * FROM sys.sysmessages;





Understanding the Syntax


Here the Error Number is very important. The user defines error numbers begins at greeter then 50000.


We can add the custom error message by using system stored procedure namedsp_AddMessage.



sp_addmessage [ @msgnum= ] msg_id, [ @severity= ] severity ,


[ @msgtext = ' ] 'message'


[, [ @lang = ] 'Language' ]


[, [ @with_log = ] 'log' ]


[, [ @replace = ] 'replace' ]


@msgnum


It indicate the number of Message for user define message it should be grater then 50000


@severity


It means the priority of the Error Messages. It’s range from 0-25. But for user define message we use 0-19. Severity level 20-25 is considered as fatal error.


A TRY…CATCH construct catches all execution errors with severity greater than 10 that do not terminate the database connection.


Errors with severity from 0 through 10 are informational messages and do not cause execution to jump from the CATCH block of a TRY…CATCH construct.


Errors that terminate the database connection, usually with severity from 20 through 25, are not handled by the CATCH block because execution is aborted when the connection terminates.


@msggtext


The error message can have a maximum of 2,047 characters. If the message has more than 2,047 characters, then will show only 2,044 characters with an ellipsis to indicate that the message has been truncated


We can pass the optional custom parameters within the message text. This parameter is useful to display any custom messages within the message text. The details of the parameters are mentioned bellow.


Type of Separation

Representation

d or i

Signed integer

O

Unsigned Octal


S

String


U

Unsigned Integer


@lang

It means the language that we want to specify.


@with_log

Set TRUE to log the event in event viewer.


@replace

If the same message number already exists, but we want to replace the string for that ID, we have to use this parameter.


Example with sp_AddMessage


Here we are going to add a custom error message by using sp_AddMessage stored procedure.


EXEC sp_addmessage


@msgnum = 50009,


@severity = 1,


@msgtext = 'Example Custom Error Message';


GO

SELECT * FROM sys.sysmessages where error=50009;

Here we provide an example to understand it properly.


EXEC sp_addmessage

@msgnum = 50010,

@severity = 1,

@msgtext = 'Invalid Sales Order Number : %s for Quantity :%i';

GO


SELECT * FROM sys.sysmessages where error=50010;


error

severity

dlevel

description

msglangid

50010

1

0

Invalid Sales Order Number : %s for Quantity :%i

1033



How to Call RaisError() Function


BEGIN

DECLARE @str_Sorder VARCHAR(max),

@int_Qty INT;


SET @str_Sorder = 'ORDER-1';

SET @int_Qty = 30;

RAISERROR(50010, 1, 1, @str_Sorder, @int_Qty);

END


Invalid Sales Order Number : ORDER-1 for Quantity :30


Msg 50010, Level 1, State 1



Error handling
Error handling is a very important part of T-SQL development life cycle. Before SQL Server 2005 the error handling is quite difficult to maintain.

From SQL Server 2005 Microsoft provide some easier process to handle our T-SQL error properly. In this article we are discussing about the Error handling process of SQL Server.

The error information that SQL Server passes to the client consists of several components, and the client is responsible for the final interpretation of the message. These are the components that SQL Server passes to the client.


Message number

Each error message has a number. We can find most of the message numbers in the table sysmessages in the master database. But there some special numbers like 0 and 50000 that do not appear there.

Severity level

A number range from 0 to 25. This is that if the severity level is in the range 0-10, the message is informational or a warning, and not an error. Errors resulting from programming errors in our SQL code have a severity level in the range 11-16. Severity levels 17-25 indicate resource problems, hardware problems or internal problems in SQL Server, and if the severity is 20 or higher, the connection is terminated. For system messages we can find the severity level in master ..sysmessages.


State

A number range from 0 to 127. The meaning of this item is specific to the error message, but Microsoft has not documented these values.


Procedure

In which stored procedure, trigger or user-defined function the error occurred. Blank if the error occurred in a plain batch of SQL statements (including dynamic SQL).

Line

The line number within the procedure/function/trigger/batch the error occurred. A line number of 0 indicates that the problem occurred when the procedure was invoked.


Message text

This is the actual text of the message that tells us what went wrong. We can find this text in master..sysmessages, or rather a template for it, with placeholders for names of databases, tables etc.


Error handling process in Microsoft SQL Server 2000

As our main concern is to demonstrate the error handling process at MS SQL Server 2005 and above, we are not going to depth of MS SQL Server 2000 error handling process.


@@error


Microsoft SQL Server sets the global variable @@error to 0, unless an error occurs, in which case @@error is set to the number of that error. @@error will hold the number of that message.

Now we are trying to generate error and display the error message by @@error global variable.


CREATE TABLE notnull(a int NOT NULL)
DECLARE @err int,


@value int

INSERT notnull VALUES (@value)

SELECT @err = @@error


IF @err <> 0

PRINT '@err is ' + ltrim(str(@err)) + '.'


Msg 515, Level 16, State 2, Line 5


Cannot insert the value NULL into column 'a', table 'MAHAGUN.dbo.notnull'; column does not allow nulls. INSERT fails.


The statement has been terminated.

@err is 515.


@@rowcount


This is a global variable reports the number of affected rows in the most recently executed statement. Just like @@error we need to save it in a local variable if we want to use the value later, since @@rowcount is set after each statement. Since with SET we can only assign variable at a time, we must use the SELECT if you need to save both @@error and @@rowcount into local variables:

SELECT @err = @@error, @rowc = @@rowcount


@@trancount


This is a global variable which reflects the level of nested transactions. Each BEGIN TRANSACTION increases @@trancount by 1, and each COMMIT TRANSACTION decreases @@trancount by 1. Nothing is actually committed until @@trancount reaches 0. ROLLBACK TRANSACTION rolls back everything to the outermost BEGIN TRANSACTION (unless we have used the fairly exotic SAVE TRANSACTION), and forces @@trancount to 0, regards of the previous value.


Error handling process in Microsoft SQL Server 2005


The Microsoft SQL Server 2005 provides TRY… CATCH mechanism to handle with error. It is so effective and easy to use for T-SQL developer.


The syntax of the blogs is mentioned bellow.


BEGIN

BEGIN TRY

BEGIN TRANSACTION


-- TSQL Starements


COMMIT TRANSACTION

END TRY



BEGIN CATCH

-- Error Handling Code

ROLLBACK TRANSACTION

END CATCH

END


Here in this block of BEGIN TRY and END TRY if any kind of error occurs it directly jump to the BEGIN CATCH and END CATCH block and handle the error.

The values that can be retrieved from the error are also much more detailed, then what you could get with previous versions of SQL Server. Below is a list of the data that can be retrieved when an error occurs.
ERROR_NUMBER() - returns the number of the error.
ERROR_SEVERITY() - returns the severity.
ERROR_STATE() - returns the error state number.
ERROR_PROCEDURE() - returns the name of the stored procedure or trigger where the error occurred.
ERROR_LINE() - returns the line number inside the routine that caused the error.
ERROR_MESSAGE() - returns the complete text of the error message.

A sample code of calling stored procedure using error handling technique is mentioned bellow.



BEGIN TRY

BEGIN TRY

EXECUTE up_MyProc

END TRY



BEGIN CATCH

SELECT

ERROR_NUMBER() AS ErrorNumber,

ERROR_SEVERITY() AS ErrorSeverity;

END CATCH;



EXECUTE up_MyProc

END TRY

BEGIN CATCH

SELECT

ERROR_NUMBER() AS ErrorNumber,

ERROR_SEVERITY() AS ErrorSeverity;

END CATCH;


RAISERROR() statement

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.


RAISERROR can be used to:

1. Assign a specific error number, severity and state.

2. Record the error in Windows Application Log.

3. Return messages that contain variable text.

Example:


DECLARE @DBID INT;

SET @DBID = DB_ID();


DECLARE @DBNAME NVARCHAR(128);

SET @DBNAME = DB_NAME();



RAISERROR


(N'The current database ID is:%d, the database name is: %s.',


10, -- Severity.


1, -- State.


@DBID, -- First substitution argument.


@DBNAME); -- Second substitution argument.

GO


Error handling process in Microsoft SQL Server 2012


In Microsoft SQL server 2012 code named denali in traduced HROW statement.

The statement before the THROW statement must be followed by the semicolon (;) statement terminator.

If a TRY…CATCH construct is not available, the session is ended. The line number and procedure where the exception is raised are set. The severity is set to 16.


If the THROW statement is specified without parameters, it must appear inside a CATCH block. This causes the caught exception to be raised. Any error that occurs in a THROW statementcauses the statement batch to be ended.


RAISERROR statement

THROW statement

If a msg_id is passed to RAISERROR, the ID must be defined in sys.messages.

The error_number parameter does not have to be defined in sys.messages.

The msg_str parameter can contain printf formatting styles. 

The message parameter does not accept printf style formatting.

The severity parameter specifies the severity of the exception.

There is no severity parameter. The exception severity is always set to 16.


Example:


-- IN SQL Server 2005


BEGIN TRY

DECLARE @VarToTest INT

SET @VarToTest = 'C'

END TRY

BEGIN CATCH

DECLARE @ErrorMessage NVARCHAR(4000),

@ERRORSEVERITY INT

SET @ERRORMESSAGE = ERROR_MESSAGE()


SET @ERRORSEVERITY = ERROR_SEVERITY()

RAISERROR (@ErrorMessage, @ErrorSeverity, 1 )

END CATCH



-- IN SQL Server 2012

BEGIN TRY

DECLARE @VarToTest INT

SET @VarToTest = 'C'

END TRY

BEGIN CATCH

THROW

END CATCH

Tips To Improve SQL Server Query Performance

Here are some tips to improve your SQL query performance.

1. Choose Correct Data types

2. Always avoid nchar & nvarchar if you are not going to store Unicode data

3. Always Avoid Select * statement, it’s better to mention the column name in select statement.

4. Use Exists instead of IN, NOT Exists instead of <> and Not Like keyword

5. Create Clustered and Non-Clustered Index

6. Try to keep Clustered Index Small

7. Avoid using Cursors, try to implement using while loop

8. Use Table Variable and Temporary table cleverly

9. Use Union All in place of Union

10. Define All Primary keys and Foreign Key Relationships

11. Define All Unique and Check Constraints

12. Partition Tables Vertically and Horizontally

13. Avoid Expensive Operators such as Having, Not Like, Distinct, Order By

14. Avoid Explicit or Implicit functions In Where Clause

15. Full Qualify database Objects using Database.Schema.Objects

16. Use Locking and Isolation Level Hints to minimize locking

17. Use Stored Procedure or Parameterized Queries

18. Avoid Long Actions in Triggers

19. Avoid Long Running Transactions, Transactions that require user input to commit

20. Use Set NOCOUNT ON in Stored Procedure

21. Don’t use SP_(Your SP Name) for user defined stored Procedure name

22. Try to convert Sub Query to Join

23. Use TRY-Catch blocks to handle errors

24. Always use column list in your insert statements

25. Don’t use column numbers in ORDER BY clause

26. Try to avoid trigger on transaction table which gets many Insert, update, Delete operations.


Performance Improvement

T-SQL SIDE

1. Include SET NOCOUNT ON inside SP 
2. If table’s rowcount is required, then use sysindexes query instead of COUNT(*) 
3. Try to avoid CURSOR wherever required 
4. Try to use UNION ALL instead of UNION wherever required 
5. Try to avoid DISTINCT wherever required 
6. Try to avoid unnecessary complicated joins 
7. Try to return only required columns instead of using SELECT * 
8. Try to avoid IN, instead EXISTS query can be used 
9. Avoid triggers 
10. Try to avoid function in WHERE query or SELECT query 
11. Try to avoid CONVERT or CAST function 
12. If less number of records are involved, then TEMP variable can be used instead of TEMP table, otherwise go for TEMP table 
13. Avoid large number of DML operations in single transaction 
14. Avoid update on a table which is affecting lot of records. Divide this query in chunks and update the records in chunks which will be faster 
15. Consider using the NOLOCK hint to prevent locking if the data being locked is not modified often 
16. Try to avoid ISNULL wherever required 
17. Avoid sorting if not required. Can it be achieved using Index? 
18. Try to use in-built function instead of using system tables. 

e.g. if exists (select * from sysobjects where name = 'tblTest1908')

drop table tblTest1908

if object_id('tblTest1908') is not null
drop table tblTest1908



DESIGN & MAINTENANCE SIDE

1. Have a database design as per normalization rule 
2. Create Clustered Index and non-clustered based on the usage 
3. Try to rebuild the indexes monthly/weekly based on the data insertion/updation 
4. In worst case, denormalization 
5. If appropriate, reduce lock escalation by using the ROWLOCK or PAGLOCK. 
6. If you experience a large number page splits, then increase the Fillfactor which will reduce page splits as it will have more room to accommodate new data. 

EXECUTION PLAN

Execution Plan will give you an idea which execution plan SQL Server internally uses.


Table Scan
Ø This means, no clustered index is being used to retrieve the data. It is using a heap structure and does a full table scan.

Ø If a table is having less number of records, then it is fine otherwise index needs to be created.

Ø Sometimes, though a non-clustered index is available, SQL server uses table scan. This will happen in following 2 scenarios.

§ If total number of retrieved rows are higher relative to total number of records available in a table

§ If total number of rows having almost a same value are coming from a table

It will be efficient when output number of rows are greater than 50 % of total number of rows



Index Seek
Ø This means, non-clustered index is being used to retrieve the data. It happens generally when fewer rows are being returned. It focuses on qualifying rows and qualifying pages which contain these qualifying rows. It will be efficient when output number of rows are less than 50 % of total number of rows
Clustered Index Seek

Ø This means, clustered index is being used to retrieve the data. This is the fastest way to get the data
Clustered Index Scan

Ø This is just like a table scan, only difference is – it is happening on clustered index table instead of heap structure. It happens due to same reason mentioned in table scan
Bookmark Lookup

Ø It is a mechanism to navigate from non clustered index row to actual data row in base table (clustered index)


Hash Join/Nested Loop/Merge Join

Hash

Nested Loop

Merge


When being used?


No adequate index on join columns

When only one table has index and other does not have

When both the tables have clustered index (sorted rows)


Phase
Build Phase
Probe Phase



Further Info

Smaller table will be build input and larger will be probe input.

Each row of smaller table will be linked with larger one and being stored in Hash Table

For each row of outer table, all the rows of inner table will be executed

Very effective for larger tables. Scans n1 + n2 rows unlike n1 X n2 as in Nested loop because the records are physically sorted. So rowID = 6 will scan only till RowID = 6, the moment it gets 7, it skips.

Types 

In Memory


Stores in Memory
Grace


When there is no sufficient memory then it will be stored in partitions.

USING DTA (DATABASE TUNING ADVISOR)


Create one *.sql file with various DML operations or SPs on which you want to perform tuning

Open DTA from "Tools" Menu

Connect to a server

Select Database & chose tables which are being used in DML Operations or SPs you have specified in saved file

Select a file which you have saved in Step # 1 

In "Tuning option", select different options and "Start Analysis"




It will give result like this.

Display Line Numbers in SQL Server Query Editor Window

If you are a database users then you may have observed while executing any query when it gives error and error message shows that on particular line number there is some issue. So we need to go to particular line number and correct the same. But what if your code is hundreds or thousands of lines, in that case scrolling down and finding that error line becomes quite difficult and time consuming.

SQL Server Management Studio provides an option to display line numbers in query editor window. In SSMS you can go to Tools –> and select Options…

It will pop up Options window as given below. Now navigate to Text Editor on left side options and select Transect-SQL –> General and in Display part choose Line numbers and click OK button. Now when when you will open a new query editor window, you will observe line numbers in it.

But if you don’t want to set line numbers in SSMS for every query editor window so there is one more option to directly go to particular line number by pressing Ctrl+G. When you will press it, you will get a pop up Go To Line to enter line number.

Here you can enter line number and press OK and your cursor will directly go to particular line number and you can correct the error.

SQL Server Management Studio (SSMS): Display Line Numbers

Losing your patience while writing long lines of code in SQL is understandable. SSMS includes a feature to show line numbers in your query editor to assist with this process. Instructions for SQL Server versions 2005, 2008, and 2012 are included.

By default line numbers are disabled in SQL server. However, through these steps they can be displayed with all versions of SQL Server.

Enabling line numbers option in SQL Server Management Studio (SSMS) is very useful in debugging. When errors are created, SSMS will show the line number to indicate the location of the issue. The following is an example:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'FROM'.

Turning on line numbers makes it easy to jump to an line within the code by specifying the specific line number.

SQL Server 2008 and 2012

1.Open SSMS.

2.Under the Tools menu, select Options.

3.When Options window appears, expand the Text-Editor section.

4.When you see above window, span the Transact-SQL option and check the box Line Numbers under Display.

5.Now when you return to your query editor, the line numbers will be enabled.

SQL Server 2005

1. Under Tools Menu, select Options.
2. When the Option window appears, expand the Text-Editor option and select All Languages.
3. Select Line Numbers.
4. Return to your query editor, line numbers would be enabled.

We will be glad to read your comments. Let us know if this really helped you to increase your productivity.

If you are a fan of displaying line numbers, you will probably want to review how to do this is in Visual Studio text editor and and Vi/Vim as well.

How to get total row count of a table in a faster way

1. Background

The purpose of this article is to describe an efficient way of getting the row count of the table which is very fast as compare to COUNT (*) OR COUNT (Col1). Whenever we execute SELECT * FROM [TABLE] query, SQL Server performs table scan to get the total number of records in the table. If the table is bulky then it will take a long time to get the total record count. It will affect the performance of SQL stored procedure or function if we are using COUNT (*) function in them for bulky tables.

To resolve this issue, we are going to use sys.partitions system view which contains one row for each partition of all the tables and indexes except Full-Text, Spatial, and XML type indexes.


2. What is sys.partitions system view?

i. Sys.partitions is a system view that provides information about partitions of tables/indexes whether table/indexes are explicitly partitioned or not.

ii. Each row contains data for a partition including default partition.

iii. Index_id: This column denotes type of index :

· 0 = heap
· 1 = clustered index
· 2 or greater = no clustered index

iv. Partition_number: This column contains partition number.


   · 1 = Default Partition
· > 1 = New partitions in sequential order


v. ROWS: This column contains no. of rows in the partition.

3. Steps to get row count of table in faster way

i. Get the total record count of the table using COUNT (*) FUNCTION:

COUNT(*) is taking approximately 1 minute and 32 seconds to retrieve total records count. Time taken by the COUNT (*) may be more and more if we have more records in the table. For a bulky table, COUNT (*) may affect your system performance too.

ii. Now get the total record count of the table using sys.partitions system view:

SELECT [ROWS] AS TotalCount
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='Business'
AND index_id < 2

GO

iii. If your table is partitioned table then use the below T-SQL script to get total record count in faster way.

SELECT SUM([ROWS]) AS TotalCount
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='Business'
AND index_id < 2

GO

Differences - SQL Server

Difference Between Truncate and Delete

This is one of the most important questions generally asked in the interview.



Truncate releases allocated memory but Delete operation doesn't release allocated memory.
Truncate is faster then Delete because it perform the operation on Table but delete performs operation on each row. 

1. First we understand what type of command they are. DELETE is the DML command and TRUNCATE is the DDL Command.

2. No WHERE condition is used with TRUNCATE, so it effects on entire Table Objects.

3. The TRUNCATE command NOT fire any TRIGGER related to Table, as it is not delete data row wise.

4. Drop all object's statistics and marks like High Water Mark free extents and leave the object really empty with the first extent. zero pages are left in the table

5. If the table contains an identity column, the counter for that column is reset to the seed value that is defined for the column

6. Restrictions on using Truncate Statement


a. Are referenced by a FOREIGN KEY constraint

b. Participate in an indexed view.

c. Are published by using transactional replication or merge replication.


Now What the Advantage of TRUNCATE rather than DELETE


1. It is Very FAST

2. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data.



Delete

Truncate 

Command 

DELETE FROM <<TableName>>

TRUNCATE TABLE <<TableName>>


Speed 

Slower

Faster


WHERE 

Can be applied

Cannot be applied


IDENTITY 

Cannot reset

Will reset


TRIGGER 

Will fire

Won’t fire


Transaction Log 
Maintains record level log

Maintains page level log


Referential Integrity 

If Child table doesn’t have corresponding record from master table then it will allow

Will never ever allow if any referential integrity exists; no matter child table has record or not


Table Variable 
Can be deleted

Cannot be truncated


CDC 

Will allow

Won’t allow if CDC is enabled on table


Rollback 

Can be rolled back

Can be rolled back (yes, this is true, Truncate can be rolled back) 

Difference Between Temp Table and Variable Table

This is one of the most important questions generally asked in the interview. Please give your valuable suggestions and feedback to improve this article.



There is one more difference, Table Variable will not work in if you use in Dynamic SQL.



Temporary table and Table variable

In this article I am trying to collects some facts related to temporary table and table variable. So let's start about it.

The concepts of the temporary tables, which help developer a great, relax to maintain T-SQL statements. This table is created on run time and can do the all kind of operations that normal table can do. But based on the type of table the scope is limited.

The temp tables are created in the tempdb database. So before starting about temp table, let's take a quick look about tempdb database.


Tempdb database

It is a system database global resource that is available to all users. Some facts are mentioned bellow.


1. Temporary use objects that are explicitly created such as, global and local

temporary tables, temporary stored procedure, table variables or cursors.

2. Internal objects created by SQL Server database engine. For example work

tables to store intermediate result for spools or sorting.

3. Row versions that re generated by data modifications transactions in a database

that use read-committed using row versioning isolation or snapshot isolation transaction.

4. Row version that are generated by data modification transactions for feature.


Type of temporary table

Based on behavior and scope the temporary table is divided into two categories.


1. Local Temp Table


2. Global Temp Table



Local temp table

Local temp tables are only available to the current connection for the user and automatically deleted when the user disconnects from instances. It is started with # sign.

Example:

CREATE TABLE #tbl_localTemp
(sRoll Int,
sName Varchar(50),
sClass Int)

GO


Global Temp table
Once the table has been created by a connection, like a permanent table it then available to any user by any connection. It is only be deleted when all connections have been closed. Global temp table name starts with ##.

Example:

CREATE TABLE ##tbl_localTemp
(sRoll Int,
sName Varchar(50),
sClass Int)

GO


Some points we must remember related to temporary table

1. As they are created in a separate database named tempdb, so additional overhead and can causes performance issue.

2. Number of rows and columns need to be minimised as we needed.


Table variable

Alternate of the temporary table is table variable. Which can do all kinds of operation we can do with the temporary table. It is always useful for less data. If result set returns large amount of data we use the temporary table.

Example:

DECLARE @tblvrbl_local TABLE
(sRoll Int,
sName Varchar(50),
sClass Int)

GO


1. Table variable are transaction neutral. They are variables and are not bound to a transaction

2. Temp table behave same as normal table and are bound by transaction.

Difference between Stored Procedure and Function





Differences between char and varchar

1. Data type char is example of fixed length memory allocation. For example:


DECLARE @Value AS CHAR(10) = 'Exact'
SELECT DATALENGTH(@Value)


Output: 10


So, Sql sever has allocated 10 byte memory spaces while total number of characters in the constant 'Exact' is only 5.

Data type varchar is example of variable length memory allocation. For example:


DECLARE @Value AS VARCHAR(10) = 'Exact'
SELECT DATALENGTH(@Value)

Output: 5

So in case of varchar sqlsever use dynamic allocation.


2. If total numbers of characters is less than size of char data type then sql sever embeds that number of space characters at the end but not in varchar data type. For example:


What will be output of following sql query ?


DECLARE @First AS CHAR(10) = 'Exact'
DECLARE @Last AS CHAR(10) = ' Help'

IF (@First + @Last = 'Exact Help')

SELECT 'Equal'

ELSE

SELECT 'Not Equal'


Output: Not Equal


Explanation: In memory variable @First has stored like 'Exact ' and variable @Second ' Help '


So, @First + @Last = 'Exact Help '


It is obvious that is not equal to 'Exact Help'


3. When ANSI_PADDING is off then a column of any table of type CHAR NULL is same as VARCHAR NULL. For example:


Example 1:


SET ANSI_PADDING OFF


CREATE TABLE CharTest_1(
Value CHAR(10) NULL
)

INSERT INTO CharTest_1 VALUES ('Exact'),('Help')

SELECT DATALENGTH(Value) FROM CharTest_1


Output:

5

4

Explanation: Here sql server is treating CHAR(10) NULL as VARCHAR(10) NULL.


Example 2:
SET ANSI_PADDING ON

CREATE TABLE CharTest_1(
Value CHAR(10) NULL
)

INSERT INTO CharTest_1 VALUES ('Exact'),('Help')

SELECT DATALENGTH(Value) FROM CharTest_1


Output:

10

10


Example 2:

SET ANSI_PADDING OFF

CREATE TABLE CharTest_2(
Value CHAR(10) NOT NULL
)


INSERT INTO CharTest_2 VALUES ('Exact'),('Help')

SELECT DATALENGTH(Value) FROM CharTest_2

Output:

10


10


4. VARCHAR keep additional two bytes at the end to keep the length of the string while CHAR is fixed length so it doesn't keep the length information.

5. When we perform update query on CHAR column is doesn't move that row since data always fits in the allocated space while when we update VARCHAR column then there is need to move the row.


Advantage of using CHAR data type in sql server:

1. Query performance is better since no need to move the column while updating.

2. No need to store the length of string in last two bytes.


Disadvantage of using CHAR data type in sql server:

1. It takes too much more spaces than varchar since it is fixed length and we don't know the length of string which to be store.

2. It is not good for compression operation since it embeds space characters at the end.


Advantage of using VARCHAR data type in sql server:

1. Since it is variable length so it takes less memory spaces.

Disadvantage of using VARCHAR data type in sql server:


1. Decrease the performance of some sql queries.


So, we should use CHAR data type of a column only if we known the length of the string is fixed otherwise we should always use varchar.


How to perform case sensitive comparison in sql server

Sql server string comparison is case sensitive or not

Insert default value when data or parameter is null in sql server

SQL SERVER PPT


Reference: http://www.exacthelp.com/2012/01/difference-between-char-and-varchar-in.html


What are differences between varchar and nvarchar or char and nchar in sql server?


1. Data type nvarchar always use 2 bytes to store a single character while varchar use one byte or two byte depending upon collation. For example:

DECLARE @Str1 VARCHAR(5) = 'A'
DECLARE @Str2 NVARCHAR(5) = N'A'

SELECT
DATALENGTH(@Str1) [Varchar],
DATALENGTH(@Str2) [NVarchar]


Output:

Varchar

NVarchar


2. Data type nvarchar stores the characters in Unicode which uses Unicode universal code page (UCS-2) while varchar uses many different types of code pages

3. In data type varchar(n)

n can be 1<= n <= 8000

While In data type nvarchar(n)

n can be 1<= n <= 4000

4. Data type nvarchar support all type of collation while varchar supports only those collations which use one byte to store a single character. For example:


It is right to declare:

DECLARE @Str2 NVARCHAR(5) = N'A' COLLATE Bengali_100_CI_AI


But not this:

DECLARE @Str1 VARCHAR(5) = 'A' COLLATE Bengali_100_CI_AI

Due to Collation 'Bengali_100_CI_AI' is supported on Unicode data types only and cannot be applied to char, varchar or text data types.

When we should use varchar and nvarchar data type for a column of a table in sql server

We should use varchar when there is no need to store the data of different languages that is collation which requires only one byte to store a single character since:


1. Varchar saves 50% memory space than nvarchar

2. Query execution with varchar is faster than nvachar since due to less size less number of pages will have to search.

We should use nvarchar only if there is need to store the data of different languages that is collation which requires two one bytes to store a single character.


Sql server temporary stored procedures

Foreign key constraint sql server | Referential constraint

IF statement or condition in WHERE clause of SELECT statement in sql server

Sql server create unique constraint which allow multiple NULL values

Dynamic ORDER BY clause in sql server

How to compile any query without executing it in sql server

Reference: http://www.exacthelp.com/2012/01/difference-between-varchar-and-nvarchar.html



IN Vs EXISTS

Every developer knows about IN and EXISTS clause. Depends on situation to situation we use them. Here in this article we are typing to make some differences between them


What to use IN or EXISTS


We recommended to use the EXISTS instead of IN as performance wise EXISTS gives us better output.


Syntax wise Differences

Syntax of EXISTS


SELECT *
FROM tbl_Customers
WHERE Exists (SELECT *
FROM tbl_Orders
WHERE Orders.CustomerID = Customers.ID)


Syntax of IN


SELECT *
FROM tbl_Customers
WHERE ID IN (SELECT CustomerID
FROM tbl_Orders)


We can Use Join

SELECT Customers.*
FROM Customers
INNER JOIN Orders ON Customers.ID = Orders.CustomerID

Here if we think about the performance factors in mind, we recommended the order of Using Join, Using EXISTS and Last Order is Using IN Clause.


Disadvantage of Using IN clause


As we all know that IN clause decrees the performance of query, beside this is another disadvantage we find in IN clause.


Generally we are writing IN clause in this fashion

WHERE id IN (1,2,3,4)

But there is a limitation of Number of item within IN Clause and that is 10000

WHERE id IN (1,2,3,4,.....10000)

We can use maximum of 10,000 items within IN clause.

SQL Server Authentication Modes

Authentication is the process of verifying the credentials and if it is correct then allowing to access the database and its components. Only valid identity will be granted the access to the database server. Any user who provides correct login id and password are granted access to SQL Server so it is very important to manage the login details properly and secure the same. SQLServer also trusts windows authentication and once valid credentials are provided, it grants access.

SQL Server can be configured in two authentication modes.

1. Windows Authentication Mode

2. SQL Server and Windows Authentication Mode

In Windows Authentication Mode, only genuine windows authenticated users are granted access. When user tries to login, SQLServer instance verifies the windows login and grants the access.

In SQL Server and Windows Authentication Mode, both type of users i.e. SQL Server and Windows authenticated are verified and permitted to access the server.

You can choose either Windows Authentication Mode or SQL Server and Windows Authentication Mode at the time of SQL Serverinstallation. You can change this setting any time after installation also. You can right click on database instance and go toProperties then select Security page and you can choose among these two options under Server authentication as shown below.

Once you have changed server authentication mode, it will prompt you to restart the SQL Server, click OK to confirm the same.



During SQL Server installation, when you set SQL Server and Windows Authentication Mode, then ‘sa’ login is enabled and if you select Windows Authentication Mode then ‘sa’ login is disabled and you have to manually activate it if you want to use it. Windows authentication is normally performed by Kerberos protocol.