All posts by Basavaraj Biradar

How to Split comma or any other character delimited string into a Table in Sql Server

Scripts, Sql Server, , , , , , , ,

Many a time we come across a scenario where we pass a comma or any other character delimited string to stored procedure and in stored procedure we want to fetch data from other tables based this delimited string. We can’t use this delimited string directly in the in clause as Sql treats it as one string. So, the solution for this is to get a table by splitting the delimited string by the delimiter character and then join this resultant table data with other table columns.

In Sql Server we have mechanism where we can take table as a input parameter from the application code. So if you like to pass a table from your code with multiple rows instead of passing a delimited string to stored procedure and splitting it in SP, then you may like to read the article Table-Valued Parameters in Sql Server. But I would prefer passing the comma delimited string to stored procedure and split it in the SP.

In Sql Server we have multiple approaches to achieve this. This article lists couple of them. Please let me know which one you use and if you use some-other approach let me know, so that we can help the Sql Server developer community

APPROACH 1: Sql Server 2016 STRING_SPLIT function

STRING_SPLIT is one of the new built-in table valued function introduced in Sql Server 2016. This table valued function splits the input string by the specified character separator and returns output as a table.

Below example shows how we can use STRING_SPLIT function to splits the comma separated string.

SELECT * 
FROM STRING_SPLIT('Basavaraj,Kalpana,Shree',',')

RESULT:
Sql STRING_SPLIT Function Example 1

To know in detail about the STRING_SPLIT function you may like to read the article: STRING_SPLIT function in Sql Server 2016

APPROACH 2: Using While Loop and Sql String Functions

We can crate a table valued function like the below which is using WHILE loop and Sql String functions like CHARINDEX and SUBSTRING to split the string. This should work in all the versions of Sql Server.

CREATE FUNCTION [dbo].StringSplit
(
	@String  VARCHAR(MAX), @Separator CHAR(1)
)
RETURNS @RESULT TABLE(Value VARCHAR(MAX))
AS
BEGIN      
 DECLARE @SeparatorPosition INT = CHARINDEX(@Separator, @String ),
		@Value VARCHAR(MAX), @StartPosition INT = 1

 IF @SeparatorPosition = 0	
  BEGIN	
   INSERT INTO @RESULT VALUES(@String)
   RETURN
  END
	
 SET @String = @String + @Separator
 WHILE @SeparatorPosition > 0
  BEGIN
   SET @Value = SUBSTRING(@String , @StartPosition, @SeparatorPosition- @StartPosition)

   IF( @Value <> ''  ) 
    INSERT INTO @RESULT VALUES(@Value)
  
   SET @StartPosition = @SeparatorPosition + 1
   SET @SeparatorPosition = CHARINDEX(@Separator, @String , @StartPosition)
  END     
	
 RETURN
END

Below example shows how we can use the above function to split the comma delimited string

SELECT * 
FROM StringSplit('Basavaraj,Kalpana,Shree',',')

RESULT:
StringSplit

APPROACH 3: Using XML

We can crate a table valued function like the below which is using Sql XML feature to split the string.

CREATE FUNCTION [dbo].StringSplitXML
(
    @String  VARCHAR(MAX), @Separator CHAR(1)
)
RETURNS @RESULT TABLE(Value VARCHAR(MAX))
AS
BEGIN     
 DECLARE @XML XML
 SET @XML = CAST(
    ('<i>' + REPLACE(@String, @Separator, '</i><i>') + '</i>')
    AS XML)

 INSERT INTO @RESULT
 SELECT t.i.value('.', 'VARCHAR(MAX)') 
 FROM @XML.nodes('i') AS t(i)
 WHERE t.i.value('.', 'VARCHAR(MAX)') <> ''

 RETURN
END

Below example shows how we can use the above function to split the comma delimited string

SELECT * 
FROM StringSplitXML('Basavaraj,Kalpana,Shree',',')

RESULT:
StringSplitXML

100 Frequently used queries in Sql Server – Part 1

Sql Server, , , , , , , , , , , ,

This article lists out the fifty frequently used queries by the Sql Server Developers. The next article (i.e part 2 of this article) lists out another 50 frequently used queries by the Sql Server Developers. Hope you will like this article and if any of your commonly used query is not listed here, please let me know. I will definitely post it and we can help together to the Sql Server Developers community

