SQL SERVER – 2 T-SQL Puzzles and Win USD 50 worth Amazon Gift Card and 25 Other Prizes

We all love brain teasers and interesting puzzles. Today I decided to come up with 2 interesting puzzles and winner of the contest will get USD 50 worth Amazon Gift Card. The puzzles are sponsored by NuoDB. Additionally, The first 25 individuals who download NuoDB Beta 8 by midnight Friday, Sept. 21 (EST) will automatically receive a $10 Amazon gift card.

Puzzle 1:

Why following code when executed in SSMS displays result as a * (Star)?

SELECT CAST(634 AS VARCHAR(2))

Puzzle 2:

Write the shortest code that produces results as 1 without using any numbers in the select statement.

Bonus Q:

How many different Operating System (OS) NuoDB support?
Click here HINT

If you can solve above puzzles you will be eligible for winning USD 50 Amazon Gift Card. However, you can always enroll yourself for following Bonus Prizes where if you have good chance of winning USD 10 Amazon Gift Card (if you are first 25 individual in specific time).

Bonus Prizes:

The first 25 individuals who download NuoDB Beta 8 by midnight Friday, Sept. 21 (EST) will automatically receive a $10 Amazon gift card.

Rules:

  • Please leave an answer in the comments section below.
  • You can resubmit your answer multiple times, the latest entry will be considered valid.
  • The winner will be announced on 1st October.
  • Last day to participate in the puzzle is September 28th, 2012.
  • All valid answer will be kept hidden till September 28th, 2012.
  • Only One Winner will get USD 50 worth Amazon Gift Card.
  • The first 25 individuals who download NuoDB Beta 8 by midnight Friday, Sept. 21 (EST) will automatically receive a $10 Amazon gift card.
  • The winner will be selected using random algorithm.

UPDATE: The winner of the contest is Jaime Mtnz Lafargue (@jaimeml)

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

