PRINT Statement in Sql Server

In Sql Server PRINT statement can be used to return message to the client. It takes string expression as input and returns string as a message to the application. In case of SSMS the PRINT statement output is returned to the Messages window and in case applications PRINT statement output is returned as an informational error message to the client application.

Basically we use PRINT statement for troubleshooting the code by displaying the message or displaying variable value etc.

Let us understand PRINT statement with extensive list of examples

Example 1: PRINT statement printing/returning a string literal

PRINT 'Hello World!'

RESULT:
Sql Server PRINT Example1

From the above result we can see that in case of Sql Server Management Studio, the PRINT statement output is returned to the Messages tab.

[ALSO READ] PRINT/SELECT Statement messages within WHILE LOOP or BATCH of statement is not displayed immediately after it’s execution- Sql Server

Example 2: PRINT statement printing a Sql Server variable value

DECLARE @WelcomeMsg VARCHAR(100) = 'Hello World!'
PRINT @WelcomeMsg

RESULT:
Sql Server PRINT Example 2

Example 3: PRINT statement printing a Function output

Let us print built-in function GETDATE() return value

PRINT GETDATE()

RESULT:
Sql Server PRINT Example 3

PRINT Statement Input and Return Data Type

The input to the PRINT statement can be either of CHAR, NCHAR, VARCHAR or NVARCHAR data type. If input passed to it is other than these specified data types then it tries to implicitly convert it to one of these data types. And if input is of type VARCHAR(MAX) or NVARCHAR(MAX) then it is truncated to datatypes VARCHAR(8000) or NVARCHAR(4000). The return type of the PRINT statement is either VARCHAR or NVARCHAR depends on the type of the input.

Example 4: Implicit and Explicit Data Type conversion in PRINT statement

As explained above PRINT statement expects string input, if other data type is passed it will try to do the implicit conversion of the data type. Let us understand this with couple of examples:

Example 4.1: PRINT statement displaying integer variable value

DECLARE @I INT = 100
PRINT @I

RESULT:
Sql Server PRINT Example4

From the above result we can see that the integer variable value passed to the PRINT statement is implicitly converted.

Example 4.2: PRINT statement printing XML type variable value

DECLARE @value XML = '<Employee id="1" Name="Basavaraj"/>'
PRINT @value

RESULT:

Msg 257, Level 16, State 3, Line 2
Implicit conversion from data type xml to nvarchar is not allowed. Use the CONVERT function to run this query.

From the above result we can see that the XML type variables implicit conversion to NVARCHAR type is failed. To solve this issue we can explicitly convert the XML type to VarChar and pass to the PRINT statement.

DECLARE @value XML = '<Employee id="1" Name="Basavaraj"/>'
PRINT CAST(@value AS VARCHAR(50))

RESULT:
Sql Server PRINT Example 4.3

From the above result it is clear that the input to the PRINT statement must be either of CHAR, NCHAR, VARCHAR or NVARCHAR data type. If input passed to it is other than these specified data types then it tries to implicitly convert it to one of these data types.

Example 5: PRINT statement printing the concatenated result of the string literal and and integer variable value

DECLARE @I INT = 100
PRINT 'Current Number : ' + @I

RESULT:
Sql Server PRINT Example 5.1

Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value ‘Current Number :’ to data type int.

From the above result we can see that in this case Sql Server is trying to convert string literal value ‘Current Number : ‘ to integer type (i.e. type of the variable @I) as integer has higher precedence than the VarChar type.

To solve this issue we can explicitly convert the integer variable @I value to VARCHAR type as shown below script by using the CAST statement:

DECLARE @I INT = 100
PRINT 'Current Number : ' + CAST(@I AS VARCHAR(10))

RESULT:
Sql Server PRINT Example 5.2

In Sql Server 2012 we have CONCAT funtion which takes care of converting the input to the correct format and then concatenating and returning a string output. We can re-write the above script using CONCAT function as below:

DECLARE @I INT = 100
PRINT CONCAT('Current Number : ',@I)

RESULT:
Sql Server PRINT Example 5.3

Example 6: NULL in the PRINT statement

Example 6.1: NULL as input to the PRINT statement

PRINT NULL

RESULT:
Sql Server PRINT Example 61

From the above result we can see that PRINT statement doesn’t print NULL value

Example 6.2: PRINT statement with string expression which is a concatenation of string literal and a variable whose value is NULL

DECLARE @Name NVarChar(50)
PRINT 'Welcome ' + @Name

RESULT:
Sql Server PRINT Example 62

From the above result we can see that PRINT statement didn’t print any value because the concatenation of a string literal ‘Welcome ‘ and the variable @Name whose value is NULL (because it is not initialized) results to NULL.