1. How to check if a Database exists in Sql Server

We can use DB_ID() function like below to check if a database exists. Here in the below script we are checking the existence of the database SqlHintsDB. We can as well use the sys.databases Catalog View to check the existence of the database.

IF DB_ID('SqlHintsDB') IS NOT NULL
BEGIN
	PRINT 'Database Exists'
END

RESULT:
Database existence check using DB_ID() function

To know the various alternative approaches to check the existence of a database read: How to check if a Database exists in Sql Server

2. How to check if a Table exists in Sql Server

We can use the sys.Tables catalog view to check the existence of the Table. Here in the below script we are checking the existence of the table Customers

IF EXISTS(SELECT 1 FROM sys.Tables 
          WHERE  Name = N'Customers' AND Type = N'U')
BEGIN
  PRINT 'Table Exists'
END

RESULT
Check Table Existance Using Sys.Tables Catalog view

To know the various approaches to check the existence of a table read: How to check if a Table exists in Sql Server

3. How to check if a Temp table exists in Sql Server

Below script shows how we can check the existence of a Temporary Table. As we know temp tables are created in TempDB database, so we need to check the existence of the temp table in TempDB database. In the below script we are checking the existence of the temp table #TempTable

IF OBJECT_ID('TempDB.dbo.#TempTable') IS NOT NULL
BEGIN
  PRINT '#TempTable Temporary Table Exists'
END
GO

RESULT:
How to Check if Temporary Table exists in Sql Server

To understand this in detail with examples, you may like to read the article : How to check if Temp table exists in Sql Server?

4. How to check if a Stored Procedure exists in Sql Server

We can use the sys.procedures catalog view to check the existence of a stored proedure. Here in the below script we are checking the existence of the stored procedure GetCustomers

USE SqlHintsDemoDB
GO
IF EXISTS(SELECT 1 FROM sys.procedures 
          WHERE Name = 'GetCustomers')
BEGIN
	PRINT 'Stored Procedure Exists'
END

RESULT:
Check Stored Procedure Existence using sys.procedures

To know the various alternative approaches to check the existence of a stored procedure read: How to check if a Stored Procedure exists in Sql Server

5. How to check if a Function exists in Sql Server

We can use the sys.objects catalog view to check the existence of a User Defined Function. Here in the below script we are checking the existence of the User Defined Function GetEmployeeDetail

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

To know the various alternative approaches to check the existence of a user defined function read: How to check if a Function exists in Sql Server

6. How to check if a VIEW exists in Sql Server

We can use the sys.views catalog view to check the existence of a View. Here in the below script we are checking the existence of the View vwGetCustomerInfo

IF EXISTS(SELECT 1 FROM sys.views 
     WHERE Name = 'vwGetCustomerInfo')
	BEGIN
		PRINT 'View Exists'
	END

RESULT:
Check-View-Existence-using-sys.views 1

To know various alternative approaches to check the existence of a View read: How to check if a VIEW exists in Sql Server

7. How to check if an Index exists in Sql Server

We can use sys.indexes catalog view to check the existence of a Clustered and Non-Clustered indexes. We can execute a query like below to check the existence of a 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

To know various alternative approaches to check the existence of a Index read: How to check if an Index exists in Sql Server

8. How to find all the tables with no indexes in Sql Server

We can write a query like below to get all the Tables in the Database that don’t have any indexes:

SELECT Name 'Tables without any Indexes'
FROM SYS.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasIndex')=0

RESULT:
List_All_Tables_Without_Any_Indexes

To understand this in detail with examples, you may like to read the article: How to find all the tables with no indexes in Sql Server

9. How to find all the indexes that have included columns

We can write a query like below to get the name of all the indexes that have included columns in it and the name of the table to which the index belongs to:

SELECT DISTINCT T.Name 'Table Name',
		I.Name 'Index Name',
		I.type_desc 'Index Type',
		C.Name 'Included Column Name'
FROM sys.indexes I 
 INNER JOIN sys.index_columns IC 
  ON  I.object_id = IC.object_id AND I.index_id = IC.index_id 
 INNER JOIN sys.columns C 
  ON IC.object_id = C.object_id and IC.column_id = C.column_id 
 INNER JOIN sys.tables T 
  ON I.object_id = T.object_id 
WHERE is_included_column = 1
ORDER BY T.Name, I.Name

