Computed column

  • VM-723206

    SSCrazy

    Points: 2964

    Comments posted to this topic are about the item Computed column

  • TomThomson

    SSC Guru

    Points: 104773

    Now wouldn't it be fun if a unification capability were added to SQL so that you in an insert statement could specify the values of some computed columns and leave out the values of some of the non-computed columns and have the database engine compute the set of (new) rows that would satisfy the computations and any constraings (keys, check constraings, etc) and insert them all. :w00t:

    Or maybe not - probably safer to stick to doing that sort of thing in prolog or parlog or something.

    Tom

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Nice question. I was a bit distracted by the select 1 from #table statement, but opted to choose for the error anyway.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • paul s-306273

    SSChampion

    Points: 10615

    Interesting question.

    Keep them coming...

  • Daniel Bowlin

    SSC-Dedicated

    Points: 34566

    I was looking for the trick, but couldn't find one. Nice question. Thanks.

  • OCTom

    SSChampion

    Points: 11755

    Nice easy one for a Monday morning. Thanks.

    I was working with computed columns last week and ran across this issue.

  • SanjayAttray

    SSChampion

    Points: 13157

    Create Table #Test (a int, b int, C as a+b )

    go

    insert into #test values (3,4)

    insert into #test values (1,7)

    go

    select * from #test

    order by 1

    go

    drop table #test

    go

    Isn't it funny that when you say order by 1, sql server assumes 1 as column and gives result order by column 1 but at the same time when you execute

    select 1 from #test

    you get result as 1

    SQL DBA.

  • SanDroid

    SSChampion

    Points: 10068

    Nice question for a monday.

    I Also give this question Five stars for have correct code and no typos in the question or the answer.

    😎

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    SanjayAttray (11/8/2010)


    Isn't it funny that when you say order by 1, sql server assumes 1 as column and gives result order by column 1 but at the same time when you execute

    select 1 from #test

    you get result as 1

    That's because when you use 1 in ORDER BY, you are giving an ordinal position of a column to sort by, while with the SELECT statement, the 1 is an expression.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • UMG Developer

    SSChampion

    Points: 13482

    Nice question, I would have gotten it wrong had the option for the problem with + not been there. (I didn't see the computed column so once I went looking for the + I immediately knew what was going to happen.

    Thanks!

  • Lon-860191

    SSCrazy

    Points: 2449

    Only one small comment, the answer depends on collation for the database where the commands are issued. Some collations are case-sensitive so only the create statement would work and the rest would fail because the table doesn't exist. Other than that absolutely perfect.

    Lon

  • SanDroid

    SSChampion

    Points: 10068

    Lon-860191 (11/8/2010)


    Only one small comment, the answer depends on collation for the database where the commands are issued. Some collations are case-sensitive so only the create statement would work and the rest would fail because the table doesn't exist. Other than that absolutely perfect.

    Lon

    I treat questions here like I do when taking M$ Exams.

    The DB collation is the SQL server install default unless otherwise specified.

  • john.arnott

    SSChampion

    Points: 11882

    SanjayAttray (11/8/2010)


    Create Table #Test (a int, b int, C as a+b )

    go

    insert into #test values (3,4)

    insert into #test values (1,7)

    go

    select * from #test

    order by 1

    go

    drop table #test

    go

    Isn't it funny that when you say order by 1, sql server assumes 1 as column and gives result order by column 1 but at the same time when you execute

    select 1 from #test

    you get result as 1

    Hmmm. When I run this code to build the table, "Select 1 from #test" returns two rows, each with a "1". It's an implied join, isn't it?

    Create Table #Test (a int, b int, C as a+b )

    go

    insert into #test values (3,4)

    insert into #test values (1,7)

    go

    select 1 from #test

    go

    drop table #test

    (1 row(s) affected)

    (1 row(s) affected)

    -----------

    1

    1

    (2 row(s) affected)

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    john.arnott (11/8/2010)


    Hmmm. When I run this code to build the table, "Select 1 from #test" returns two rows, each with a "1". It's an implied join, isn't it?

    Create Table #Test (a int, b int, C as a+b )

    go

    insert into #test values (3,4)

    insert into #test values (1,7)

    go

    select 1 from #test

    go

    drop table #test

    Nope, no join at all. The logic of the result can be foundd by following the query evaluation steps in their logical order:

    1. FROM clause - refers to a single table, so intermediate result set is a copy of that table; two rows, with values {(3,4,7),(1,7,8)}.

    2. WHERE clause - not present; intermediate result set unchanged.

    3. GROUP BY clause - not present; intermediate result set unchanged.

    4. HAVING clause - not present; intermediate result set unchanged.

    5. SELECT clause - for each row in intermediate result set, built row in final result set. The intermediate result set has two rows, so the final result set will have two rows as well. The SELECT list includes one column, so the final result set will have one column. And since that single column is defined as the constant value 1, the final result set will consist of two rows, and one column; the colunm is unnamed, typed as integer, and filled with the constant value 1 in both rows.

    It's basically the same as when you use SELECT a, b, c, 1 FROM #test - except that you now leave out the a, b, and c columns - and when you have no column left that refers to the table, the results suddenly look a lot weirder than usual.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • RichardDouglas

    SSCarpal Tunnel

    Points: 4029

    UMG Developer (11/8/2010)


    Nice question, I would have gotten it wrong had the option for the problem with + not been there. (I didn't see the computed column so once I went looking for the + I immediately knew what was going to happen.

    Thanks!

    I thought the title gave it away

    Very topical, I was reading up on persistance last night

    Hope this helps,
    Rich

    [p]
    [/p]

Viewing 15 posts - 1 through 15 (of 20 total)

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