How to read PRINT statement out in the .NET code

PRINT statement output is returned as an informational error message to the client application. It is not returned as a regular exception instead it is returned as information error message with severity less than or equal to 10. To read the informational messages returned by the PRINT statement or RAISERROR statement with severity less than or equal to 10, we can add event handler delegate method to the InfoMessage event of the connection object in C# ADO.NET code

SqlConnection conn = new SqlConnection(ConnectionString);
 conn.InfoMessage += new SqlInfoMessageEventHandler(ProcessInformationalMessage);

And below is the sample Delegate method ProcessInformationalMessage code which is writing the PRINT statement output to the console:

protected static void ProcessInformationalMessage(
  object sender, SqlInfoMessageEventArgs args)
{
  foreach (SqlError err in args.Errors)
  {
    Console.WriteLine('Error Number {0}, Error Line  {1}, Error Message {2}',
   err.Number, err.LineNumber, err.Message);
  }
}

In a long running stored procedure or script, if you have added multiple PRINT statement to know progress of the script execution. Then to your surprise usually you will not see these messages till the end of procedure execution. The reason is sql server buffers the PRINT statement output and sends to client once it reaches TDS packet size of 4KB. If you want to instantaneously send the PRINT statement output to the client then you can use RAISERROR statement with NO WAIT as explained in the below article:

PRINT/SELECT Statement messages within WHILE LOOP or BATCH of statement is not displayed immediately after it’s execution- Sql Server

Differences Between Sql Server TEXT and VARCHAR(MAX) Data Type

The objective of this article is to compare the legacy Large OBject (LOB) data type TEXT and the Sql Server 2005 introduced VARCHAR(MAX) LOB data type.

[ALSO READ] Difference Between Sql Server VARCHAR and VARCHAR(MAX)

TEXT VarChar(MAX)
Basic Definition

It is a Non-Unicode large Variable Length character data type, which can store maximum of 2147483647 Non-Unicode characters (i.e. maximum storage capacity is: 2GB).

It is a Non-Unicode large Variable Length character data type, which can store maximum of 2147483647 Non-Unicode characters (i.e. maximum storage capacity is: 2GB).

Version of the Sql Server in which it is introduced?

Text data type was present from the very old versions of Sql Server. If I remember correctly it was present even in Sql Server 6.5 days.

VarChar(Max) data type was introduced in Sql Server 2005.

Which one to Use?

As per MSDN link Microfost is suggesting to avoid using the Text datatype and it will be removed in a future versions of Sql Server.

Varchar(Max) is the suggested data type for storing the large string values instead of Text data type.

In-Row or Out-of-Row Storage?

Data of a Text type column is stored out-of-row in a separate LOB data pages. The row in the table data page will only have a 16 byte pointer to the LOB data page where the actual data is present.

Data of a Varchar(max) type column is stored in-row if it is less than or equal to 8000 byte. If Varchar(max) column value is crossing the 8000 bytes then the Varchar(max) column value is stored in a separate LOB data pages and row will only have a 16 byte pointer to the LOB data page where the actual data is present.

When LOB column value is less than 8000 bytes or available space in the row, then whether LOB column value is stored in-row or out-of-row?

Execute the following script to create a demo database SqlHintsLOBDemo if it doesn’t exists already. In the demo data base it creates a table TextTable with a Text LOB type column LargeString. Finally it inserts 100 records, where LargeString column value in each row is 4000 B characters (i.e. 4000 bytes).

--Create a Demo Database
IF DB_ID('SqlHintsLOBDemo')
     IS NULL
CREATE DATABASE SqlHintsLOBDemo
GO
USE SqlHintsLOBDemo
GO
--Create a table with Text type 
--column
CREATE TABLE dbo.TextTable
(
	Id INT IDENTITY(1,1),
	LargeString TEXT
)
GO
--INSERT 100 records, where  
--LargeString column value in 
--each row is 4000 B characters
-- (i.e. 4000 bytes)
INSERT INTO dbo.TextTable
(LargeString)
VALUES(REPLICATE('B', 4000))
GO 100 --Loop 100 times

[ALSO READ] GO Statement can also be used to excute batch of T-Sql statement multiple times

Execute the following statement to check whether the Text type column value is stored in-row or out-of-row:

SELECT alloc_unit_type_desc, 
 page_count
FROM
 sys.dm_db_index_physical_stats 
   (DB_ID('SqlHintsLOBDemo'),
    OBJECT_ID('dbo.TextTable'),
    NULL, NULL , 'DETAILED')

RESULT:
TextData Typ Page Allocation Column Value Less Than 8000 Bytes

