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
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
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.