Computed column
-
Comments posted to this topic are about the item Computed column
-
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
-
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 -
Interesting question.
Keep them coming...
-
I was looking for the trick, but couldn't find one. Nice question. Thanks.
-
Nice easy one for a Monday morning. Thanks.
I was working with computed columns last week and ran across this issue.
-
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.
-
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.
😎
-
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 -
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!
-
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
-
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.
-
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)
-
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/ -
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 19 total)
You must be logged in to reply to this topic. Login to reply