From the above result we can see that even though one row (4 Byte for Integer Id column value + 4000 Bytes for Text type column value) can fit into one 8KB data page, but still as per design Sql Server always stores the Text type column value in the LOB data pages. Whether we are storing 1 byte or 2GB data in a Text type column Sql Server always stores the Text type column value out-of-row in the LOB data pages and the row will have a 16 byte pointer pointing to the LOB data pages where the data is stored.

Execute the following script to create a demo database SqlHintsLOBDemo if it doesn’t exists already. In the demo data base it creates a table VarMaxTable with a VarChar(Max) LOB type column LargeString. Finally it inserts 100 records, where LargeString column value in each row is 4000 B characters (i.e. 4000 bytes).

--Create a Demo Database
IF DB_ID('SqlHintsLOBDemo')
   IS NULL
CREATE DATABASE SqlHintsLOBDemo
GO
USE SqlHintsLOBDemo
GO
--Create a table with a 
--Varchar(Max) type column
CREATE TABLE dbo.VarMaxTable
(
	Id INT IDENTITY(1,1),
	LargeString VARCHAR(MAX)
)
GO
--INSERT 100 records, where
--LargeString column value in 
--each row is 4000 B characters 
--(i.e. 4000 bytes)
INSERT INTO dbo.VarMaxTable
 (LargeString)
VALUES(REPLICATE('B', 4000))
GO 100

Execute the following statement to check whether the VarChar(Max) type column value is stored in-row or out-of-row:

SELECT alloc_unit_type_desc, 
 page_count
FROM
 sys.dm_db_index_physical_stats 
   (DB_ID('SqlHintsLOBDemo'),
    OBJECT_ID('dbo.VarMaxTable'),
    NULL, NULL , 'DETAILED')

RESULT:
VarcharMax Type Page Allocation Column Value Less Than 8000 Bytes

From the above result we can see that LOB VarChar(MAX) type column value is stored in-row. For VarChar(MAX) type column Sql Server by default always tries to store the data in-row. Only if it is exceeding 8000 bytes or available space in the row, then only it stores out-of-row in a LOB data pages and in-row it will have 16 byte pointer to the LOB data pages where actual column value is stored.

When LOB column value is more than 8000 bytes or available space in the row, then whether LOB column value is stored in-row or out-of-row?

Execute the following script to remove the previously inserted records and insert 100 records where LargeString column value in each row is 10,000 B characters (i.e. 10,000 bytes).

--TRUNCATE the table to
--remove all the previously
--Inserted records
TRUNCATE TABLE dbo.TextTable
GO
INSERT INTO dbo.TextTable
 (LargeString)
VALUES(REPLICATE(
 CAST('B' AS VARCHAR(MAX)), 
 10000))
GO 100

Execute the following statement to check whether the Text type column value is stored in-row or out-of-row:

RESULT:
TextData Typ Page Allocation Column Value more Than 8000 Bytes

The above result further re-affirms that: whether we are storing 1 byte or 2GB data in a Text type column Sql Server always stores the Text type column value out-of-row in the LOB data pages and the row will have a 16 byte pointer pointing to the LOB data pages where the data is stored.

Execute the following script to remove the previously inserted records and insert 100 records where LargeString column value in each row is 10,000 B characters (i.e. 10,000 bytes).

--TRUNCATE the table to
--remove all the previously
--Inserted records
TRUNCATE TABLE dbo.VarMaxTable
GO
INSERT INTO dbo.VarMaxTable
 (LargeString)
VALUES(REPLICATE(
 CAST('B' AS VARCHAR(MAX)), 
 10000))
GO 100

Execute the following statement to check whether the VarChar(Max) type column value is stored in-row or out-of-row:

RESULT:
VarcharMax Type Page Allocation Column Value more Than 8000 Bytes

From the above result we can see that LOB VarChar(MAX) type column value is stored out-of-row in a LOB data pages. For VarChar(MAX) type column Sql Server by default always tries to store the data in-row. Only if it is exceeding 8000 bytes or available space in the row, then only it stores out-of-row in a LOB data pages having 16 byte pointer in-row pointing to LOB data pages where actual column value is stored.

Do we have an option to change default In-Row and Out-Of-Row Storage behavior?

As we have already seen above whether we are storing 1 byte or 2GB data in a Text type column Sql Server always stores it out-of-row in the LOB data pages and the row will have a 16 byte pointer pointing to the LOB data pages where the data is stored.

Sql Server provides a mechanism where we can change this default behavior of storing the data out-of-row even when we have a sufficient free space in the row to accommodate the Text type column value, by means of sp_tableoption system stored procedure with the option ‘text in row’.

Execute the below statement to store the Text Type Column value in Row if Text Type column value is less than 7000 bytes or enough space is available in the row.

