Tag Archives: TIps/Tricks

Simple Quiz On Sql Server Select statement

If we execute a statement like below, it will throw error:

SELECT xyz

Result:
Msg 207, Level 16, State 1, Line 1
Invalid column name 'xyz'.

Can you guess what the below statement execution will result and reason for the same? Be frank don’t execute the statement and answer. By the way Today, I came to know about this behaviour only after executing this statement ūüôā

SELECT 111xyz

Difference between Sql Server Char and Varchar Data Type

Everyone knows about the basic difference between CHAR and VARCHAR data types. In this article apart from the basic difference, will discuss on one more interesting difference which I have observed recently.

CHAR Data Type is a Fixed Length Data Type. For example if you declare a variable/column of CHAR (10) data type, then it will always take 10 bytes irrespective of whether you are storing 1 character or 10 character in this variable or column. And in this example as we have declared this variable/column as CHAR(10), so we can store max 10 characters in this column.

On the other hand VARCHAR is a variable length Data Type. For example if you declare a variable/column of VARCHAR (10) data type, it will take the no. of bytes equal to the number of characters stored in this column. So, in this variable/column if you are storing only one character then it will take only one byte and if we are storing 10 characters then it will take 10 bytes. And in this example as we have declared this variable/column as VARCHAR (10), so we can store max 10 characters in this column.

Below example illustrates the basic difference explained above:

DECLARE @CharName Char(20) = 'Basavaraj',
  @VarCharName VarChar(20) = 'Basavaraj'
 SELECT DATALENGTH(@CharName) CharSpaceUsed,
  DATALENGTH(@VarCharName) VarCharSpaceUsed

Result:

CharSpaceUsed VarCharSpaceUsed
------------- ----------------
20            9
(1 row(s) affected)

Below is an interesting difference, which I have observed recently while writing some script.

Concatenation of CHAR variables:

DECLARE @FirstName Char(20) = 'Basavaraj',
  @LastName Char(20) = 'Biradar'

IF @FirstName + ' ' + @LastName = 'Basavaraj Biradar' 
 PRINT 'I was Expecting'
ELSE 
 PRINT 'Surprise to me ...'

SELECT  @FirstName + ' ' + @LastName AS Name,
 len(@FirstName + ' ' + @LastName) AS Length

Result:

Surprise to me …
Name                                      Length
—————————————– ———–
Basavaraj            Biradar              28
(1 row(s) affected)

Concatenation of VARCHAR variables:

DECLARE @FirstName VarChar(20) = 'Basavaraj',
 @LastName VarChar(20) = 'Biradar'

IF @FirstName + ' ' + @LastName = 'Basavaraj Biradar' 
 PRINT 'I was Expecting'
ELSE 
 PRINT 'Surprise to me ...'

SELECT  @FirstName + ' ' + @LastName AS Name,
 len(@FirstName + ' ' + @LastName) AS Length

Result:

I was Expecting
Name                                      Length
----------------------------------------- -----------
Basavaraj Biradar                         17
(1 row(s) affected)

So, it is clear from the above examples that during concatenation of CHAR data type variables, it includes space in-place of unused space in the result of concatenation.

[ALSO READ] You may like to read below other popular articles on differences
Varchar Vs NVarchar
Varchar Vs Varchar(MAX)
Union Vs Union All
DateTime Vs DateTime2
SET QUOTED_IDENTIFIER ON Vs OFF
Stored Procedure Vs User Defined Function
Primary Key Vs Unique Key
RAISERROR Vs THROW
Temporary Table Vs Table Variable
LEN() Vs DATALENGTH()
Sequence Vs Identity

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

Till recently, I was under the impression that GO statement’s sole purpose is to convey to the Sql Server the end of a batch of T-Sql¬†Statements.¬†But recently while searching for some Sql feature details, landed on to MSDN¬†page for the GO statement. And to my surprise observed that GO statement also has an integer optional parameter, this parameter value¬†signals Sql Server to¬†execute the batch of T-Sql¬†Statement prior to¬†the GO statement¬†to be¬†executed for the specified number of times.

Let us understand this with following two simple examples:

Example 1:

PRINT 'Hello'
GO 5

Result of Executing the above statement

Beginning execution loop
Hello
Hello
Hello
Hello
Hello
Batch execution completed 5 times. 

Example 2: In this example we are creating a Test table and inserting 5 records in it.

 

This statement is very handy for testing purposes and dev tasks, such as for testing purpose if we need to insert millions together records we can achieve this with simple two statement as explained in the above examples.

Please correct me, if my understanding is wrong. Comments are always welcome, hope this article helped you.

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

Recently, I was needed¬†to search for all the Stored Procedures¬†having a given text in its definition. So, as usual did the g ūüôā ūüôā gling,¬† most of the top results returned were suggesting to use INFORMATION_SCHEMA.ROUTINES view¬†like below, which is not going to return correct result in all the scenario’s:

Below Script gives Wrong Result:

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%SearchString%'
AND ROUTINE_TYPE='PROCEDURE'

As usual tried to understand the query and¬†thought of checking¬†it’s correctness. And to my surprise¬†when I checked this view’s definition using: sp_helptext ‘INFORMATION_SCHEMA.ROUTINES’,¬†¬†the ROUTINE_DEFINITION column was returning only¬†the first 4000 characters of the Stored Procedure definition ¬†i.e. in view ROUTINE_DEFINITION¬†column is defined as: convert(nvarchar(4000),object_definition(o.object_id))AS ROUTINE_DEFINITION.¬†So with this it is clear that it will not return all the Stored Procedure’s which have the first occurrence of the search sting in its definition after 4000 characters.

 To get correct results we can use sys.procedures view as below, as the return type of the function OBJECT_DEFINITION(object_id) which returns Stored Procedure definition is of type nvarchar(max):

Below script gives Correct Result:

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

Below screen shot illustrates the difference between these two queries:

There are multiple alternative way’s with which we can correctly¬†find all the¬†Stored Procedure’s having a given text. And sys.procedures explained in this article is one such solution.

Please correct me, if my understanding is wrong. Comments are always welcome.