RESULT:
How_To_Find_All_Indexes_With_Included_Column

To understand this in detail with examples, you may like to read the article: How to find all the indexes that have included columns

10. How to find all the filtered indexes or all the tables having filtered indexes in Sql Server

We can write a query like below to get the name of all the filtered indexes or all the tables having filtered indexes in Sql Server:

SELECT DISTINCT T.Name 'Table Name',
  I.Name 'Filtered Index Name',
  I.Filter_Definition 'Filter Definition'
FROM sys.indexes I		
      INNER JOIN sys.tables T 
        ON I.object_id = T.object_id 
WHERE I.has_filter = 1
ORDER BY T.Name, I.Name

RESULT:
How_To_Find_All_Filtered_Indexes_Or_Tables_With_Filtered_Index

To understand this in detail with examples, you may like to read the article: How to find all the filtered indexes or all the tables having filtered indexes in Sql Server

[ALSO READ] A-Z of Filtered Indexes with examples in Sql Server

11. How to get all HEAP Tables or Tables without Clustered Index in Sql Server

A Table that doesn’t have a Clustered Index is referred to as a HEAP Table. We can write a query like below to get all the HEAP Tables or tables that doesn’t have Clustered Index:

SELECT T.Name 'HEAP TABLE'
FROM sys.indexes I		
	INNER JOIN sys.tables T 
		ON I.object_id = T.object_id 
WHERE I.type = 0 AND T.type = 'U'

RESULT:
List_all_HEAP_Tables_or_Tables_without_Clustered_Index

To understand this in detail with examples, you may like to read the article: How to get all HEAP Tables or Tables without Clustered Index in Sql Server

12. How to get all the Tables with Primary Key Constraint in Sql Server

We can write a query like below to get all the Tables with Primary key constraint:

SELECT T.name 'Table with Primary Key'
FROM SYS.Tables T
WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 1
      AND type = 'U'

To understand this in detail with examples, you may like to read the article: How to get all the Tables with or without Primary Key Constraint in Sql Server?

13. How to get all the Tables without Primary Key Constraint in Sql Server

We can write a query like below to get all the Tables without Primary key constraint:

SELECT T.name 'Table without Primary Key'
FROM SYS.Tables T
WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0
      AND type = 'U'

To understand this in detail with examples, you may like to read the article: How to get all the Tables with or without Primary Key Constraint in Sql Server?

14. How to get all the Tables with Non-Clustered Indexes in Sql Server

We can write a query like below to get all the Tables with Non-Clustered indexes:

--List of all the Tables that have Non-Clustered Indexes
SELECT Name 'Tables with Non-Clustered Indexes'
FROM SYS.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasNonclustIndex') = 1
		AND Type = 'U'

To understand this in detail with examples, you may like to read the article: How to get all the Tables with or without Non-Clustered Indexes in Sql Server?

15. How to get all the Tables without any Non-Clustered Indexes in Sql Server

We can write a query like below to get all the Tables without any Non-Clustered indexes:

--List of all the Tables with NO Non-Clustered Indexes
SELECT Name 'Tables without any Non-Clustered Indexes'
FROM SYS.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasNonclustIndex') = 0
		AND Type = 'U'

To understand this in detail with examples, you may like to read the article: How to get all the Tables with or without Non-Clustered Indexes in Sql Server?

16. How to get all the Tables with an Identity column in Sql Server

We can write a query like below to get all the Tables with Identity column:

SELECT name 'Table with Identity column'
FROM SYS.Tables
WHERE OBJECTPROPERTY(object_id,'TableHasIdentity') = 1
      AND type = 'U'

To understand this in detail with examples, you may like to read the article: How to get all the Tables with or without an Identity column in Sql Server?

17. How to get all the Tables without an Identity column in Sql Server

We can write a query like below to get all the Tables without Identity column:

SELECT name 'Table without Identity column'
FROM SYS.Tables
WHERE OBJECTPROPERTY(object_id,'TableHasIdentity') = 0
      AND type = 'U'

To understand this in detail with examples, you may like to read the article: How to get all the Tables with or without an Identity column in Sql Server?

18. How to find all the Stored Procedures having a given text in it

We can write a script like below to get all the stored all the Stored Procedures having a given text in its definition. Here we are searching for the text SearchString in all the stored procedures