EXEC sp_tableoption 
 @TableNamePattern =
      'dbo.TextTable',
 @OptionName = 'text in row', 
 @OptionValue = 7000

The @OptionValue parameter value can be:
0/OFF (Default Value): Text type column value is stored out-of-row
ON: Text Type Column value is stored in-row as long as the Text type column value is less than or equal to 256 bytes
integer value from 24 through 7000: specifies the number bytes up to which the text type column value is stored in-row.

Execute the following script to remove the previously inserted records and insert 100 records where LargeString column value in each row is 4,000 ‘B’ characters (i.e. 4,000 bytes).

TRUNCATE TABLE dbo.TextTable
GO
INSERT INTO dbo.TextTable
(LargeString)
VALUES(REPLICATE('B', 4000))
GO 100

Execute the following statement to check whether the Text type column value is stored in-row or out-of-row:

SELECT alloc_unit_type_desc, 
 page_count
FROM
 sys.dm_db_index_physical_stats 
   (DB_ID('SqlHintsLOBDemo'),
    OBJECT_ID('dbo.TextTable'),
    NULL, NULL , 'DETAILED')

RESULT:
Changing Text Data Type Default Storgae Behaviour

From the above result now we can see that the Text column values are stored in-row. So we can use sp_tableoption system stored procedures option ‘text in row’ to change the text data types default storage behavior of always storing out-of-row. With this option we an force text data type column value to store in-row up-to 7000 bytes or till the enough space is available in the row.

Execute the following statement to change back the Text type columns storage behavior to the default behavior where Text type columns values are always stored out-of-row even we have sufficient space in the row.

EXEC sp_tableoption
 @TableNamePattern =
      'dbo.TextTable',
 @OptionName = 'text in row',
 @OptionValue = 'OFF'

As we have already seen above for VarChar(MAX) type column Sql Server by default always tries to store the data in-row. Only if it is exceeding 8000 bytes or available space in the row, then only it stores out-of-row in a LOB data pages and in-row it will have 16 byte pointer to the LOB data pages where actual column value is stored.

Sql Server provides a mechanism where we can change this default behavior of storing the data for VarChar(Max) type column, by means of sp_tableoption system stored procedure with the option ‘large value types out of row’.

Execute the below statement to always store Varchar(Max) column value out-of-Row whether it is 1 byte or 2GB even when enough space is available in the row.

EXEC sp_tableoption 
 @TableNamePattern =
  'dbo.VarMaxTable',
 @OptionName = 
  'large value types out of row',
 @OptionValue = 1

The @OptionValue parameter value can be:
0 (Default) : Varchar(Max) column values are stored in-row as long as the value length is <= 8000 bytes and enough space is available in the row. 1 : VarChar(Max) column values are always stored out-of-row even when enough space is available in the row.

Execute the following script to remove the previously inserted records and insert 100 records where LargeString column value in each row is 4,000 ‘B’ characters (i.e. 4,000 bytes).

TRUNCATE TABLE dbo.VarMaxTable
GO
INSERT INTO dbo.VarMaxTable
 (LargeString)
VALUES(REPLICATE('B', 4000))
GO 100

Execute the following statement to check whether the VarChar(Max) type column value is stored in-row or out-of-row:

SELECT alloc_unit_type_desc, 
 page_count
FROM
 sys.dm_db_index_physical_stats 
   (DB_ID('SqlHintsLOBDemo'),
    OBJECT_ID('dbo.VarMaxTable'),
    NULL, NULL , 'DETAILED')

RESULT:
Changing VarcharMax column Default Storgae Behaviour

From the above result we can see that VarChar(Max) type column values are stored out-of-row even when there was a sufficient space available in the row. So we can use the sp_tableoption system stored procedures option ‘large value types out of row’ to change the Varchar(Max) data type columns default storage behavior.

Execute the following statement to change back the Varchar(Max) type columns storage behavior to the default behavior where Sql Server by default always tries to store the data in-row. Only if it is exceeding 8000 bytes or available space in the row, then only it stores out-of-row in a LOB data pages.

EXEC sp_tableoption 
 @TableNamePattern =
  'dbo.VarMaxTable',
 @OptionName = 
  'large value types out of row',
 @OptionValue = 0
Supported/Unsupported Functionalities
Some of the string functions, operators or the constructs which work on VarChar(Max) type column may not work on the Text type column.
Below are two such example functions, operators or constructs:
1. = Operator on Text type column

SELECT *
FROM TextTable WITH(NOLOCK)
WHERE LargeString = 'test string'

RESULT:

Msg 402, Level 16, State 1, Line 1
The data types text and varchar are incompatible in the equal to operator.

2. Group by clause on Text type column