366 thoughts on “SQL SERVER – 2 T-SQL Puzzles and Win USD 50 worth Amazon Gift Card and 25 Other Prizes

  1. Puzzle 1
    Ans: Converting from NUMERIC data type to string never truncate the value it will produce * symbol on your output.

    Puzzle 2
    Ans: SELECT len(‘a’)

  2. Quiz 2:
    SELECT len(‘A’)

    Quiz 1:
    Varchar(2) hold maximum of 2 characters.
    So SELECT CAST(99 AS VARCHAR(2)) will show 99 and any number above 99 will show * because converting character (char, nchar, nvarchar, varchar) to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display.
    converting 634 to varchar(2) is displayed as * because * means “Result length too short to display”

  3. Puzzle #1 answer: Truncated output as a result of conversion between int/smallint/tinyint and char/varchar will result in an output of an asterisk.

    Puzzle #2 answer: select len(‘a’)

    Bonus answer : NuoDB supports 5 operating systems.

  4. Puzzle 1 -> SELECT CAST(634 AS VARCHAR(2))
    Answer—->
    The output ‘*’ is showing the data overflow while conversion(high to low precedence conversion). Problem here is we have data value(i.e. 634) having more length then the defined one which is varchar(2) in above statement.
    This can be fixed as

    SELECT CAST(634 AS VARCHAR(3))

    Puzzle 2 –> shortest code that produces results as a1 without using any numbers in the select statement
    Answer –> SELECT len(‘a’)

  5. Ans 1: We are casting 3 digit number to varchar(2) which is going out of bound thats why its giving *, if we increase it to varchar(3) it will shoe correct digit or if we pass any digit upto 99 it will show correct results. When we are converting this to varchar it is making them as three char but it can only hold 2 .

    Ans 2: Select CAST(‘True’ as bit)

  6. Here are my answers for the puzzles :

    Puzzle 1:

    Why following code when executed in SSMS displays result as a * (Star)?

    SELECT CAST(634 AS VARCHAR(2))

    Answer :

    The above statement has an integer value (634) of three digits, so when it is converted to VARCHAR data type it requires one byte per character for storage. So it needs three bytes for three characters. Since VARCHAR(2) has a length of two bytes, it fails to store all the three characters.

    Hence, the conversion occurred but it returned an invalid value of ‘asterik (*).’

    Puzzle 2:

    Write the shortest code that produces results as a1 without using any numbers in the select statement.

    Method 1

    SELECT (‘a’ + CAST(COUNT(*) AS VARCHAR(1))) AS [Result]

    Method 2

    SELECT (‘a’ + CAST(COUNT(”) AS VARCHAR(1))) AS [Result]

  7. Looks like Puzzle 2 has been updated from a1 to 1 in the question.

    so I’m updating my answers here.

    Here are my answers for the puzzles :

    Puzzle 1:
    ——————
    Why following code when executed in SSMS displays result as a * (Star)?

    SELECT CAST(634 AS VARCHAR(2))

    Answer :
    ——————

    The above statement has an integer value (634) of three digits, so when it is converted to VARCHAR data type it requires one byte per character for storage. So it needs three bytes for three characters. Since VARCHAR(2) has a length of two bytes, it fails to store all the three characters.

    Hence, the conversion occurred but it returned an invalid value of ‘asterik (*).’

    Puzzle 2:
    ——————-
    Write the shortest code that produces results as a1 without using any numbers in the select statement.

    Answer :
    ——————

    Method 1

    SELECT COUNT(*)

    Method 2

    SELECT COUNT(”)

  8. 1) as part of the rule “truncating and rounding result” its showing *. ideally from -9 to 99 will come fine but other than that range it will display a *.
    2) select ascii(‘B’)-ascii(‘A’)

  9. 1) as part of the rule “truncating and rounding result” its showing *. ideally from -9 to 99 will come fine but other than that range it will display a *.
    2) select ascii(‘B’)-ascii(‘A’)

  10. Ans 1: We are storing 3 digit number and the space allocated is only for 2 digits hence it cannot accomodate it so it shows the result as 0

    Ans2: Select COUNT(”) would return the value as 1

  11. Puzzle 1:Varchar support the integer from 0-99 for casting into the varchar datatype ,but if it exceeds from 99 to any 9 digit length number ,it shows always * at the type of conversion.

  12. Ans 1: We are storing 3 digit number and the space allocated is only for 2 digits hence it cannot accomodate it so it shows the result as ‘*’

    Ans2: Select COUNT(”) would return the value as 1

  13. Answers :

    Puzzle 1 :
    Number of digits specified in casting is 3 its greater then value specified in data type. [varchar(2)]. So that its result as a * . If you change the datatype value as varchar(3) then you got the result as 634.

    Puzzle 2 :
    SELECT (SELECT database_id FROM SYS.DATABASES
    WHERE name = ‘master’)
    I am not sure its correct way to get result as 1 without using numeric. But, my thinking its also one of the way to get result as 1 using this query.

    Bonus Q :Its supports 5 Operating Systems. They are :
    Windows, MacOS, Linux, Solaris and Joyent SmartOS

  14. Looks like Puzzle 2 has been updated from a1 to 1 in the question.

    so I’m updating my answers here.

    Here are my answers for the puzzles :

    Puzzle 1:
    ——————
    Why following code when executed in SSMS displays result as a * (Star)?

    SELECT CAST(634 AS VARCHAR(2))

    Answer :
    ——————

    The above statement has an integer value (634) of three digits, so when it is converted to VARCHAR data type it requires one byte per character for storage. So it needs three bytes for three characters. Since VARCHAR(2) has a length of two bytes, it fails to store all the three characters.

    Hence, the conversion occurred but it returned an invalid value of ‘asterik (*).’

    Puzzle 2:
    ——————-
    Write the shortest code that produces results as a1 without using any numbers in the select statement.

    Answer :
    ——————

    Method 1

    SELECT COUNT(*)

    Method 2

    SELECT COUNT(”)

    Bonus Q:
    How many different Operating System (OS) NuoDB support?

    Answer :
    ——————
    06

  15. For getting 1 as output without using numeric value i can use following sql global variable :

    select @@error+@@rowcount

    following query will give 1 as output .

  16. 2nd method for printing 1 : we can find out the difference of two consecutive ascii value no.

    select ASCII (‘b’)-ASCII (‘a’)
    This will also return 1

  17. Hi Pinal,

    The result of Puzzle No. 1 is * because of Truncating and Rounding Results information from MSDN http://msdn.microsoft.com/en-us/library/ms187928.aspx

    the result of the following query

    select CAST(634 as varchar(2)) is = *

    because * = Result length too short to display.

    Here we are casting three digit value to another data type of 2 bytes, but if we do something like this select CAST(634 as varchar(3)) , this will work properly and display 634.

    Thanks
    Manish

  18. Hi All,

    Thank you for the great responses, all the valid answers will be kept hidden. Please make sure to to answer the Bonus Q. If you have missed it earlier you can still answer it in a follow up comment.

    The contest is open till September 28th.

    Kind Regards,
    Pinal

  19. Hi Pinal,

    Please find below my answers:

    Puzzle 1:
    * is displayed because varchar(2) is not sufficient to display the result after applying cast operator to 634.Result length is too short.

    Puzzle 2:
    select ascii(‘B’)-ascii(‘A’)

    • Puzzle 1:
      After executing of the code “SELECT CAST(634 AS VARCHAR(2)) ” displays * (star) because
      “Result length too short to display.”
      Puzzle 2:
      Ans :select (@@ROWCOUNT)
      It returns always 1

  20. HI Pinal,

    The answer for the Bonus question i.e. How many different Operating System (OS) NuoDB support?

    These four (Windows, MacOS, Linux, Solaris) OS support NuoDB

    Thanks
    Manish

  21. Solution of Puzzle 1:

    When we convert NUMERIC data type( int , smallint, or tinyint) value to string data type(char or varchar) of length less than the digit counts in numeric value, then we will get ‘*’ as a result value. Here we are truncating the numeric value and sql server will show ‘*’ in result to indicate that result value length is too short to display.

    Like in this example if i convert the same int value(634) into varchar having length 3 or more i will get the same number in the result.

    SELECT CAST(634 AS VARCHAR(2)), CAST(634 AS VARCHAR(3)), CAST(634 AS VARCHAR(4)), CAST(634 AS VARCHAR)

    Output :
    (No column name) (No column name) (No column name) (No column name)
    * 634 634 634

    another example using different integer value.

    SELECT CAST(5555 AS VARCHAR(3)), CAST(5555 AS VARCHAR(4)), CAST(5555 AS VARCHAR(5)), CAST(5555 AS VARCHAR)

    Output:
    (No column name) (No column name) (No column name) (No column name)
    * 5555 5555 5555

    Solution of Puzzle 2:

    Select (LEN(‘d’))

    Answer of Bonus Q:

    NuoDB support for Windows, MacOS, Linux, Solaris, Joyent SmartOS.

    Thanks,
    Ashish

  22. ———————————————–
    Puzzle. 1
    ———————————————–
    It is due to data overflow while conversion.

    SELECT CAST(634 AS VARCHAR(3)) works fine.

    Any number with lower varchar cast will generage the same.

    Eg. SELECT CAST(444 as VARCHAR(1))
    SELECT CAST(111 as VARCHAR(2))

    ———————————————–
    Puzzle. 2
    ———————————————–
    SELECT LEN(‘.’)

    Length of any Single char will generate 1.

  23. Bonus Question: How many different Operating System (OS) NuoDB support?

    Answer: The following different types are supported operating systems

    MacOS 10.7 or higher
    Windows (32-bit, 64-bit)
    RHEL 5 & 6 (64-bit)
    SuSe 10 & 11 (64-bit)
    Amazon Basic EC2 (64-bit)
    Ubuntu 10 & 11 (64-bit)
    RHEL 5 & 6 (64-bit)
    SuSe 10 & 11 (64-bit)
    Amazon Basic EC2 (64-bit)
    Ubuntu 10 & 11 (64-bit)
    Solaris 11 (Intel 64-bit)
    Joyent SmartOS (Intel 64 bit)

  24. 1. Because the width provided in the data type is less than the length (3) of the value 634. If you can use varchar(3) or varchar then you will get 634 as the value.
    select cast(123 as varchar(2)), cast(123 as varchar)

    2. select LEN(‘a’) will give the required result.

  25. 1. Because the width provided in the data type is less than the length (3) of the value 634. If you can use varchar(3) or varchar then you will get 634 as the value.
    select cast(123 as varchar(2)), cast(123 as varchar)

    2. select LEN(‘a’) will give the required result.

    Bonus Q:
    How many different Operating System (OS) NuoDB support?

    ANS: Windows, MacOS, Linux, Solaris, Joyent SmartOS

  26. Bonus question: How many different Operating System (OS) NuoDB support?

    Answer: following mentioned operating system are supported by NuoDB

    MacOS 10.7 or higher
    Windows (32-bit, 64-bit)
    RHEL 5 & 6 (64-bit)
    SuSe 10 & 11 (64-bit)
    Amazon Basic EC2 (64-bit)
    Ubuntu 10 & 11 (64-bit)
    RHEL 5 & 6 (64-bit)
    SuSe 10 & 11 (64-bit)
    Amazon Basic EC2 (64-bit)
    Ubuntu 10 & 11 (64-bit)
    Solaris 11 (Intel 64-bit)
    Joyent SmartOS (Intel 64 bit)

  27. 1. Length of the target datatype is restricted and not enough to display the result.
    2. select CAST(‘true’ as bit)
    BQ. 5 Operating Systems

  28. PUZZLE 1: Why following code when executed in SSMS displays result as a * (Star)?

    SELECT CAST(634 AS VARCHAR(2))

    Answer: In the above query We are trying to convert 634(3 digit number) into varchar(2)(2 digit value). When we are casting any value(int/smallint/tinyint) as varchar/char type, if the length of converting value is shorter than casting value, result will be displayed as *.

    * indicates Result length is too short to display

  29. Puzzle 1 Answer:-

    In this, we are converting “634″ into varchar(2). Varchar take 1 byte for every character. Varchar(2) is to shot to display the converted data, so it’s show ” * ” according to msdn (“http://msdn.microsoft.com/en-us/library/ms187928.aspx”).

    If we will convert this into varchar(3) then it will show “634″.

    Puzzle 2 Answer:-

    SELECT CHARINDEX (‘Y’,’Your Answer Here’)

    Bonus Q Answer:-

    NuoDB supports 6 Operating Systems

  30. Hi Pinal,

    Welcome to brain teaser Puzzles…!!

    Puzzle 1:
    Why following code when executed in SSMS displays result as a * (Star)?

    SELECT CAST(634 AS VARCHAR(2))

    Ans : When integers are converted to a character data type, if the integer is too large to fit into the character field, SQL Server enters ASCII character 42, the asterisk (*). But in the case of converting Character Data to Character datatype data are truncated.

    Puzzle 2:
    Write the shortest code that produces results as 1 without using any numbers in the select statement.

    Ans:
    SELECT DIFFERENCE(‘Ant’, ‘Elephant’)

    SELECT DATALENGTH(‘ ‘)

    SELECT LEN(‘x’)

    SELECT ISNUMERIC($)

    SELECT ISNUMERIC(‘+’)

    SELECT ISNUMERIC(‘-’)

    SELECT ISNUMERIC(‘.’)

    SELECT COUNT_BIG(*)

    SELECT COUNT(*)

    SELECT CEILING(RAND())

    SELECT ISDATE(GETDATE())

    SELECT MONTH(GETDATE())/MONTH(GETDATE())

    SELECT DAY(GETDATE())/DAY(GETDATE())

    SELECT YEAR(GETDATE())/YEAR(GETDATE())

    SELECT POWER($,$)

    SELECT ISNULL(NULL,COUNT(*))

    SELECT LEN(NEWID())/LEN(NEWID())

    SELECT @@SPID/@@SPID

    SELECT UNICODE(‘A’)/ UNICODE(‘A’)

    SELECT ISNUMERIC(@@SPID)

    Regards,
    Nikhildas
    Cochin

  31. Puzzle 1:
    Why following code when executed in SSMS displays result as a * (Star)?
    SELECT CAST(634 AS VARCHAR(2))
    Answer 1:
    * Result length too short to display.

    When converting character or binary expressions (char, nchar, nvarchar, varchar, binary, or varbinary) to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display.

    E Error returned because result length is too short to display.

    Microsoft SQL Server guarantees that only roundtrip conversions, conversions that convert a data type from its original data type and back again, will yield the same values from release to release

    we can do roundtrip converion for: select cast(CAST(63 AS VARCHAR(2)) as int)
    but not for : select cast(CAST(635 AS VARCHAR(2)) as int)

    so sql server will show * because of its too short to display
    –=================================================================================================
    Puzzle 2:
    Write the shortest code that produces results as 1 without using any numbers in the select statement.
    select (your answer)
    Answer 2:
    select ascii(”)
    OR
    select count(*)
    –=================================================================================================
    Bonus Q:
    How many different Operating System (OS) NuoDB support?
    Answer BonusQ:
    MacOS
    Windows
    Linux (RHEL,Ubuntu,SuSe,Amazon Basic EC2)
    Solaris
    Joyent SmartOS

  32. Hi Pinal,

    Answer for puzzle 1:
    SELECT CAST(634 AS VARCHAR(2))

    While you are casting a number value to varchar(2), so depending upon the
    length of number, its not able to cover its complete length as you are casting it to varchar.

    Lets take an example
    select CAST(12341234 as varchar(1))
    will give you * in result. This will give you * untill we are casting it upto varchar(7). Please note that length of number is 8. Now take another example:

    select CAST(12341234 as varchar(8))
    This will return the complete number – 12341234.

    So in puzzle 1 – As per my example above
    SELECT CAST(634 AS VARCHAR(2)) or SELECT CAST(634 AS VARCHAR(1))
    will give * in result. While
    SELECT CAST(634 AS VARCHAR(3)) will give complete number

  33. Answer Puzzle 1- When we Cast (int, smallint or tinyint) to an expression of a different data type (char, varchar) data can be truncated, only partially displayed because the result is too short to display. Here the length of 634 is 3 and target varchar type is 2 thats why partially result is displayed as ‘*’.

    Answer Puzzle 2- select len(‘a’)

  34. Hi

    Puzzle 1 answer

    This is because vachar size is 2 instead of 3.

    Casting vice-versa must also work but that will not work in this case.
    SQL here is not able to cast * to 634 which is violation casting rule.

    Hence SQL is returning * .

    Regards,

  35. Hello Pinal,

    For display 1 without numbers. In general any short name function that return int, float ,etc will be divide by himself.

    select -cos(Pi())
    select Sign(Pi())
    select Pi()/Pi()

  36. Puzzle 1: Displayed result is an asterisk because converted number is too large to fit in excected data type. (*) in result means – “Result length too short to display”.

    Puzzle 2: Shortest code I am able to come up with is:
    SELECT COUNT(*)

    Bonus Q: NuoDB supports 5 different families of operating systems: Windows, MacOS, Linux, Solaris, Joyent SmartOS.

  37. Answere for Puzzle 1:
    634 is three digit Number casting by varchar(2) it will show * because Of Less Size of VARCHAR
    i mean by casting 634 we use varchar(3) (because of three digit)
    other wise it will show * in the result

    the casting number(634) length must Less than or equal to varchar size
    other wise it show * as result

    Answere for Puzzle 2:
    SELECT CAST(‘true’ as Bit)

  38. Answer for puzzle 1:
    SELECT CAST(634 AS VARCHAR(2))

    While you are casting a number value to varchar(2), so depending upon the
    length of number, its not able to cover its complete length as you are casting it to varchar.

    Lets take an example
    select CAST(12341234 as varchar(1))
    will give you * in result. This will give you * untill we are casting it upto varchar(7). Please note that length of number is 8. Now take another example:

    select CAST(12341234 as varchar(8))
    This will return the complete number – 12341234.

    So in puzzle 1 – As per my example above
    SELECT CAST(634 AS VARCHAR(2)) or SELECT CAST(634 AS VARCHAR(1))
    will give * in result. While
    SELECT CAST(634 AS VARCHAR(3)) will give complete number

    Answer for puzzle 2-

    select (char(49))

    This will return result 1 as expected

    Bonus Q:
    How many different Operating System (OS) NuoDB support?

    Answer – Five OS

  39. 1. 634 is identified as an integer by SQL Server and Asterisk is the result when an integer is converted to a varchar, char, nvarchar or nchar and truncated.

    2. SELECT len(‘a’)

    3. 5 (Windows, MacOS, Linux, Solaris, Joyent SmartOS)

  40. Puzzle 1:

    Ans: If Result length is too short to display, then result would be *

    Puzzle 2 :

    Ans: SELECT (@@ROWCOUNT)

    Bonus Q

    Ans: Windows, MacOS, Linux, Solaris, Joyent SmartOS

  41. Puzzle 1:

    Answer:-Simply as we all known Cast() function is used for data conversion.When the cast() fails or whlie the conversion data can be truncated,partially displayed or an error is returned because the result is too short to display.When we convert “int/tinyint/bigint” to “char” or “varchar” and in that case if the cast() fails then it returns “*”.Here the “*” indicates that the conversion happened with an incorret value which is too short to display.

    SELECT CAST(634 AS VARCHAR(2))

    In above SELECT statment, for the value 634 to be converted successfully to varchar type variable , 634 needs 3 bytes as 1 byte for each digit to be stored as character. But in above statement the target expression is of type varchar(2) which can store only two bytes. So the value 634 can not fit into box of varchar(2) and results in incorrect resulting value i.e. “*”.

    In above SELECT statement, any expression which requires more than 2 characters to represent the value will not be converted successfully to varchar(2) and returns “*”.
    In other words, only 1 or 2 digit values or values between 0 to 99 can be converted and if you want to convert 634 you have to use varchar(3) or greater or you can simply use “varchar” instead of “varchar(max)” means their is no requirement of providing length with target expression i.e. “varchar”.
    e.g-SELECT CAST(634 as VARCHAR)

    Puzzle 2:

    Answer:-SELECT COUNT(*)

    Bonus Q:
    Answer:-Windows, MacOS, Linux, Solaris

  42. Puzzle 1:

    Why following code when executed in SSMS displays result as a * (Star)?

    SELECT CAST(634 AS VARCHAR(2))
    Answer:
    * (star) because the Result length too short to display.

    Puzzle 2:

    Write the shortest code that produces results as 1 without using any numbers in the select statement.

    Answer:
    select COUNT(*)

    Bonus Q:

    How many different Operating System (OS) NuoDB support?
    Answer:
    5(They are Windows, MacOS, Linux, Solaris, Joyent SmartOS)

  43. 1. The size of the integer 634 is not matching with destination data type. Source has length 3 and destination is 2. So it is giving answer as *.
    SELECTCAST(634 AS VARCHAR(1)) also return *

    2. SELECT COUNT(*)

    Bonus Qustn

    Windows, MacOS, Linux, Solaris, Joyent SmartOS

  44. Puzzle 1:
    Per Books On Line, “Conversions to char, varchar, nchar, nvarchar, binary, and varbinary are truncated, except for the conversions shown in the following table.” The table then goes on to show that int, smallint or tinyint values, if becoming too short to display manifest as “*”.

    Reference: http://msdn.microsoft.com/en-us/library/ms187928.aspx

    Puzzle 2:
    SELECT COUNT(*)

    Bonus Q:
    MacOS
    Windows
    RHEL,SuSe,Amazon Basic EC2
    Ubuntu
    Solaris
    Joyent SmartOS

  45. Hello Pinal, here’s my answers:

    Puzzle 1: it returns * because the number is 3 digits long and the sql statement is trying to cast it as a varchar long 2.

    Puzzle 2: select RIGHT(ASCII(”)),1)

    Bonus Q: 5 OS

    Have a nice day!

  46. Puzle 1. Due to Truncation when converting int to varchar. This will throw error when converting to nchar, nvarchar

    Puzzle 2. SELECT (SELECT 1))

  47. the answer are as belows
    Ans 1) When you convert character or binary expressions (char, nchar, nvarchar, varchar, binary, or varbinary) to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display. Conversions to char, varchar, nchar, nvarchar, binary, and varbinary are truncated.
    * = Result length too short to display

    Ans2) select (count(*))

  48. Answer for the first question :
    You have to specify the correct length
    SELECT CAST(634 AS VARCHAR(2))
    It should have been VARCHAR(3)
    So the query when rewritten : SELECT CAST(634 AS VARCHAR(3))

    Answer for the second question :
    Asked shortest query :
    SELECT CAST(‘*’ as CHAR)

    Bonus Question answer :
    Support for Windows, MacOS, Linux, Solaris, Joyent SmartOS

  49. 1)Answer
    When you convert character or binary expressions (char, nchar, nvarchar, varchar, binary, or varbinary) to an expression of a different data type, data can be truncated
    * = Result length too short to display. E = Error returned because result length is too short to display

    2) Answer
    select (count(*))

  50. 1. SELECT CAST(634 AS VARCHAR(2)) results in ‘*’ because Int is implicitly convertible to varchar and if the length to convert is less than the no of digits then it results as a warning .
    * = Result length too short to display
    2. SELECT (@@ROWCOUNT)

  51. 1. SELECT CAST(634 AS VARCHAR(2)) results in ‘*’ because Int is implicitly convertible to varchar and if the length to convert is less than the no of digits then it results as a warning .
    * = Result length too short to display

    2. SELECT (@@ROWCOUNT)

    BonusQ:
    OS Supported by NuoDB:
    1. MacOS 10.7 or higher
    2. Windows(32-bit, 64-bit)
    3. RHEL 5 & 6 (64-bit)
    4. SuSe 10 & 11 (64-bit)
    5. Amazon Basic EC2 (64-bit)
    6. Ubuntu 10 & 11 (64-bit)
    7. Solaris 11 (Intel 64-bit)
    8. Joyent SmartOS (Intel 64 bit)

  52. PUZZLES -1:

    As we all have seen that it is very common to convert values from one data type to another while working with data. Most of the time these conversions are achieved using implicit conversion approach. There are two ways of converting values of one data type to another data type and they are referred to as Explicit Conversion and Implicit Conversion. Implicit conversion is achieved by using a value as it is already a desired type of data. Implicit conversions are those conversions that occur without specifying either the CAST or CONVERT function. Explicit conversions are those conversions that require the CAST or CONVERT function to be specified. In order to convert a value from one data type to another data type explicit conversion should be used using CAST or CONVERT functions.

    When we convert character or binary expressions like varchar to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display. Conversions to varchar are truncated if the size is smaller than the string passed as a parameter in the target data type.

    First Case:

    view sourceprint?1.SELECT CAST (634 AS VARCHAR(2))
    In the above SELECT statement, the literal 2004 is implicitly typed as INT data type. Therefore, when it tries to CAST it to VARCHAR(2) data type which does not have enough space for all the characters, the result will be returned as * . On the other hand, in order to convert the above statement successfully to varchar type, it needs 4 bytes since there are 4 digits in the source needing one byte for each digit to be stored as character type. Since in the statement, target expression type varchar(2) has a length of only 2 bytes and the value of 2004 cannot fit in this size (varchar(2)) and the result will be asterisk(*).

    Second Case:

    view sourceprint?1.SELECT CAST (’634′ AS VARCHAR(2))
    In the above SELECT statement, as per the BOL, the conversion to varchar will be truncated to get the result of the length specified in target type. Generally, CAST function converts any expression of type char, nchar, nvarchar, varchar, binary, or varbinary to an expression of any type can convert without any errors. But in the above example, CAST function truncates to the specified length of 2 used in the target type length and it does not fit into the length specified, therefore, CAST function truncates 2004 last two digit (04) and only first two digits (20) are returned. Therefore, the second select statement returns the value of 20 using CAST statement.

    PUZZLES – 2:
    select count(*)
    select len(‘a’)

    Thanks,
    Kaushik Patel

  53. PUZZLES -1:

    As we all have seen that it is very common to convert values from one data type to another while working with data. Most of the time these conversions are achieved using implicit conversion approach. There are two ways of converting values of one data type to another data type and they are referred to as Explicit Conversion and Implicit Conversion. Implicit conversion is achieved by using a value as it is already a desired type of data. Implicit conversions are those conversions that occur without specifying either the CAST or CONVERT function. Explicit conversions are those conversions that require the CAST or CONVERT function to be specified. In order to convert a value from one data type to another data type explicit conversion should be used using CAST or CONVERT functions.

    When we convert character or binary expressions like varchar to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display. Conversions to varchar are truncated if the size is smaller than the string passed as a parameter in the target data type.

    First Case:

    view sourceprint?1.SELECT CAST (634 AS VARCHAR(2))
    In the above SELECT statement, the literal 2004 is implicitly typed as INT data type. Therefore, when it tries to CAST it to VARCHAR(2) data type which does not have enough space for all the characters, the result will be returned as * . On the other hand, in order to convert the above statement successfully to varchar type, it needs 4 bytes since there are 4 digits in the source needing one byte for each digit to be stored as character type. Since in the statement, target expression type varchar(2) has a length of only 2 bytes and the value of 2004 cannot fit in this size (varchar(2)) and the result will be asterisk(*).

    Second Case:

    view sourceprint?1.SELECT CAST (’634′ AS VARCHAR(2))
    In the above SELECT statement, as per the BOL, the conversion to varchar will be truncated to get the result of the length specified in target type. Generally, CAST function converts any expression of type char, nchar, nvarchar, varchar, binary, or varbinary to an expression of any type can convert without any errors. But in the above example, CAST function truncates to the specified length of 2 used in the target type length and it does not fit into the length specified, therefore, CAST function truncates 2004 last two digit (04) and only first two digits (20) are returned. Therefore, the second select statement returns the value of 20 using CAST statement.

    PUZZLES – 2:
    select len(9)
    select count(*)
    select len(‘a’)

    Thanks,
    Kaushik Patel

  54. Hi guys,

    P1: This is because the result is being truncated and the result is too short to display.

    P2: SELECT CAST(‘true’ AS bit)

    Thanks.

  55. 1) select count(‘This is The Answer of First Question’)
    2) explicit casting result length too short to display for 634 in just 2 char

    answer for bonus que is
    nuoDB supports following os Windows, MacOS, Linux, Solaris, Joyent SmartOS

  56. Q1:SELECT CAST(634 AS VARCHAR(2))
    If you attempt an incorrect conversion (for example, if you convert a character expression that includes letters to an int), SQL Server generates an error message.

    When the output of CAST or CONVERT is a character string, and the input is a character string, the output has the same collation and collation label as the input. If the input is not a character string, the output has the default collation of the database, and a collation label of coercible-default

    When converting character or binary expressions (char, nchar, nvarchar, varchar, binary, or varbinary) to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display.

    http://msdn.microsoft.com/en-us/library/aa226054(v=sql.80).aspx

    Q2:
    Ans: select COUNT(*)

    Q3:How many different Operating System (OS) NuoDB support?
    Five: Windows, MacOS, Linux, Solaris,Joyent SmartOS

    Partha
    India

  57. 1. Because conversion from 634 does not fit to varchar(2). The culprit is the size 2.

    2. Select Count(‘x’)

    Bonus : NuoDB support 5 different OS. Windows, MacOS, Linux, Solaris, Joyent SmartOS.

  58. 1 ) SELECT CAST(634 AS VARCHAR(2))

    ANSWER
    the result length is more than 2 characters that’s why it display *… this rule will apply to all casts where result then is greater then specified length
    like SELECT CAST(10 AS VARCHAR(1))

    2) ANSWER
    select @@ROWCOUNT

  59. 1.In sql server when you try to convert int data types to char or varchar and result is too short to display then it displays ‘ * ‘.
    2. Select count(‘A’) will return 1 as output

    Bonus question answer is NuoDb Supports 5 OS (Windows, MacOS, Linux, Solaris, Joyent SmartOS)

  60. Puzzle1: The cast reduces the number of decimals that can be stored to two when there are three that need to be displayed. A star is output whenever all the digits in a number can’t fit the variable it is stored in.

    Puzzle 2: SELECT COUNT(*)

    Bonus Puzzle: 5 different OS’s

  61. Puzzle 1 : When casting data size is greater than the allocated varchar data type, the result will be shown as star. This also indicates the allocated size is not sufficient for casting

    Puzzle 2 : SELECT LEN(‘a’)

  62. Hi!

    The answer to the first question is – because this is how conversion in sql server works. If the length of converted string exceeds the desired string length, then converted string simply replaced with *.
    Internally the magic happens in function sqlCXVariant::StrConvertFromI4. According to WinDbg here is what happens:
    1) the abs value is taken from the converting int value;
    2) loop begins
    3) inside loop on each iteration argument is divided by 10 and %10 to get one digit symbol, after that number 48 is simply added to get ascii code;
    4) loop ends
    5) check if the argument was negative minus symbol is also added
    6) perform length check of converted with desired length if it is larger then simply replace string with char 42 and call the next function CXVariantBase::StrAdjustPadding
    here is how it looks like:
    –this is performed inside cycle, counting number of converted bytes
    sqlservr!CXVariant::StrConvertFromI4+0x4d: add edi,1
    –here is what we have in edi after cycle: edi=00000003
    …omitted for brevity…
    –copy desired length from memory to eax
    sqlservr!CXVariant::StrConvertFromI4+0×65: mov eax,dword ptr [ebx+8] ds:0023:337fcc70=00000002
    –here is what we have in eax:eax=00000002
    –compare results
    sqlservr!CXVariant::StrConvertFromI4+0×68: cmp edi,eax
    –perform jump to the specific brunch of code of the same function StrConvertFromI4+0x6c
    sqlservr!CXVariant::StrConvertFromI4+0x6a: ja sqlservr!CXVariant::StrConvertFromI4+0x6c (0160da7b) [br=1]
    –next instuction starts at this adress+0x6c
    –sqlservr!CXVariant::StrConvertFromI4+0x6c: test eax,eax
    …omitted for brevity…
    sqlservr!CXVariant::StrConvertFromI4+0×76: mov byte ptr [edx],2Ah ds:0023:344e24d0=28
    …further steps….
    7) if not, perform some further manipulations by calling function CXVariant::LStrConvertToStr.

    Here is my variants of the second one puzzle:
    select ascii(”)–i’m not sure if this one will work after copy/paste to/from web, in case it won’t the idea is to put here char(1) symbol, and it really gives desired result
    select count(*)

  63. when Result is too short to display so sql give the star(*) sign as result.here in this question casting of int to varchar(2) so its give the result as *.

  64. * is displayed because we are casting 634 int to varchar(2) that is result is too short to display….so it is giving * because varchar(2) is too small and we are converting int to varchar…..thank you

  65. Dear Pinal,

    The answer for today puzzle are

    Puzzle 1:

    Why following code when executed in SSMS displays result as a * (Star)?
    SELECT CAST(634 AS VARCHAR(2))

    Ans : Result length too short to display.

    Puzzle 2:

    Write the shortest code that produces results as 1 without using any numbers in the select statement.

    Ans : SELECT COUNT(*)

    Regards,
    Prasanna kumar.D

  66. Bonus Q:

    How many different Operating System (OS) NuoDB support?

    Support for Windows, MacOS, Linux, Solaris, Joyent SmartOS

  67. Puzzle 1 –
    SELECT CAST(634 AS VARCHAR(2)) –
    This statement should convert 634 into VARCHAR(2) datatype. So the expected result is “634″. But the target datatype’s size is 2, which is less than the size of the actual result (which is 3 here). So the result needs to be truncated. The * here just represents the truncation of the resultant data.

    Puzzle 2 –
    Displaying 1 without using a number –
    SELECT count(”)

    Bonus Q –
    NuoDB is an amazing product with awesome concept of cloud database. It has support for all major OSes like – Windows, MacOS, Linux, Solaris, Joyent SmartOS.

  68. Hi Pinal,

    For Question no. 2 (Write the shortest code that produces results as 1 without using any numbers in the select statement.)

    The query is

    select ASCII(‘b’)-ASCII(‘a’);

    Thanks
    Manish

  69. 1) Answer for first question is
    Result will be * in all the cases when the length of the integer is more than the casting length defined for char/varchar

    2) Answer for second question is
    SELECT CAST(CONVERT(VARCHAR(8),GETDATE(),112) AS INT)/CAST(CONVERT(VARCHAR(8),GETDATE(),112) AS INT)

  70. Puzzle 1: The conversion will lead to loss of data so the output is shown as *. As explained by MS “It requires explicit CAST to prevent the loss of precession or scale that might occur in an implicit conversion”

    Puzzle 2: Select FLOOR(TAN(45)) — 1

    Let me know if I am correct.

  71. 1. Since the varchar size is 2 but the number 634 size is bigger than the varchar size, the output results as a star(*)
    2.select len(‘a’)
    3. 6

  72. Puzzle1: The cast reduces the number of decimals that can be stored to two when there are three that need to be displayed. A star is output whenever all the digits in a number can’t fit the variable it is stored in.

    Puzzle 2: SELECT EXP(”)

    Bonus Puzzle: 5 different OS’s

  73. Puzle 1. Due to Truncation when converting int to varchar, SQL Server defaults the value to ‘*’. This will throw error when converting to nchar, nvarchar

    Puzzle 2. select (select CHARINDEX(‘A’,’A’))

  74. in the puzzzle 1 we are casting integer to varchar but as we all know char dont have sufficient bytes of space to store integer 634.
    So it is displaying star (*) because of casting to varchar from interger.

  75. Q1 : * is the error result, 634 is long to display in varchar(2)
    Q2 : select CAST(‘true’ as bit)
    Bonus Q: : MacOS, Windows, RHEL, SuSe, Ubuntu, Solaris , Joyent SmartOS

  76. select cast(634 as varchar(2))
    Result shows ‘*’ because of

    * means it requires implecit CAST to prevent the loss of precision.
    So that Query should like
    SELECT CAST(634 AS VARCHAR(3)) will shows the right out put
    OR
    SELECT CAST(634 AS VARCHAR) In this Precision set Itself as requires.

    star comes for numerical values and for Decimal value if precision is small then it shows “*”

    Thank you & Regards
    Kaushal Dhora

  77. Puzzle-1 Ans :if the Length of the Result too short to display While Converting the int/Smallint/tinyint value into Char/Varchar. By Default SSMS will display the resultset as “*”.The right Conversion for this example is Select Cast(5687 as CHAR(4)) will give the Expected result.

  78. if the Length of the Result too short to display While Converting the int/Smallint/tinyint value into Char/Varchar. By Default SSMS will display the resultset as “*”.The right Conversion for this example is Select Cast(5687 as CHAR(4)) will give the Expected result

  79. Ans 1: number are you trying to cast has length more than the size it try to cast.
    Number length is 3 and trying to convert in size 2, so we are getting result as *.

  80. Puzzle 1 : if the Length of the Result too short to display While Converting the int/Smallint/tinyint value into Char/Varchar. By Default SSMS will display the resultset as “*”.The right Conversion for this example is Select Cast(5687 as CHAR(4)) will give the Expected result.

    Puzzle 2 : SELECT COUNT(*)

  81. Puzzle 1 : if the Length of the Result too short to display While Converting the int/Smallint/tinyint value into Char/Varchar. By Default SSMS will display the resultset as “*”.The right Conversion for this example is Select Cast(5687 as CHAR(4)) will give the Expected result.

    Puzzle 2 : SELECT COUNT(*)

  82. Puzzle1: The cast reduces the number of decimals that can be stored to two when there are three that need to be displayed. A star is output whenever all the digits in a number can’t fit the variable it is stored in.

    Puzzle 2: SELECT EXP($)

    Bonus Puzzle: 5 different OS’s

  83. ANSWERS :

    PUZZLE 1:

    FROM DATATYPE : int , smallint, or tinyint
    TO DATATYPE : char , varchar
    RESULT : *

    FROM DATATYPE : int , smallint, tinyint , money , smallmoney, numeric, decimal, float, or real

    TO DATATYPE : char , varchar , nchar , nvarchar
    RESULT : E

    * = Result length too short to display. E = Error returned because result length is too short to display.

    PUZZLE 2:

    select exp(”)

  84. in the puzzle 1 we are casting 3 digits and we have written size of the varchar is 2 so because of that it is displaying *.
    It is the property of char and varchar that when we try to cast more digits or char den we written it will display *.

    and for 2nd puzzle the the shortest code foe displaying 1 is

    Select EXP(”)

  85. Answer 1
    SELECT CAST(634 AS VARCHAR(2))
    we have taken length as 2 but 634 is having length 3 hence.It is showing *

    Answer 2
    SELECT CAST(64 AS VARCHAR(1))

  86. PUZZLE 2:

    Write the shortest code that produces results as 1 without using any numbers in the select statement.

    Answer:
    select LEN(‘u’)

  87. When integers are implicitly converted to a character data type, if the integer is too large to fit into the character field, SQL Server enters ASCII character 42, the asterisk (*).

  88. Answer 1) Due to overflow from trying to cast a 3 digit integer down to a varchar of size 2.
    Answer 2) SELECT COUNT(‘A’)

  89. puzzle 1: while converting int to varchar and given length is less then the given number in this case result is very small for display and it will show *
    puzzle 2: sortest method for getting 1 without usin number
    select count(*)

  90. Puzzle 1: As the varchar is restricted to 2 places, it is showing * for 3 digited 634.
    Puzzle 2: select DB_ID(‘master’)
    Bonus Q: 5 Operating System

  91. Q1: When integers are implicitly converted to a character data type, if the integer is too large to fit into the character field, SQL Server enters ASCII character 42, the asterisk (*).

    Q2: NuoDB supports Windows, MacOS, Linux, Solaris, Joyent SmartOS.

  92. Here is the answer for the first puzzle:

    The select statment is trying to convert a number with three algarisms which is bigger than the maximum number fo algarisms allowed by varchar(2), namely, 2 characters.

    Here is the naswer to the second puzzle:
    Select ASCii(‘A’)/ASCii(‘A’)

  93. Puzzle 1 : According to SQL Help on the CAST function : “When you convert character or binary expressions (char, nchar, nvarchar, varchar, binary, or varbinary) to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display. Conversions to char, varchar, nchar, nvarchar, binary, and varbinary are truncated, except for the conversions shown in the following table…”

    So, conversions from int, smallint or tinyiny to char, varchar, nchar, nvarchar, binary, and varbinary are not truncated. That’s why in the SELECT CAST(634 AS VARCHAR(2)) statement, the “*” is displayed, because the result field is too short to display the value.

    Puzzle 2 : SELECT ISDATE(GETDATE())

    Bonus question : NuoDB supports 5 operating systems

  94. Hello. My answers :

    1- The result length is too short to display. Converting data type int to varchar does not imply trucation when the result length is shorter than necessary.
    I we had typed “SELECT CAST(634 AS VARCHAR(3))” the result would have been ’634′

    2- SELECT COUNT(*)
    That’s the shortest I could find.

    Bonus Q – 5 different OSs (Windows, MacOS, Linux, Solaris, Joyent SmartOS)

  95. Puzzle2:
    select COUNT(‘a’)

    Puzzle1:
    since we mentioned it as varchar(2) but we are referring to a 3digit number, it is unable to print the number. it will work in same manner if we reffer to any number more than 2 digits.

  96. Answer1: SQL can’t convert the 3 digit number to a varchar(2), so it replaces the value with a star because it doesn’t fit. A varchar(3) would be needed.

    Answer2: SELECT COUNT(”)

  97. Puzzle 1:
    Why following code when executed in SSMS displays result as a * (Star)?

    SELECT CAST(634 AS VARCHAR(2))
    Ans 1:
    634 is numeric, it cant be convert/reduced to varchar with the size of 2 thats why it returns “*”
    Puzzle 2:
    Write the shortest code that produces results as 1 without using any numbers in the select statement.

    SELECT CAST(REVERSE(ASCII(‘d’))AS INT) — will returns 1

    Bonus Q:
    How many different Operating System (OS) NuoDB support?
    Nuo DB will support 5 OSs which are Windows, MacOS, Linux, Solaris, Joyent SmartOS.
    notes:
    I could not able to answer all questions early morning at 7 : 10 am, coz the site http://www.wordpress.com was blocked at my office.

  98. Puzzle 1:
    Why following code when executed in SSMS displays result as a * (Star)?
    SELECT CAST(634 AS VARCHAR(2))
    Ans 1:
    A 3 digit numeric value cannot be converted/reduced to varchar with size of 2
    this is the reason the result returns “*”

    Puzzle 2:
    Write the shortest code that produces results as 1 without using any numbers in the select statement.
    ANS 2:
    SELECT CAST(REVERSE(ASCII(‘d’))AS INT)
    – the result will returns int-1

    Bonus Q:
    How many different Operating System (OS) NuoDB support?
    The NuoDB will support 5 OS which are Windows, MacOS, Linux, Solaris, Joyent SmartOS

    Note:
    My office firewall blocked http://www.wordpress.com‘s site thats why i couldn’t answer these questions early morning 7:10 am.

    thank you. this kind of puzzles are interesting and innovative.

  99. Puzzle 1:
    Why following code when executed in SSMS displays result as a * (Star)?
    Answer from http://msdn.microsoft.com/en-us/library/ms187928.aspx
    * = Result length too short to display.

    Puzzle 2:
    Write the shortest code that produces results as 1 without using any numbers in the select statement.
    Answer SELECT (@@rowcount)

    Bonus Q:
    How many different Operating System (OS) NuoDB support?
    5, Support for Windows, MacOS, Linux, Solaris, Joyent SmartOS

  100. 1) The varchar(2) doesn’t have enough room to show the number so it resorts to the *. If you use varchar(3) or greater then it displays correctly.

    2) select ASCII(”)

    Bonus: 5 operating systems

  101. Puzzle 1:
    634 converted to a char. string is ’634′, too large for a varchar(2) (much less a varchar(1)); if the 2 in the puzzle is changed to a 3, i.e.
    SELECT CAST(634 AS VARCHAR(3))
    a result other than “*” will be displayed. “*” is displayed because the result is too large — it would likewise be displayed if we attempted to CAST any three- (or larger) digit number, as opposed to 634, into a VARCHAR(2).

    Puzzle 2:
    Pick any table with at least one “int” column IC containing data and SELECT MAX(IC/IC) it. As an example, I connected to a server with a msdb database that has a non-empty table sysjobs and executed this statement:
    SELECT MAX(start_step_id/start_step_id) FROM sysjobs

    Bonus Q:
    According to the Webpage you “hint”ed at, nuoDB has “[s]upport for Windows, MacOS, Linux, Solaris, Joyent SmartOS”, i.e. five different operating systems.

  102. Hello Pinal,
    Again, great puzzles, thanks!

    Puzzle 1:
    The result is a star, because varchar(2) does not allow to display the entire string. Use instead: SELECT CAST(634 AS VARCHAR)

    Puzzle 2:
    select ascii(‘b’) – ascii(‘a’)
    or shorter:
    select ascii(”) — the character you get by: select char(1)
    Both probably not what you expect?! :-)

    Bonus Q:
    5 OS: Windows, MacOS, Linux, Solaris, Joyent SmartOS

    Best regards,
    Sparks

  103. Pinal, I have taken from your earlier post of how to display 0. Here is the simple answer to display 1

    select count(*) where ‘a’!=’b’

  104. 1) Conversions from int to varchar are truncated when result length is too short to display.

    2) select len(‘A’)

    Bonus) NuoDB supports 5 OS

  105. 1).
    Overflow, you are trying to convert the number 634 to a string but only giving the string 2 characters of space where 3 are needed.

    2).select LEN(‘a’)

  106. Puzzle 1:

    The reason is the result is too short to display.
    The result will be same for char, varchar, nchar, nvarchar. The correct result will be obtained for below query:

    SELECT CAST(634 AS CHAR(3))

    Puzzle 2:

    SELECT Count(‘Your Answer Here’)

    Bonus question:

    The supported OS are Windows, MacOS, Linux, Solaris, Joyent SmartOS.

  107. 1. Becase cannot convert 3 digit number to varchar(2)
    Same will hapen if we try SELECT CAST(63432 AS VARCHAR(3))

    2. SELECT COUNT(*)

    PS: Do not want any reward, just playing, but please reply me with the correct answers or write some blog post.

  108. 1. The result is truncated because varchar(2) is too short to display the result correctly. Ref: ‘Truncating and Rounding Results’ in ‘CAST and CONVERT’ in Books Online.

    2. select ascii(‘b’) % ascii(‘a’);

    Bonus: 5

  109. Ans 1.
    SELECT CAST (634 as varchar(2)) : Interprets conversion of expression from int data type to varchar
    While Casting, When the input of CAST is not a character string, the output has the default collation of the database, and a collation label of coercible-default. In such case, when the input is int, smallint, or tinyint and the output of CAST is varchar, the result is *

    Ans 2.
    Select Len(‘a’)

  110. SELECT CAST(634 AS VARCHAR(2))
    returns * because of not enough characters in VARCHAR, VARCHAR(3) would return the value 634.

    SELECT (ASCII(‘B’)-ASCII(‘A’)); would return a value of 1.

    Name: Ramdas
    Location: Charlotte,NC,USA

  111. Q1)
    Ans. It is showing * star because of error.

    If we replace 634 with any digit number from 1-99 it will show the number except * star.

    But if we replace 100 den it will show * as an error but if we increase the varchar size to 3 nd den write 100 or any value till 999 it sill show the number itself but again if we write 1000 as varchar(3) it will show * as an error

  112. Puzzle 1:
    Because SQL Server returns ascii character 42 (aka ‘*’) when it implicitly tries to convert an integer that is too large for it’s character field.

    Puzzle 2:
    select len(‘a’)

  113. Puzzle1: When you cast 3 characters lke 634 then you need to give in Varhcar(3) not Varchar(2)…….It’s like an overflow character.
    Puzzle2: select (count(*))

  114. Puzzle 1: when converting INT, SMALLINT or TINYINT to CHAR, or VARCHAR using CAST, and the number don’t fit in the CHAR, like trying to fit 634 in 2 characters, the result is just the character *.
    Puzzle 2: select SIGN(PI())
    Bonus Q: NuoDB supports 5 OSs

  115. Q1: The * is displayed since the cast would result on truncation from an INT to a VARCHAR that can not accommodate all the digits..
    Q2:
    SELECT COUNT(*) FROM (SELECT ” A) C

    Cheers, Pinal!

  116. Question 1: Why “select cast(634 as varchar(2))” will give (*)?
    Answer 1: the reason behind this is that in the very old versions of SQL Server, the impliciting conversions used to be handled resulting some values (* in this case) not the error message for some cases like if we try to convert the 634 to varchar(2), then logically it should give me result (’634′) but here we have varchar(2) which can not hold 3 characters so it should have been given the error message of over flow but as the int is very old or Rather I would say very first datatype to be desined in numeric integers category so the working is still same and didnot get updated as bigint etc.

    Below examples shows how this statement might have been worked:

    select cast(634 as varchar(3)) would have given ’634′
    select cast(6340 as varchar(4)) would have given ’6340′
    select cast(6340 as varchar(3)) would give (*).

    so this is the reason that our statement “select cast(634 as varchar(2))” is giving (*)

    ————————

    Question 2: How to Write the shortest code that produces results as 1 without using any numbers in the select statement?
    Answer 2: A use of deterministic system defined function will give the desired result as below.

    select ASCII(‘B’) – ASCII(‘A’)
    OR
    select ISDATE(GETDATE())
    or many more…

    Thanks for this challange and would welcome some more in future for good hands on on SQL Server.
    Nimit Goyal
    (goyal.nimit@gmail.com)
    facebook(https://www.facebook.com/nimit.goyal)
    linkedin(http://www.linkedin.com/profile/view?id=98426239&trk=tab_pro)

  117. Ans 1. becoz we are making Attempt to convert 3 digit int to two alphabet varchar which is resulting in truncation

    Ans 2. Select (1)

  118. 1-
    SELECT 1,CAST(634 AS VARCHAR(2))

    the result is * because the result is too short to display then the truncation algorithm returns *

    2-
    select (@@ROWCOUNT)

    Bonus Q-
    support 5 different OS

  119. 1) Result length too short to display due to truncation during the cast is the reason why the * is shown.

    2) SELECT(ISNUMERIC(‘ ‘) – ~ISNUMERIC(‘ ‘))

    Bonus) 5

  120. ms sql server 2008r2
    1) because 634 consist of 3 digits, but varchar(2) can cast only 2
    2)
    select(select count(*)x from(values((null)))n(sp))
    select(select ascii(‘b’)-ascii(‘a’)x)
    select(select object_id(‘sys.all_objects’)/object_id(‘sys.all_objects’))
    select(select len(‘a’)x)
    select(select cos(datediff(dd,getdate(),getdate())))

    • For First puzzle:

      SELECT CAST(634 AS VARCHAR(2))

      length of 634 is greater than 2 so it cannot accomodate in varchar of 2. if you give varchar(3) it ll display 634.

      For Second Puzzle:

      select (ASCII(‘B’)-ascii(‘A’))

      Please let me know if my answers are correct.

  121. Answer for Puzzle 1: The length of the integer value is greater than the converting value that time it is showing the result.

    Answer for Puzzle 2: 1

  122. Hi Pinal,

    Puzzle 1:
    1) Why following code when executed in SSMS displays result as a * (Star)?
    SELECT CAST(634 AS VARCHAR(2))

    A) If the length of integer value is less than the size of varchar in the conversion method, then it displays star.
    For example,

    SELECT cast( 256 as varchar(1))
    SELECT cast( 256 as varchar(2))
    SELECT cast( 256 as varchar(3)) — displays actual integer value
    SELECT convert( varchar(3), 1234)
    SELECT CAST(634 as varchar(2))
    SELECT CAST(634 as varchar(1))

    Puzzle 2:
    Write the shortest code that produces results as 1 without using any numbers in the select statement.

    A) SELECT COUNT(*)

    • Hi Pinal,

      Puzzle 1:
      1) Why following code when executed in SSMS displays result as a * (Star)?
      SELECT CAST(634 AS VARCHAR(2))

      A) If the length of integer value is less than the size of varchar in the conversion method, then it displays star.
      For example,

      SELECT cast( 256 as varchar(1))
      SELECT cast( 256 as varchar(2))
      SELECT cast( 256 as varchar(3)) — displays actual integer value
      SELECT convert( varchar(3), 1234)
      SELECT CAST(634 as varchar(2))
      SELECT CAST(634 as varchar(1))

      Puzzle 2:
      Write the shortest code that produces results as 1 without using any numbers in the select statement.

      A) SELECT COUNT(*)

      Bonus Q: How many different Operating System (OS) NuoDB support?

      A) NuoDB supports for Windows, MacOS, Linux, Solaris, Joyent SmartOS

  123. Puzzle 1: It is since casting is exceeding its limit of two characters. It can cast 0-99 in varchar(2) but if try cast three digit number into varcar(2) it can not cast it and gives you * as result.

  124. Puzzle 1

    When you convert character or binary expressions(char,nchar etc) to an expression of a different data type,
    1)data can be truncated
    2)only partially displayed or
    3)an error may return
    Because the result is too short to display.

    In the Puzzle 1

    SELECT CAST(634 AS VARCHAR(2))

    You are converting for int data type to varchar which results in ‘*’

    Meaning – The result length is too short to display

    Puzzle 2

    The answer which results 1 in the answer other than using number is given below:

    SELECT @@ROWCOUNT

    Bonus Q

    NuoDB supports 5 different OS
    1) Windows
    2) MacOS
    3) Linux
    4) Solaris
    5) Joyent SmartOS

  125. Puzzle 1:

    Why following code when executed in SSMS displays result as a * (Star)?

    SELECT CAST(634 AS VARCHAR(2))

    Ans : Since VARCHAR(2) is Mentioned in the Code, the Result is * (Star).
    Which indicates THE RESULT LENGTH TOO SHORT TO DISPLAY

    If You Change the to SELECT CAST(634 AS VARCHAR(3)) then It will work fine.

    Puzzle 2:

    Write the shortest code that produces results as 1 without using any numbers in the select statement.

    SELECT (YOUR ANSWER HERE)

    Ans : SELECT LEN(‘A’) will ouput as 1.

    Very Good Puzzles Sir.. I enjoyed with this.

    Thanks Lot and Expecting still more Puzzles from Your side.

  126. 1. Because varchar(2) is specified and int is of 3 digits. Hence it converts all numbers more than 100 to default value *.
    2. SELECT CAST(GETDATE() AS INT)/CAST(GetDate() AS INT)
    Bonus Q. Windows, MacOS,RHEL 5 & 6, SuSe 10 & 11 ,Amazon Basic EC2, Ubuntu 10 & 11, Solaris 11, Joyent SmartOS

  127. Puzzle 1:
    When an int, tinyint or smallint is converted to a varchar (or char) and is truncated a * is returned.

    Puzzle 2:
    SELECT CAST(‘true’ as BIT)

    Bonus Q:

    Windows, MacOS, Linux, Solaris, Joyent SmartOS

    Good fun!

    Graham

  128. Ohh I am late to this contest but would like to post the answer.

    Answers:
    Puzzle01: SQL Server throws a * because we are passing 3 string characters with assigning 2 in memory. Technically it’s an overflow. If we will assign VARCHAR(3) then we will get 634.

    Puzzle02: SELECT LEN(‘A’)
    There are many way to solve the puzzle 2 but I feel above is the shortest SELECT query to get the result 1

  129. SELECT CAST(532 AS VARCHAR(2)) — This is because of the overflow of 532 which is of 3 characters
    select LEN(‘L’) — This gives the result as 1.

  130. ANswer 1: because of Overflow. there are three integers in the given number and we are casting it in the length of two character string. so, result is ‘*’ and it indicates that result length too short to display.

    Answer 2: Select Count(0)

    Bonus Q: 5 different Operating System (OS) supports NuoDB

  131. puzzle 1

    Ans:

    SELECT CAST(634 AS VARCHAR(2))

    Because of data truncation sql server produces * data truncation.

    whenever data truncation for following explicit conversion sql server results *.

    int, smallint, or tinyint -> char,varchar

    puzzle 2

    Write the shortest code that produces results as 1 without using any numbers in the select statement.

    Ans:

    SELECt CHAR(ASCII(‘Q’)-ASCII(”))

    Bonas Q:
    How many different Operating System (OS) NuoDB support?

    Ans:

    5

  132. 1) SELECT CAST(634 AS VARCHAR(2)) – Returns a star because of loss of precision
    2) SELECT (CAST(‘true’ as bit))
    Bonus: 5 unless you count the flavors of Linux as seperate

  133. when increase size of varchar it return real data, 634 or any 3 digit no. is not coming in range of varchar size which is given in varchar(2). conversion from int and smallint to varchar which size is smaller than given int value length, is not truncated. that’s why it returns *.

    and second answer is .
    SELECT (CAST( ‘true’ AS bit))

  134. Puzzle #1:
    (help in SSMS)
    When you convert character or binary expressions ( char, nchar, nvarchar, varchar, binary, or varbinary) to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display. Conversions to char, varchar, nchar, nvarchar, binary, and varbinary are truncated, except for the conversions shown in the following table.

    Data type => to data type => Result
    int, smallint or tinyint => VarChar => *

    Puzzle #2:

    select COUNT(*)

    Bonus:
    Answer: 5

    1. Windows
    2. MacOS
    3. Linux
    4. Solaris
    5. Joyent SmartOS

  135. Puzzle1: * is displayed because result length too short to display. String ’634′ of 3 chars doesnt fit in varchar(2)

    Puzzle2: SELECT LEN(‘x’)

    Bonus Q: 10 Operating systems. Note: RHEL 5 & 6 is considered as a single operating system here.

    • My original comment didn’t format correctly. The command is: SELECT ACII(‘SOH’); The SOH character is x01, which is created by opening up a text editor, entering ALT-001 and copying and pasting the result into the SQL statement.

  136. Because it tries to convert 634 to character form but since the size of varchar declared is only 2, it returns unkown data

    select count(*)

  137. 1. When you convert character or binary expressions (char, nchar, nvarchar, varchar, binary, or varbinary) to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display. (per books online) An * = Result length too short to display.

    2. select day(”)

  138. In short, a * means the length of the result is too short to display. * is the result of an int, smallint, or tinyint (from) datatype whose length is longer than the length defined for the char or varchar (to) datatype.

    “E” would be the result if the (to) datatype was nchar or nvarchar in this scenario. “E” would also be the result if the (from) datatype was money, smallmoney, numeric, decimal, float or real and the (to) datatype was char, varchar, nchar, or nvarchar in the same scenario.

  139. Ans 1) varchar(2) is too short to display conversion result , so it is displaying * (Star).
    Ans 2) I have found 7-8 ways to get result 1 without using any number in select statement. But, in terms of shortest code, I have found 2 statement with exactly same size.
    First answer is inspired by one of your earlier post

    Select Count(*)

    And Second answer is

    Select len(‘A’)

    However, 2nd answer is shortest in terms of execution plan also.

    Ans of Bonus Question ) NuoDB supports different Operation systems like Windows , MacOS, Linux, Solaris & Joyent SmartDB.

  140. SELECT LEN(‘A’)
    SELECT COUNT(*)
    SELECT @@ROWCOUNT
    SELECT PI()/PI()
    SELECT POWER(234234234, 0)
    SELECT ASCII(‘B’) – ASCII(‘A’)
    SELECT MONTH(’1 JAN 1990′)

  141. Answer for Puzzle 1:-

    SELECT CAST(634 AS VARCHAR(2))

    * = Result length too short to display (special case of Truncating and Rounding Results) , SQL used to display * for this case. SQL gives Result as E when you convert money to varchar.
    E = Error returned because result length is too short to display.

    Answer for Puzzle 2:-

    SELECT LEN(‘a’)

    Answer for Bonus Question:-
    I am not pretty sure for this answer, I believe NuoDB support SmartOS.

    Regards,
    Girijesh

  142. 1.because we are converting 3 digit number to 2 digit varchar,which is an implicit conversion.so due to length of varchar,data will be lost.So in case of lost data,sql server displays * everytime
    2.select len(‘m’)

  143. Hi Lakshmi Here,

    Answer for Puzzle 1: It gives star because when the output length exceeds provided,.A three-character result cannot fit into a two character space.

    Answer for Puzzle 2: select len(‘a’)

  144. Puzzle 1 – Answer
    The space provided is for 2 character, but since there are 3 characters SQL server uses shorthand and displays *

    Puzzle 2 – Answer
    SELECT CAST(GETDATE() AS INT)/CAST(GETDATE() AS INT) AS ANSWER

  145. Puzzle 1) Converting tinyint, smallint or int to char or varchar(in the case of the puzzle) will display asterisk because the result is too short to be displayed.

    Puzzle 2) SELECT ISDATE(GETDATE());

  146. Here are my answers:

    1. When integers are implicitly converted to a varchar data type, if the integer is too large to fit into the varchar datatype field, SQL Server enters ASCII character 42, the asterisk (*).

    2. we have some mathematical functions. I picked cos and exp. All of these statements return 1 as a result:

    select cos($) — dollar
    select exp(£) — British pound

    select cos(₡) — Costa Rican colón
    select exp(₡)

    select cos(₦) — Nigerian naira
    select exp(₦)

    select cos(¥) — Japnese Yen
    select exp(¥)

    To be honest, you can use almost any currency sign in cos or exp function and it will return 1. The thing is that currency sign is treated like a numeric value:

    eg: select isnumeric(₡) returns 1 and select $ returns 0.00. So at cos($) and exp($) we have value equal to one as these functions represent 1 at a zero point.

    3. BONUS Q:

    5 OS: Windows, MacOS, Linux, Solaris, Joyent SmartOS

  147. Puzzle 1: Overflow, 634 is longer than 2 characters, so SSMS displays a *

    Puzzle 2: SELECT count(*)

    Bonus Q: Support for Windows, MacOS, Linux, Solaris, Joyent SmartOS

  148. Puzzle 1: under MSDN, result of conversions from int, smallint, or tinyint to char or varchar can be * which means “Result length too short to display”

    Puzzle 2: SELECT(PI()/PI())

    Bonus Q: Windows, MacOS, Linux, Solaris, Joyent SmartOS

  149. This is documented in the Truncating and Rounding Results section of CAST and CONVERT in Books Online. The * means ‘Result length too short to display’. This odd behaviour is maintained for backward compatibility with old versions of SQL Server. The more modern types nchar and nvarchar return an error instead:

    SELECT CAST(643 AS nvarchar(1));
    SELECT CAST(45 as nchar(1));

    Msg 8115, Level 16, State 2, Line 1
    Arithmetic overflow error converting expression to data type nvarchar.

  150. Hi Pinal,

    Ans:

    Puzzal 1
    SELECT CAST(634 AS VARCHAR(2)) Query
    When converting INT to CHAR/VARCHAR If result length is too short to display (ie: VARCHAR(2) in our query) by default it display *.If it is SELECT CAST(63 AS VARCHAR(2)) it display 63.If we add any 1 or more value after 63 and convert it to VARCHAR(2) ie SELECT CAST(634 AS VARCHAR(2)) displays * by default .

    Puzzal 2
    SELECT (COUNT(*))
    –O/P: 1

    Thanks & Regards,
    Abhijeet Desai

  151. Hi Sir,

    PUZZLE 1
    ——————
    Why following code when executed in SSMS displays result as a * (Star)?

    SELECT CAST(634 AS VARCHAR(2))

    ANSWER : When we try to cast a numeric value to a varchar less than the length of the original numeric value
    by default (*)star sign will be displayed in the result

    Puzzle 2:
    ——————

    Write the shortest code that produces results as 1 without using any numbers in the select statement.

    ANSWER :
    The shortest code that produces results as 1 is SELECT COUNT(*)

    Thanks and Regards,
    P.Anish Shenoy
    Quadwave, Bangalore

  152. Answer for Bonus Q

    Nuo DB Supports different OS :
    1. MacOS 10.7 or higher
    2. Windows
    (32-bit, 64-bit)
    3. RHEL 5 & 6 (64-bit)
    4. SuSe 10 & 11 (64-bit)
    5. Amazon Basic EC2 (64-bit)
    6.Ubuntu 10 & 11 (64-bit)
    7. Solaris 11 (Intel 64-bit)
    8. Joyent SmartOS (Intel 64 bit)

    Thanks and Regards,
    P.Anish Shenoy
    Quadwave, Bangalore.

  153. 1.You are casting three digit number with two digit varchar so it will give * ouput for any number greater two digits

    2.select (len(‘sudhir’)/len(‘sudhir’)) it will give 1

  154. Hi Pinal

    Puzzle 1:
    Why following code when executed in SSMS displays result as a * (Star)?

    Answer:

    When we convert character or binary expressions like char,varchar,nchar,nvarchar to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display. Conversions to varchar are truncated if the size is smaller than the string passed as a parameter in the target data type.

    In the above SELECT statement, the literal 634 is implicitly typed as INT data type. Therefore, when it tries to CAST it to VARCHAR(2) data type which does not have enough space for all the characters, the result will be returned as * . On the other hand, in order to convert the above statement successfully to varchar type, it needs 3 bytes since there are 3 digits in the source needing one byte for each digit to be stored as character type. Since in the statement, target expression type varchar(2) has a length of only 2 bytes and the value of 634 cannot fit in this size (varchar(2)) and the result will be asterisk(*).

    Reference: http://msdn.microsoft.com/en-us/library/aa226054%28v=sql.80%29.aspx
    http://msdn.microsoft.com/en-us/library/ms176089.aspx

    Puzzle 2:

    Write the shortest code that produces results as 1 without using any numbers in the select statement.

    Answer:

    Some simple select statement to return 1

    SELECT count(‘x’)
    go
    select len(‘x’)
    go
    select count_big(‘c’)
    go
    select @@Rowcount
    go
    select Rowcount_Big()
    go
    select Isnumeric(‘.’)
    go
    select Getansinull()
    go
    select Pi()/Pi()
    go
    select Isdate(getdate())
    go
    select @@Textsize/@@Textsize
    go
    select @@Datefirst/@@Datefirst
    go
    select @@Lock_Timeout/@@Lock_Timeout
    go
    select @@Max_Connections/@@Max_Connections
    go
    select @@Max_Precision/@@Max_Precision
    go
    select @@Options/@@Options
    go
    select @@Textsize/@@Textsize
    go
    select @@Rowcount/@@Rowcount
    go

    Thanks
    Vijayakumar P
    Kochi,India

  155. Puzzle 1: The * indicates an overflow error caused by truncation

    Puzzle 2: SELECT LEN(‘a’)

    Bonus Q: 5 OS are supported – Windows, MacOS, Linux, Solaris, Joyent SmartOS

  156. #1: An error is being produced because 634 is 3 characters but we are converting to varchar(2)

    #2: Select CHARINDEX(‘a’,’abc’)

  157. Question 1: the result is too small for the size of the number therefore the cast fails and a ‘*’ is displayed as the result.
    Question 2: declare @x bit=0; select ~@x

  158. Hi Pinal:

    Puzzle 1: The star (*) is displayed because the results is too large to fit – this is similar to Excel displaying ‘###’ in columns that are not wide enough to display the data.

    Puzzle 2: select COUNT(*) is the shortest select to produce result of 1 without using numbers If needed, it can be select (COUNT(*)).

    BONUS Q: 5

    Thanks and have a great day,
    Dave

  159. Puzzle 1:

    SELECT CAST(634 AS VARCHAR(2))

    it returns * because the conversion result set is too low to return the entire result.

    If we change the query to

    SELECT CAST(634 AS VARCHAR(3))

    then it will returns 634.

  160. Puzzle 1:
    * is define in cast function and it appear if the result length too short to display.
    in SELECT CAST(634 AS VARCHAR(2)) we are trying to cast a number of three length into two length of varchar,
    SELECT CAST(’634′ AS VARCHAR(2)) if we define it like this it will returns 63 because datatype is compatible and no need of explicit conversion.

    Puzzle 2:
    Select Len(‘A’)

  161. Q1: because of Overflow, as you are trying to cast the 3 digit number on 2 letter varchar. If you use Varchar(3), you will get the 634 as answer.
    Q2: SELECT LEN(‘A’)

  162. 1. As the length of the integer(682) is greater than specified in the Cast Function (i.e 2).
    2. SELECT @@ROWCOUNT
    OR SELECT ROWCOUNT_BIG()
    3. NuoDB Support Windows, MacOS, Linux, Solaris, Joyent SmartOS

  163. For question 1, the reason is because the result is too large for the display size. In which case, the default response for SSMS is to display ‘*’ indicating that it does not have sufficient space to display the result due to the truncation on casting. To make that work, the value should first be cast to varchar with a size of at least 3, then cast again to varchar(2) OR using the SUBSTRING function to get the first two characters.

    For question 2, there several options, but “SELECT ASCII(‘b’)-ASCII(‘a’)” is a very simple one.

  164. Puzzle1:
    in “Select cast(634 as varchar(2))” we are trying to convert 3 digits to 2 char. This overflows the target string which is shown as “*” in SQL server.

    At least Varchar(3) is required to display the number. Even Varchar(1) with this number will return “*”

    Puzzle2:
    Select len(‘a’)

    will return 1

    Bonus Q:
    NuoDb supports 6 different Os

  165. Hi, just saw your contest on LinkedIn. Very nice of you to sponsor. This is the best that I came up with. It can’t get too much shorter :-)

    select len(‘a’)

    Thanks!

  166. Puzzle 1 —> Since 634 contains 3 digits, casting it to varchar also contains 3 digits. However, we have restricted the result length to 2. So the result length is too short to display the actual result. Hence an ‘*’ is displayed. If we increase the length to 3 or more or ommit the length (which will by default take 30 characters), it will display the proper output of 634.

    Puzzle 2 —-> select len (‘a’)
    You can select length of any single non-numeric character.

  167. Puzzle 1:
    It is becuase the size of the value specified(2) is not enough to hold the number(634). In this case you wont get any error.
    You should in advance specify the enough length minimum 3. SELECT CAST(634 AS VARCHAR(3))

    Puzzle 2:
    SELECT CHARINDEX(‘M’,’M’)

  168. Answer 1:
    When converting character or binary expressions (char, nchar, nvarchar, varchar, binary, or varbinary) to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display.
    * Result length too short to display.
    E Error returned because result length is too short to display.
    Answer 2:
    select len(‘a’)

  169. 1.) Whenever trying to convert character or binary expressions ( like char, nchar, nvarchar, varchar, binary, or varbinary) to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display but except for some data types like here int /small int to char/Varchar . Then it finally returning *.

    2.) select Len(‘a’) ,it returns the 1 value

    3.) 5 types of OS ( Windows , Linux, MacOS , Joyent SmartOS , Solaris)

  170. My second entry for puzzle 2: SELECT ’1′ — technically ’1′ is a character and not a number, just like ‘A’ :-)

  171. Puzzle 1
    –> Because [SELECT CAST(634 AS VARCHAR(2))], here length of ’634′ is 3 whereas we casted numeric value to 2 length varchar which exeeded.[SELECT CAST(634 AS VARCHAR(3))] would give 634 as output.

    Puzzle 2
    select COUNT(‘A’)

    Bonus Q

    NuoDB supports for 5 OSs Windows, MacOS, Linux, Solaris, and Joyent SmartOS

  172. Puzzle 1: 634 has 3 characters and cannot be converted by SQL to 2 chars. * means error
    Puzzle 2: SELECT (ASCII(‘B’)-ASCII(‘A’))

  173. The answer to your query is: “Historical reasons”
    When integers are implicitly converted to a character data type, if the integer is too large to fit into the character field, SQL Server enters ASCII character 42, the asterisk (*).
    The datatypes INT and VARCHAR are older than BIGINT and NVARCHAR. Much older. In fact they’re in the original SQL specs. Also older is the exception-suppressing approach of replacing the output with asterisks.
    Later on, the SQL folks decided that throwing an error was better/more consistent, etc. than substituting bogus (and usually confusing) output strings. However for consistencies sake they retained the prior behavior for the pre-existing combinations of data-types (so as not to break existing code).
    So (much) later when BIGINT and NVARCHAR datatypes were added, they got the new(er) behavior because they were not covered by the grandfathering mentioned above.

  174. HI PInaldave,

    Q1 – Varchar(2) is limited to 2 characters, while the response is 3 characters. Star is a common way to show when the result is longer than field length
    Q2 – I have 2
    Select COUNT(*)
    Select LEN(‘a’)

    NuoDb supports 5 different OS

  175. Puzzle #1 When you convert data types that differ in decimal places, sometimes the result value is truncated and at other times it is rounded.

  176. Hi Team we have more than 300 comments. The winners are to be announced on 1st but looking at all the various entries – we will have all the comments will be visible on before 1st Oct.

    However, the announcement of the winner may take 2-3 days extra as each of the 300 entries have to be evaluated and the winner is to be selected after careful evaluation.

    Tomorrow is the last day to take part in the Quiz!

    • Puzzel 1 Answer-
      SELECT CAST(634 AS VARCHAR(2))
      This displays result as a * (Star) because varchar max length is 2 and the above statement casting 3 digit integer value into character which max length is 2

  177. 1 When CAST() fails to convert any expression of type tinyint, smallint or int to an either char or varchar datatype and produces incorrect resulting value that is too short to display, then it returns * . Here, the returned value * indicates that conversion happened with incorrect resulting value which is too short to display. This is the reason why the statement returns “*”:

    2.select count(*)

    • 1. “Arithmetic overflow error converting numeric to data type varchar” not handled for numeric value as it handled for decimal values
      2. SELECT ISDATE(GETDATE())

      • 1. “Arithmetic overflow error converting numeric to data type varchar” not handled for numeric value as it handled for decimal values
        2. SELECT len(‘.’) or SELECT ’1′

  178. Hi Pinal Dave,

    Here is my contest entry…

    Puzzle 1:
    When executing “SELECT CAST(634 AS VARCHAR(2))”, an asterisk/star (*) is displayed because the varchar(2) is too short in length to display the 3-digit integer that is cast to it.
    * = Result length too short to display (see table in “Truncating and Rounding Results” section of http://msdn.microsoft.com/en-us/library/ms187928.aspx)

    Puzzle 2:
    The shortest code I could think of that produces a result of 1 without using any numbers in the select statement is:
    SELECT(LEN(‘A’))
    OR
    SELECT(COUNT(*))

    Bonus Q:
    How many different Operating System (OS) NuoDB support? 5: Windows, MacOS, Linux, Solaris, Joyent SmartOS

  179. Hi Pinal Dave,

    Here is my contest entry…

    Puzzle 1:
    When executing “SELECT CAST(634 AS VARCHAR(2))”, an asterisk/star (*) is displayed because the varchar(2) is too short in length to display the 3-digit integer that is cast to it.
    * = Result length too short to display (see table in “Truncating and Rounding Results” section of http://msdn.microsoft.com/en-us/library/ms187928.aspx)

    Puzzle 2:
    The shortest code I could think of that produces a result of 1 without using any numbers in the select statement is:
    SELECT(LEN(‘A’))
    OR
    SELECT(COUNT(*))

    Bonus Q:
    How many different Operating System (OS) NuoDB support? 5: Windows, MacOS, Linux, Solaris, Joyent SmartOS

  180. 1: * indicates that an error DID NOT occur and that the result length is too small to display as a result of the cast.
    2: SELECT LEN(‘a’)
    Bonus: Windows, MacOS, Linux, Solaris, Joyent SmartOS

  181. Ans 1. This is a conversion from int to varcar. Since varchar length is smaller than the length of the integer, by default SQL server shows ‘*’. This is true for any conversion from int, smallint, tinyint to char or varchar of length less than the length of the int, smallint, tinyint.

    Ans 2. SELECT(COUNT(*))

  182. Hi Pinal,

    Answer 1

    When we convert character or binary expressions (char, nchar, nvarchar, varchar, binary, or varbinary) to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display. Conversions to char, varchar, nchar, nvarchar, binary, and varbinary are truncated, except for the conversions shown in the following.

    From data type: int , smallint, or tinyint
    To data type: char and varchar
    Result as * (star).
    To data type nchar and nvarchar
    Result as Error.

    From data type: money , smallmoney, numeric, decimal, float, or real
    To data type: char, varchar, nchar and nvarchar.
    Result as Error.

    364 as int so shows * as result.

    Answer 2:

    Select isnumeric(‘+’)

    i use msdn for first answer
    link: http://msdn.microsoft.com/en-us/library/ms187928.aspx

  183. This happens because the size of varchar type is small then needs to be. So the ‘*’ (star) appears because when sql server did the cast the size of the varchar is not enough.So the sql server show a star(*).

    For example:

    SELECT CAST(123 AS VARCHAR(2)) dosen´t work.

    SELECT CAST(123 AS VARCHAR(3)) It´s work!

    Thank you!

  184. Copy the script and paste it in ssms. When you run this script you will get 1 as result.
    There is character, whose ascii value is 1, between quot’s.
    ——Script———
    select ASCII(”)
    ——Script———

  185. Puzzle 1
    when converting number to varchar, if the varchar range is not properly set, any Arithmetic overflow error will display a star.
    the same behaviour will be observed with select (convert(nvarchar(2), 634))

    Puzzle 2
    select (CHAR(49))

  186. select cast(634 as varchar(2))

    means the length of the integer value is greater than the converting value that time it is showing the result.

  187. (1) VARCHAR(2) isn’t big enough for three digit int, and conversion from int to VARCHAR which doesn’t fit returns * instead of truncating
    (2) select (count(*))
    (3) 5

  188. Hey Pinal, I am exited and desperate to know the result, and its already Oct 1st started in India, Please share the results as early as possible.

  189. Hey Pinal, I am exited and desperate to know the result, and its already Oct 1st in India, Please share the results as early as possible.

    Thanks
    Manish

  190. Hi All,

    There are total 350+ entires. The contests is over but it will take some more time to decide the winner because – well, if you will read all the comments, you will understand why – there are great great answers and excellent learning!

    I will announce the date and winners very soon!

    Kind Regards,
    Pinal

  191. Pingback: SQL SERVER – Solution – 2 T-SQL Puzzles – Display Star and Shortest Code to Display 1 « SQL Server Journey with SQL Authority

  192. SELECT ’1′

    the reason for using ’1′ (and I got some slack for using ’1′) is because technically ’1′ is NOT a number it is a char! so select ’1′ does reproduce the requested output, and if you where using ado to set to int sql server likes to do something called implicit casting.

  193. SELECT ’1′

    the reason for using ’1′ (and I got some slack for using ’1′) is because technically ’1′ is NOT a number it is a char! so select ’1′ does reproduce the requested output, and if you where using ado to set to int sql server likes to do something called implicit casting.

  194. Hi All,

    We thank you for your kind participation. Here is the winner of Amazon USD 50 Gift Card

    Jaime Mtnz Lafargue (@jaimeml)
    http://blog.sqlauthority.com/2012/09/20/sql-server-2-t-sql-puzzles-and-win-usd-50-worth-amazon-gift-card-and-25-other-prizes/#comment-353572

    Jaime, please send me email at pinal at SQLAuthority.com .

    The first 25 downloads of Nuodb will automatically receive their gift cards.

    We thank you for your participation and appreciate your support. We will come up with another interesting puzzle very soon!

    Kind Regards,
    Pinal

  195. Hi pinaldave. Why is my answer not correct? I answer sooner than Jaime.

    “This is my answer. Question 1: When converting number to character type, the result display * if result length too short to display.
    Question 2: select cos($)”

    • Hi Doan,

      It is not about one answer correct and another not correct. We put all the good answers in a single table and selected random winner. As there was one winner Jaime got selected.

      I have personally great respect for your answer too. As I said it is a randomly picked correct answer. Additionally there was the deadline to submit the answer – this means all the answers are equally eligible irrespective to when it was submitted.

      I hope you understand.

      Kind Regards,
      Pinal

  196. Pingback: SQL SERVER – Weekly Series – Memory Lane – #047 | Journey to SQL Authority with Pinal Dave

  197. Pingback: SQL SERVER – Weekly Series – Memory Lane – #049 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s