SELECT OBJECT_NAME(object_id), 
       OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%SearchString%'

To understand this in detail with examples, you may like to read the article: How to find all the Stored Procedures having a given text in it?

19. How to find all tables that have specified column name in Sql Server

We can use a script like below to find all the tables in the database that have column with specified name in it. Here we are searching for all the tables that have columns with a name having a text ColumnName in it.

SELECT SCHEMA_NAME(schema_id) + '.' + t.name AS 'Table Name'
FROM sys.tables t 
     INNER JOIN sys.columns c
        ON c.object_id = t.object_id
WHERE c.name like '%ColumnName%'
ORDER BY 'Table Name'

To understand this in detail with examples, you may like to read the article: How to find all tables that have specified column name in Sql Server?

20. How to find all dependencies of a table in Sql Server

We can use the Dynamic Management Function sys.dm_sql_referencing_entities to get all the entities in the current database that refer to the specified table. In this script we are trying to get the Employee tables dependencies

SELECT referencing_schema_name, referencing_entity_name, 
 referencing_id, referencing_class_desc
FROM sys.dm_sql_referencing_entities
             ('dbo.Employee', 'OBJECT')
GO

RESULT:
dm_sql_referencing_entities
Note: While specifying the table name please include schema name also, otherwise result will not display the dependencies.

To understand this in detail with examples, you may like to read the article: How to find all dependencies of a table in Sql Server?

21. How to find referenced/dependent objects (like Table, Function etc) of a Stored Procedure/Function in Sql Server

We can use the Dynamic Management Function sys.dm_sql_referenced_entities to get all the entities in the current database which are referenced by a stored procedure or function. Now we can use a script like below to find all the entities in the current database which are referenced by the stored procedure dbo.GetEmployeeDetails

SELECT referenced_schema_name, referenced_entity_name, 
 referenced_minor_name
FROM sys.dm_sql_referenced_entities
              ('dbo.GetEmployeeDetails', 'OBJECT')
GO

RESULT:
dm_sql_refererenced_entities
Note: While specifying the stored procedure name please include schema name also, otherwise referenced objects list will not be displayed.

To understand this in detail with examples, you may like to read the article: How to find referenced/dependent objects (like Table, Function etc) of a Stored Procedure/Function in Sql Server?

22. How to get first day of the previous quarter

--First day of the previous quarter
SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) - 1, 0)

RESULT:
FirstDayofPreviousQuarter

23. How to get first day of the current quarter

--First day of the current quarter
SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)

RESULT:
FirstDayofCurrentQuarter

24. How to get first day of the next quarter

--First day of the next quarter
SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) + 1, 0)

RESULT:
FirstDayofNextQuarter

25. How to get first day of the quarter for any given date

DECLARE @date DATETIME
SET @date = '07/28/2016'
SELECT DATEADD(qq, DATEDIFF(qq, 0, @date), 0)

RESULT:
FirstDayofQuarterforAnyGivenDate

26. How to get last day of the previous quarter

--Last day of the previous quarter
SELECT DATEADD (dd, -1, 
           DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0))

RESULT:
LastDayofPreviousQuarter

27. How to get last day of the current quarter

--Last day of the current quarter 
SELECT DATEADD (dd, -1, 
           DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) +1, 0))

RESULT:
LastDayofCurrentQuarter

28. How to get last day of the next quarter

--Last day of the next quarter 
SELECT DATEADD (dd, -1, 
           DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) +2, 0))

RESULT:
LastDayofNextQuarter

29. How to get last day of the quarter for any given date

DECLARE @date DATETIME = '07/28/2016'
SELECT DATEADD (dd, -1, 
           DATEADD(qq, DATEDIFF(qq, 0, @date) +1, 0))

RESULT:
LastDayofQuarterForAnyDate

30. How to get first day of the previous month

--First day of the previous month
SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0)

RESULT:
FirstDayofPreviousMonth

31. How to get first day of the current month

--First day of the current month
SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)

RESULT:
FirstDayofCurrentMonth

32. How to get first day of the next month

SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0)

RESULT:
FirstDayofNextMonth

33. How to get first day of the month for any given date

--First day of the month for any given date
DECLARE @date DATETIME = '07/28/2016'
SELECT DATEADD(mm, DATEDIFF(mm, 0, @date), 0)