SELECT LargeString, COUNT(1)
FROM VarMaxTable WITH(NOLOCK)
GROUP BY LargeString

RESULT:

Msg 306, Level 16, State 2, Line 3
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

From the above examples we can see that we can’t use ‘=’ operator on a Text type column and also the Group By clause on the Text type column.

Some of the string functions, operators or the constructs which doesn’t work on the Text type column, but they do work on VarChar(Max) type column.
Below are two such example functions, operators or constructs:
1. = Operator on VarChar(Max) type column

SELECT *
FROM VarMaxTable WITH(NOLOCK)
WHERE LargeString = 'test string'

RESULT:
Equal to Operator on a VarCharMax type column
2. Group by clause on VarChar(Max) type column

SELECT LargeString, COUNT(1)
FROM VarMaxTable WITH(NOLOCK)
GROUP BY LargeString

RESULT:
Group By Clause on VarCharMax Type Column
From the above examples we can see that we use ‘=’ operator and Group By clause on the VarChar(Max) type column, but not on the Text type column. If data stored in the Varchar(Max) column is a very large strings, then using these functions may lead to performance issues.

System IO Considerations

As we know that the Text type column values are always stored out-of-row in LOB data pages and in-row it will have a 16 byte pointer pointing to the root LOB data page. So if the query doesn’t include the LOB columns then the number of pages required to read to retrieve the data will be less as the column data is out-of-row. But if the query includes the LOB columns, then the number of pages required to retrieve the data will be more.

As we know that the VarChar(Max) type column values are stored out-of-row only if the length of the value to be stored in it is greater than 8000 bytes or there is not enough space in the row, otherwise it will store it in-row. So if most of the values stored in the VarChar(Max) column are large and stored out-of-row, the data retrieval behavior will almost similar to the one that of the Text type column.

But if most of the values stored in VarChar(Max) type columns are small enough to store in-row. Then retrieval of the data where LOB columns are not included requires the more number of data pages to read as the LOB column value is stored in-row in the same data page where the non-LOB column values are stored. But if the select query includes LOB column then it requires less number of pages to read for the data retrieval compared to the Text type columns.

[ALSO READ] You may like to read below other popular articles on differences

1. Varchar vs NVarchar
2. Varchar vs Varchar(MAX)
3. Char vs Varchar
4. Text vs Varchar(Max)
5. Union vs Union All
6. DateTime vs DateTime2
7. SET QUOTED_IDENTIFIER ON vs SET QUOTED_IDENTIFIER OFF
8. Stored Procedure vs User Defined Function
9. Primary Key vs Unique Key
10. RAISERROR vs THROW
11. Temporary Table vs Table Variable
12. Len() vs Datalength()
13. Sequence vs Identity
14. DATEDIFF vs DATEDIFF_BIG

How to check if an Index exists in Sql Server

Many a time we come across a scenario where we need to execute some code based on whether an Index (Clustered/Non-Clustered) exists or not. This article explains how we can check the existence of Index with extensive list of examples.

[ALSO READ] How to find all the tables with no indexes at all in Sql Server?

To demonstrate how we can check the existence of a Index, let us create a sample demo database with a Customer table having a Clustered and Non-Clustered indexes by executing the following script:

--Create Demo Database
CREATE DATABASE SqlHintsIndexExists
GO
USE SqlHintsIndexExists
GO
--Create Customer Table
CREATE TABLE dbo.Customer
(
	Id INT NOT NULL, Name NVARCHAR(100)
)
GO
--Create Clustered Index IX_Customer_Id
CREATE CLUSTERED INDEX IX_Customer_Id ON dbo.Customer(Id) 
GO
--Create Non-Clustered Index PK_Customer
CREATE NONCLUSTERED INDEX IX_Customer_Name 
    ON dbo.Customer(Name) 

Executing the above script creates a Customer table with a Clustered and a Non-Clustered indexes as shown in the below image:

Table with Indexes

Let us now understand the various approaches of checking the existence of an index:

[ALSO READ] How to get all the Tables with or without Non-Clustered Indexes in Sql Server?

Approach 1: Check the existence of Index by using catalog views

sys.indexes catalog view a record for each Clustered and Non-Clustered indexes. We can execute a query like below to check the existence of a Clustered Index IX_Customer_Id on the Customer table created with a default schema (i.e. dbo).

IF EXISTS (SELECT 1
			FROM sys.indexes I
				INNER JOIN sys.tables T
					ON I.object_id = T.object_id
				INNER JOIN sys.schemas S
					ON S.schema_id = T.schema_id
			WHERE I.Name = 'IX_Customer_Id' -- Index name
				AND T.Name = 'Customer' -- Table name
				AND S.Name = 'dbo') --Schema Name
BEGIN
	PRINT 'Index Exists!'
END

RESULT:
Check existence of a Clustered Index by using sys indexes catalog view

[ALSO READ] How to find all the filtered indexes or all the tables having filtered indexes in Sql Server?

We can execute a query like below to check the existence of a Non-Clustered Index IX_Customer_Name on the Customer table created with a default schema (i.e. dbo). This query is same as the previous query only difference is the name of the index passed to it.

IF EXISTS (SELECT 1
			FROM sys.indexes I
				INNER JOIN sys.tables T
					ON I.object_id = T.object_id
				INNER JOIN sys.schemas S
					ON S.schema_id = T.schema_id
			WHERE I.Name = 'IX_Customer_Name' -- Index name
				AND T.Name = 'Customer' -- Table name
				AND S.Name = 'dbo') --Schema Name
BEGIN
	PRINT 'Index Exists!'
END

RESULT:
Check existence of a Non-Clustered Index by using sys indexes catalog view

[ALSO READ] How to get all HEAP Tables or Tables without Clustered Index in Sql Server?

Approach 2: Check the existence of Index by using sys.indexes catalog view and OBJECT_ID function

We can execute a query like below to check the existence of a Clustered Index IX_Customer_Id on the Customer table created with a default schema (i.e. dbo).

IF EXISTS (SELECT 1
			FROM sys.indexes I				
			WHERE I.Name = 'IX_Customer_Id' -- Index name
			 AND I.object_id = OBJECT_ID('dbo.Customer'))
BEGIN
	PRINT 'Index Exists!'
END

RESULT:
Check existence of a Clustered Index by using sys indexes catalog view and object id function

[ALSO READ] How to find all the indexes that have included columns in it and the name of the table to which the index belongs to?

We can execute a query like below to check the existence of a Non-Clustered Index IX_Customer_Name on the Customer table created with a default schema (i.e. dbo). This query is same as the previous query only difference is the name of the index passed to it.

IF EXISTS (SELECT 1
	   FROM sys.indexes I				
	   WHERE I.Name = 'IX_Customer_Name' -- Index name
	    AND I.object_id = OBJECT_ID('dbo.Customer'))
BEGIN
	PRINT 'Index Exists!'
END

RESULT:
Check existence of a Non-Clustered Index by using sys indexes catalog view and object id function

[ALSO READ]
How to check if a Database exists in Sql Server
How to check if a Table exists in Sql Server
How to check if Temp table exists in Sql Server
How to check if a Stored Procedure exists in Sql Server
How to check if a Function exists in Sql Server
How to check if a VIEW exists in Sql Server
How to check if a Trigger exists in Sql Server
How to check if a record exists in table in Sql Server

How to check if a Trigger exists in Sql Server

Many a time we come across a scenario where we need to execute some code based on whether a Trigger exists or not. This article explains how we can check the existence of Trigger with extensive list of examples.

To demonstrate how we can check the existence of a Trigger, let us create a sample demo database with an Inline Table Valued function by executing the following script:

--Create Demo Database
CREATE DATABASE SqlHintsTriggers
GO
USE SqlHintsTriggers
GO
--Create Customer Table
CREATE TABLE Customer 
( CustomerId INT IDENTITY (1, 1) NOT NULL ,
  FirstName NVARCHAR(50), LastName NVARCHAR(50))
GO
--Create an After Trigger
CREATE TRIGGER AfterTriggerExample
ON Customer
FOR INSERT, UPDATE, DELETE
AS
BEGIN
     PRINT 'AFTER Trigger AfterTriggerExample executed!'
END
GO
--Create a Server Scoped DDL Trigger
CREATE TRIGGER DDLServerSopedTrigger
ON ALL SERVER
FOR DDL_DATABASE_EVENTS
AS
BEGIN
 PRINT 'Disable trigger DDLServerSopedTrigger to 
		Create , Alter or Drop database'
 ROLLBACK
END
GO

[ALSO READ] Data Manipulation Language (DML) Triggers in Sql Server

Checking the existence of a Database Scoped Triggers using sys.triggers

We can use the sys.triggers catalog view to check the existence of a Database scoped triggers. DML triggers are Database scoped triggers, where as DDL triggers can be DATABASE scoped or SERVER scoped. The DDL triggers with Server level scope gets fired in response to a DDL statement with server scope like CREATE DATABASE, CREATE LOGIN, GRANT_SERVER, ALTER DATABASE, ALTER LOGIN etc. Where as DATABASE scoped DDL triggers fire in response to DDL statement with database scope like CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, ALTER TABLE, ALTER PROCEDURE, ALTER FUNCTION etc.

Example 1: Check the existence of a Database scoped Trigger using sys.triggers

