SQL Puzzle | OUTPUT Puzzle – The RIGHT Puzzle
In this puzzle you have to find out the output of the two queries without executing them. And You also have to provide reasoning for the output.
QUERY-1
--
DECLARE @Vals AS CHAR(6) = '87654'
SELECT RIGHT('000000' + @Vals , 6 ) Outp
GO
--
|
QUERY-2
--
DECLARE @Vals AS VARCHAR(6) = '87654'
SELECT RIGHT('000000' + @Vals , 6 ) Outp
GO
--
|
OUTPUT for Query 1
-- Outp -------- |87654 | (1 row affected) -- |
In this case you will have a space at the end with the 5 characters on the left. So when we assign the 5 characters to a CHAR(6) [As it is fixed length data type] it is saving the data as |87654 | and the we are adding ‘000000’ to the value , so we will get |00000087654 |. So clearly we have a space at the end and we we take right 6 characters we will get |87654 |.
OUTPUT for Query 2
-- Outp -------- |087654| (1 row affected) -- |
Enjoy !!! Keep Learning
Pawan Khowal
Http://MSBISkills.com
Improving my SQL BI Skills 
query 1 o/p ‘ 87654’ since its char 6 the memory is pre allocated of 6 characters
query 2 o/p ‘087654’ var char is a variable length it is allocated on the fly while saving to memory.
LikeLiked by 1 person
Excellent answer Vijay !! The first one will give you ‘87654 ‘ a space after the value as it is char(6) – a fixed length data type.
LikeLike