RESULT:
FirstDayofMonthforAnyGivenDate

34. How to get last day of the previous month

--Last day of the previous month
SELECT DATEADD(dd, -1, 
	   DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))

RESULT:
LastDayofPreviousMonth

35. How to get last day of the current month

--Last day of the current month
SELECT DATEADD(dd, -1, 
           DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0))

RESULT:
Last

36. How to get last day of the next month

--Last day of the next month
SELECT DATEADD(dd, -1, 
           DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 2, 0))

RESULT:
LastDayofNextMonth

37. How to get last day of the month for any given date

--Last day of the month for any given date
DECLARE @date DATETIME = '07/28/2016'
SELECT DATEADD(dd, -1, 
           DATEADD(mm, DATEDIFF(mm, 0, @date) + 1, 0))

RESULT:
LastDayofMonthForAnyDate
[ALSO READ] EOMONTH FUNCTION IN SQL SERVER 2012

38. How to get all tables in a Database

We can use the sys.Tables catalog view to get the list of all the tables in a database. Here in the below script we are trying to get list of all the tables in the SqlhintsDemoDB database

USE SqlhintsDemoDB
GO
SELECT * FROM sys.tables

RESULT:
GetAllTablesofDatabase

39. How to get all stored procedures in a database

We can use sys.procedures catalog view to get the list of all the stored procedures in a database

SELECT * FROM sys.procedures

40. How to get all functions in a database

We can use sys.objects catalog view as shown in the below script to get all the functions in a database. Here joining with sys.sql_modules to display the function definition.

SELECT o.Name, m.[Definition], o.type_desc 
FROM sys.objects o
		INNER JOIN sys.sql_modules m 
			ON m.object_id=o.object_id
WHERE o.type_desc like '%function%'

RESULT:
Get All Functions In a Database

41. How to check the definition or content of a stored procedure in Sql Server

We can use the system stored procedure sp_helptext to check the definition of a Stored Proccedure in Sql Server. In the below example we are using the sp_helptext system stored procedure to check the definition of the Stored Procedure GetCityCustomers. 

sp_helptext GetCityCustomers

RESULT:
How to check Stored Procedure Definition or Content

42. How to check if a record exists in a table in Sql Server

Below example script checks the existence of the customer record with CustId = 2 in the IF statement

DECLARE @CustId INT = 2
IF EXISTS(SELECT 1 FROM dbo.Customer WITH(NOLOCK)
          WHERE CustId = @CustId)
	BEGIN
		PRINT 'Record Exists'
	END
ELSE
	BEGIN
		PRINT 'Record doesn''t Exists'
	END

RESULT:
Check if record exists in a table in Sql Server 1

To know more on using the EXISTS clause to check the existence of the record in IF statement, CASE Statement, WHERE clause etc read the article: How to check if a record exists in table in Sql Server

43. How to rename column name in Sql Server

We can use the system stored procedure SP_RENAME to rename the table column. Below is the SYNTAX of the SP_RENAME system stored procedure for renaming the column name:

SYNTAX:

SP_RENAME 'TableName.OldColumnName' , 'NewColumnName', 'COLUMN'

Example 1: Rename Customer table column CustName to FullName using SP_RENAME

SP_RENAME 'Customer.CustName' , 'FullName', 'COLUMN'

Result:
Rename column name in Sql Server 2

To know more table column rename option with extensive list of examples you may like to read the article: How to rename column name in Sql Server

44. How to get month name from date in Sql Server

We can use DATENAME() function to get Month name from Date in Sql Server, here we need specify datepart parameter of the DATENAME function as month or mm or m all will return the same result.

SELECT GETDATE() 'Today', DATENAME(month,GETDATE()) 'Month Name'
SELECT GetDate() 'Today', DATENAME(mm,GETDATE()) 'Month Name'
SELECT GetDate() 'Today', DATENAME(m,GETDATE()) 'Month Name'

RESULT:
Month name from date in sql server 1

We can also get the Month name from date by using the FORMAT function. To know more on this you may like to read the article: How to get month name from date in Sql Server

45. How to get Day or Weekday name from date in Sql Server

We can use DATENAME() function to get Day/Weekday name from Date in Sql Server, here we need specify datepart parameter of the DATENAME function as weekday or dw both will return the same result.