We can write a query like below to check if the DML trigger AfterTriggerExample exists in the current database.

USE SqlHintsTriggers
GO
IF EXISTS (SELECT 1 FROM sys.triggers 
           WHERE Name = 'AfterTriggerExample')
BEGIN
    PRINT 'Trigger Exists'
END

RESULT:
Check IF Trigger Exists Example 1

If you want check the existence of a database scoped trigger in a database other than the contextual database then we can re-write the above query as below where sys.triggers is specified by three part name:

USE master
GO
IF EXISTS (SELECT 1 FROM SqlHintsTriggers.sys.triggers 
           WHERE Name = 'AfterTriggerExample')
BEGIN
    PRINT 'Trigger Exists'
END

RESULT:
Check IF Trigger Exists Example 2

From the above results we an see that even though the current database is MASTER database, we can check the existence of a Trigger in another database by using three part naming convention for the sys.triggers catalog view.

[ALSO READ] Data Definition Language (DDL) Triggers in Sql Server

Example 2: Try to check the existence of a Server scoped Trigger using sys.triggers

Try to execute the following query to see whether we can use the sys.triggers catalog view to check the existence of the Server scoped DDL trigger DDLServerSopedTrigger

IF EXISTS (SELECT 1 FROM sys.triggers 
           WHERE Name = 'DDLServerSopedTrigger')
	BEGIN
		PRINT 'Trigger Exists'
	END
ELSE
	BEGIN
		PRINT 'Trigger doesn''t exists'
	END

RESULT:
Check IF Trigger Exists Example 3

From the result it is clear that sys.triggers catalog view can’t find a server scoped DDL trigger even though it is present. To find server scoped DDL triggers or LOGON Triggers we can use the sys.server_triggers catalog view.

[ALSO READ] Logon Triggers in Sql Server

Example 3: Check the existence of a Server scoped Trigger using sys.server_triggers

We can write a query like below to check the existence of a Server scoped DDL trigger DDLServerSopedTrigger using the sys.server_triggers catalog view.

IF EXISTS (SELECT 1 FROM sys.server_triggers 
           WHERE Name = 'DDLServerSopedTrigger')
BEGIN
	PRINT 'Trigger Exists'
END

RESULT:
Check IF Trigger Exists Example 4

Conclusion:

From the above examples it is clear that we can use the sys.triggers catalog view to check the existence of the Database scoped triggers (i.e. DML Triggers and Database scoped DDL Triggers). Where as we need to use the sys.server_triggers catalog view to check the existene of the Server scoped triggers like Server Scoped DDL Triggers and LOGON triggers.

[ALSO READ]
How to check if a Database exists in Sql Server
How to check if a Table exists in Sql Server
How to check if Temp table exists in Sql Server
How to check if a Stored Procedure exists in Sql Server
How to check if a Function exists in Sql Server
How to check if a VIEW exists in Sql Server
How to check if a record exists in table in Sql Server

How to check if a Function exists in Sql Server

Many a times we come across a scenario where we need to execute some code based on whether a User Defined Function exists or not. There are different ways of identifying the Function existence in Sql Server, in this article will list out the commonly used approaches. Let me know which approach you use and reason for the same.

To demonstrate these different approaches let us create a sample database with an Inline Table Valued function by executing the following script:

CREATE DATABASE SqlHintsFunctionExists
GO
USE SqlHintsFunctionExists
GO
--Create Inline Table Valued function
CREATE FUNCTION dbo.GetEmployeeDetail()
RETURNS TABLE
AS
RETURN (SELECT 1 AS Id, 'Basavaraj' AS Name)
GO

[ALSO READ] How to check if a Stored Procedure exists in Sql Server

Approach 1: Using sys.objects catalog view

We can write a query like below to check if a GetEmployeeDetail User Defined Function exists in the current database in any schema.

