Today’s puzzle is going to be an interesting puzzle for many. The matter of the fact when I discovered this puzzle related to the stored procedure and unexpected results, I was very much surprised as well.
Let us see the puzzle right away.
Unexpected Results
First, let us following code where I have declared varchar and right after that I am storing some value in it and selecting it afterwards.
DECLARE @phone_no varchar(8) SET @phone_no=910034568 SELECT @phone_no as phone_no
When you run the above code, it gives us the following strange results.
Here is the Puzzle: Unexpected Results
When we run above query, why is it resulting result as *, instead of the value stored in a variable?
Please leave your answer in the comments section and I will publish the answers next week at the same time. I believe this puzzle is simple enough but not as easy as you think. I suggest you may ask this question to your work colleagues and see if they know the answer to this puzzle.
Here are few additional puzzles, if you want to try to tease your brain:
- SQL SERVER – Puzzle – How Does Table Qualifier Work in INSERT Statement?
- SQL SERVER – Simple Puzzle with UNION – Part 2
- SQL SERVER – Puzzle – Strange Behavior With Extra Comma in Create Table Statement
- SQL Puzzle – A Quick Fun with BitWise Operator
Update: The correct answer to this puzzle is given by Shweta in the comments section.
Please feel free to reach out to me on twitter.
Reference: Pinal Dave (https://blog.sqlauthority.com)
50 Comments. Leave new
Was it because you tired to set the variable to more characters than the amount that was set (8)?
Here length of the @phone_no is varchar(8) and we try to set the value which is of 9 characters (@phone_no=910034568). So if any integer value which is much large to fit into the varchar then sql server return asterisk (*) character (which is internally an overflow exception).
This output occurs because the NUMBER set for the @phone_no variable exceeds the maximum length defined for that variable.
But, if the same data (in that case a number) is single-quoted, the output data is truncated because the SQL Server will deal with this data as a character string.
For example: SET @phone_no=’910034568′ in that case the output for the same select is “91003456” because the SQL Server truncates the character string until the limit of the lenghl defined.
The solution for that puzzle is to increase the length of the varchar to 9.
varchar length is 8 and the phone number is 9 digits long ?
It’s documented behavior: https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017#truncating-and-rounding-results ;) trying to store an integer with 9 digits in a 8 char variable would give some kind of error/overflow issue, in this case it is always handled as ‘*’
The reason behind is that it prints character value of 910034568
select cast (910034568 as varchar(8)) also prints *.
It would function just ok had the constant that is fed in the variabel be no longer than 8 characters. It would be converted to a string representing the number. But the number is 9 figures long and doesn’t fit.
An error about the size would be more appropriate or about wrong data type…
Kees
Because the variable is set from an integer that is too long to fit in the length available, the implicit integer to varchar conversion results in ‘*’, meaning the result length is too short to display it.
because size is varchar(8) and phone no has 9 digit.
The number provided is long 9 characters
The unexpected result is because declare variable @phone_no length is varchar(8) and the value set to that variable has 9 character.
First, only VARCHAR(8), not VARCHAR(9). Second, no single quotes to enter phone_no.
The code tries to convert 9 digit integer to varchar(8), which is arithmetic overflow error. However, instead of throwing the error, it shows ‘*’ instead because arithmetic overflow error was not applied to data type from original sql spec. You will get error message if you try with nvarchar(8).
Were can i find the valid reason
how did that ouput generated
The value is of 9 characters while varchar size is 8. Varchar is very old datatypes than new once such as NVARCHAR of SQL. At that time Microsoft developers might have suppress such exceptions and replace it with *.
Further , when we try to same scenario with below query:-
DECLARE @phone_no Nvarchar(8)
SET @phone_no=910034568
SELECT @phone_no as phone_no
we get exception message as “Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type nvarchar.”
Which means later in the days developers at Microsoft has decided to raise proper exception message rather than replacing it with *.
Because history. Back in the original specifications for Structured Query Language, they defined the behavior for this interaction, where an int value was placed into a varchar field too short to display the full number as returning an asterisk.
Later, as SQL developed, they changed the behavior to return an overflow error. But so as to not break existing systems, the early data types, such as int and varchar retained their existing behavior. If you change the varchar variable to an Nvarchar variable, this will generate an error instead.
Is it because @phone_no is 8 characters but ‘910034568’ is 9 characters when implicitly converted to a string?
Increasing @phone_no to 9 characters (or deleting a character from 910034568) returns a proper value.
length of number your are trying to store is greater than the declared varchar length
because value (@phone_no=910034568) length is more then datatype length.