SELECT GETDATE() 'Today', DATENAME(weekday,GETDATE()) 'Day Name'
SELECT GetDate() 'Today', DATENAME(dw,GETDATE()) 'Day Name'

RESULT:
Day or weekday name from Date Sql Server 1

We can also get the Day or Weekday name from date by using the FORMAT function. To know more on this you may like to read the article: How to get Day or Weekday name from date in Sql Server

46. How to find whether a Table is referenced by the Foreign Key constraint defined in another Table in Sql Server

We can use script like below to identify whether a Table is referenced by another Tables foreign key constraints in Sql Server:

SELECT OBJECT_NAME (FK.referenced_object_id) 'Referenced Table', 
 OBJECT_NAME(FK.parent_object_id) 'Referring Table', 
 FK.name 'Foreign Key', 
 COL_NAME(FK.referenced_object_id, FKC.referenced_column_id) 'Referenced Column',
 COL_NAME(FK.parent_object_id,FKC.parent_column_id) 'Referring Column'
FROM sys.foreign_keys AS FK
		INNER JOIN sys.foreign_key_columns AS FKC 
			ON FKC.constraint_object_id = FK.OBJECT_ID
WHERE OBJECT_NAME (FK.referenced_object_id) = 'Enter Table Name'

To understand this in detail with examples, you may like to read the article: How to find whether a Table is referenced by the Foreign Key constraint defined in another Table

47. How to Check if a String Contains a Sub-string in it in Sql Server

We can use the CHARINDEX() function to check whether a String contains a Sub-string in it. Name of this function is little confusing as name sounds something to do with character, but it basically returns the starting position of matched Substring in the main String. If it is not found then this function returns value 0.

Below example demonstrates how we can use the CHARINDEX() function to check whether a String contains a Sub-string in it.

DECLARE @ExpressionToSearch VARCHAR(50) 
SET @ExpressionToSearch = 'Basavaraj Prabhu Biradar'
--Check whether @ExpressionToSearch contains the substring 
--'Prabhu' in it
IF  CHARINDEX('Prabhu', @ExpressionToSearch ) > 0 
	PRINT 'Yes it Contains'
ELSE
	PRINT 'It doesn''t Contain'

RESULT:
Substring_Within_String_Using_CHARINDEX

To know various alternative approaches to check whether a String contains a Sub-string in it, you may like to read the article: How to Check if a String Contains a Substring in it in Sql Server

48. How to get all the records which contain double byte data in a particular NVARCHAR data type column Sql Server

In NVARCHAR DataType column we can store both Single byte and Double byte data. Many a times we want to know, how many records have double byte data in the NVARCHAR data type column. We can write a script like below for this:

--Query to get all the customers whose CustomerName 
--column contains DOUBLE Byte Data
SELECT *
FROM dbo.Customer 
WHERE CustomerName != CAST(CustomerName AS VARCHAR(50))

RESULT:
Double_Byte_Records_In_Sql_Server

To understand this in detail with examples, you may like to go through the article: How to get all the records which contain double byte data or all the records which contain single byte data in Sql Server?

49. How to get all the records which contain only single byte data in Sql Server

In NVARCHAR DataType column we can store both Single byte and Double byte data. Many a times we want to know, how many records have only single byte data in the NVARCHAR data type column. We can write a script like below for this:

-- Query to get all the customers whose CustomerName 
-- column contains SINGLE Byte Data only
SELECT *
FROM dbo.Customer 
WHERE CustomerName = CAST(CustomerName AS VARCHAR(50))

RESULT:
Single_Byte_Records_Sql_Server

To understand this in detail with examples, you may like to go through the article: How to get all the records which contain double byte data or all the records which contain single byte data in Sql Server?

50. How to get Date Part only from DateTime in Sql Server

There are multiple ways of getting date part only from DateTime, below is one such approach. To know various alternative approaches you may like to read the article: How to get Date Part only from DateTime in Sql Server

SELECT CONVERT (DATE, GETDATE()) 'Date Part Only'

RESULT:
How to get Date Part only from DateTime

To know the various alternative approaches to get Date Part only from DateTime in Sql Server you may like to read the article: How to get Date Part only from DateTime in Sql Server

Hope you liked this article and if any of your commonly used query is not listed here, please let me know. I will definitely post it and we can help together to the Sql Server Developers community.

PRINT Statement in Sql Server

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