USE SqlHintsFunctionExists
GO
IF EXISTS (SELECT 1 FROM sys.objects 
           WHERE Name = 'GetEmployeeDetail' 
             AND Type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
BEGIN
    PRINT 'User defined function Exists'
END

RESULT:
Function Exists Usig Sys Objects Example 1

The above query checks the existence of the object whose name is GetEmployeeDetail across all the schemas in the current database which is one of the below function type:

Type Type Description
FN Scalar function
IF Inline table-valued function
TF Table-valued-function
FS Assembly (CLR) scalar-function
FT Assembly (CLR) table-valued function

In Sql Server object names are unique, so it is really not required to mention the type in the where clause of the sys.objects query. But it is always better practice to mention type, reason is sys.objects contains the rows for not just function it also has records for views, stored Procedures etc
If you want to check the existence of the User Defined Function in a specified Schema, then we can re-write the query like below:

USE SqlHintsFunctionExists
GO
IF EXISTS (SELECT 1 FROM sys.objects 
           WHERE Name = 'GetEmployeeDetail' 
	    AND schema_id = SCHEMA_ID('dbo') --Schema name 'dbo'
	    AND Type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
BEGIN
    PRINT 'User defined function Exists'
END

RESULT:
Function Exists Usig Sys Objects Example 2

If you want check the existence of a user defined function in a database other than the contextual database then we can re-write the above query as below where sys.objects is specified by three part name:

IF EXISTS (SELECT 1 FROM SqlHintsFunctionExists.sys.objects 
           WHERE Name = 'GetEmployeeDetail' 
            AND schema_id = SCHEMA_ID('dbo') --Schema name 'dbo'
            AND Type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
BEGIN
    PRINT 'User defined function Exists'
END

RESULT:
Function Exists Usig Sys Objects Example 3

[ALSO READ] How to check if a Database exists in Sql Server

Approach 2: Using OBJECT_ID() function

We can use OBJECT_ID() function like below to check if a GetEmployeeDetail function exists in the current database.

USE SqlHintsFunctionExists
GO
IF OBJECT_ID(N'dbo.GetEmployeeDetail') IS NOT NULL
BEGIN
    PRINT 'User defined function Exists'
END

RESULT:
Function Exists Usig OBJECT ID Example 1

Specifying the Database Name and Schema Name parts for the Function Name is optional. But specifying Database Name and Schema Name provides an option to check the existence of the user defined function in the specified database and within a specified schema, instead of checking in the current database across all the schemas. The below query shows that, even though the current database is MASTER database, we can check the existence of the GetEmployeeDetail function in the dbo schema in the SqlHintsFunctionExists database.

USE MASTER
GO
IF OBJECT_ID(N'SqlHintsFunctionExists.dbo.GetEmployeeDetail')
           IS NOT NULL
BEGIN
    PRINT 'User defined function Exists'
END

RESULT:
Function Exists Usig OBJECT ID Example 2

OBJECT_ID() function also accept the type of the object as a parameter. We can specify one of the type of the function listed in the approach 1. In the below example we are checking whether GetEmployeeDetail Table-Valued function (i.e type IF) existing in the SqlHintsFunctionExists database within the dbo schema.

USE MASTER
GO
IF OBJECT_ID(N'SqlHintsFunctionExists.dbo.GetEmployeeDetail'
             , N'IF') IS NOT NULL
BEGIN
    PRINT 'User defined function Exists'
END

RESULT:
Function Exists Usig OBJECT ID Example 3

Let us modify the above statement and specify the type as ‘FN’ (i.e. Scalar function):

USE MASTER
GO
IF OBJECT_ID(N'SqlHintsFunctionExists.dbo.GetEmployeeDetail'
             , N'FN') IS NOT NULL
	BEGIN
		PRINT 'User defined function Exists'
	END
ELSE
	BEGIN
		PRINT 'User defined function doesn''t Exists'
	END

RESULT:
Function Exists Usig OBJECT ID Example 4

From the result we can see that the function GetEmployeeDetail doesn’t exists in the SqlHintsFunctionExists database. This is because we have specified the type of the object as FN (i.e. scalar function) in OBJECT_ID function instead of IF (i.e. Table-Valued function).

[ALSO READ] How to check if a Table exists in Sql Server

Approach 3: Using INFORMATION_SCHEMA.ROUTINES View

We can use the INFORMATION_SCHEMA.ROUTINES view to check the existence of the function as shown below:

USE SqlHintsFunctionExists
GO
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES 
           WHERE ROUTINE_NAME = 'GetEmployeeDetail' 
            AND ROUTINE_TYPE = 'FUNCTION') 
	BEGIN
		PRINT 'User defined function Exists'
	END
GO

RESULT:
Function Exists Using Information Schema Routine Example

This view does has the ROUTINE_SCHEMA column, but as suggested in MSDN we should avoid using it for identifying the schema of the object.

[ALSO READ] How to check if Temp table exists in Sql Server?

Approach 4: Avoid Using sys.sysobjects System table

We should avoid using sys.sysobjects System Table directly, direct access to it will be deprecated in some future versions of the Sql Server. As per Microsoft BOL link, Microsoft is suggesting to use the catalog view sys.objects instead of sys.sysobjects system table directly to check the existence of the function.

USE SqlHintsFunctionExists
GO
IF EXISTS(SELECT * FROM sys.sysobjects  
     WHERE name = 'GetEmployeeDetail' 
      AND Type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
BEGIN
	PRINT 'User defined function Exists'
END

RESULT:
Function Exists Using sys sysobjets Example