Minimalistic SELECT

  • Thomas Franz

    Hall of Fame

    Points: 3565

    Comments posted to this topic are about the item Minimalistic SELECT

    God is real, unless declared integer.

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71446

    Nice question to start the week on, thanks Thomas

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • Carlo Romagnano

    SSC-Insane

    Points: 21810

    The explanation is that constant numbers are evaluated as one token! It doesn't matter if there is not a space at the end of the constant

    These queries are equivalent:

    SELECT 1.test
    SELECT 1. test
    SELECT 0+1test
    SELECT 1 AS test
    ORDER BY 1DESC -- same thing

     

  • webrunner

    One Orange Chip

    Points: 29948

    Thanks, Carlo. That's crazy!

    -- webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and says Can I join you?
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Thomas Franz

    Hall of Fame

    Points: 3565

    @Carlo: so the dot in SELECT 1.test is not a whitespace or a dot as between filename and extension, but a simple decimal separator without a following decimal number as in SELECT 1.0test

    God is real, unless declared integer.

  • Carlo Romagnano

    SSC-Insane

    Points: 21810

    That's true! It's the decimal separator.

  • Oddvar Eikli

    SSC Journeyman

    Points: 88

    However, the data type isn't strictly speaking an INT it is a DECIMAL(1,0):

    The query

    sp_describe_first_result_set @tsql = N'SELECT 1.test;' 

    shows it is a system_type_id 108 (numeric) and the system_type_name is "numeric(1,0)".

  • George Vobr

    SSCrazy Eights

    Points: 8995

    Thanks Thomas for a funny but interesting question, and thanks

    Oddvar for the reminder sp_describe_first_result_set.

    This clearly shows that if the expression before the dot is data type numeric,

    then the expression after the dot is taken as a column name.

    For example, try these expressions:

    SELECT '1'*1.test
    UNION
    SELECT AVG(1+1)*1.test;
    GO
    /*
    Results
    test
    ---------------------------------------
    1
    2

    (2 row(s) affected)
    */
    -- Next, verify the expression data type before the dot:
    sp_describe_first_result_set @tsql = N'SELECT AVG(1+1)*1.test;';
    GO
    sp_describe_first_result_set @tsql = N'SELECT ''1''*1.test;';
    GO
  • Carlo Romagnano

    SSC-Insane

    Points: 21810

    This clearly shows that if the expression before the dot is data type numeric,

    then the expression after the dot is taken as a column name.

    The dot is part of constant number, it's the decimal point and not a separator.

  • George Vobr

    SSCrazy Eights

    Points: 8995

    Hi Carlo, yes you're right. The first dot in constant numeric number in the selected

    expression is the decimal point. The second dot is a "virtual AS".

    SELECT 1.77+0. test;
    GO

    sp_describe_first_result_set @tsql = N'SELECT 1.77+0. test';
    GO

    Results
    test
    ---------------------------------------
    1.77

    (1 row(s) affected)

    is_hidden column_ordinal name is_nullable system_type_id system_type_name
    --------- -------------- -------------------------------------------------------------------------------------------------------------------------------- ----------- -------------- ----------------
    0 1 test 1 108 numeric(4,2)

    (1 row(s) affected)
  • Carlo Romagnano

    SSC-Insane

    Points: 21810

    The second dot is a "virtual AS".

    Absolutely NO, but if you like to think so OK.

  • George Vobr

    SSCrazy Eights

    Points: 8995

    Ah..., my apologies, it is obvious that in the expression '1.77 + 0. test' is the second dot also an decimal point. I had to be blind. I have overlooked your explanation from the examples above. Thanks again for your post.

  • t.ovod-everett

    Ten Centuries

    Points: 1310

    Oddvar Eikli wrote:

    However, the data type isn't strictly speaking an INT it is a DECIMAL(1,0):

    The query

    sp_describe_first_result_set @tsql = N'SELECT 1.test;' 

    shows it is a system_type_id 108 (numeric) and the system_type_name is "numeric(1,0)".

    I concur - numeric(1,0) may be similar to an int (i.e. it only stores integers), but it has a different range, different behaviors, etc.

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply