February 1, 2011 at 6:13 am
Hi,
If i run the below query it is getting executed but it should throw error since id column is not there in sys.identity_columns table resulting in
Msg 207, Level 16, State 1, Line 1
Invalid column name 'id'.
but why it is getting executed?
Select * from Sys.sysobjects where id in(Select id from sys.identity_columns)
Whether Sub-Query is not checked or how it behaves.What might be reason for this
Actual syntax is
Select * from Sys.sysobjects where id in(Select object_id from sys.identity_columns)
Thanks
Parthi
Thanks
Parthi
February 1, 2011 at 6:24 am
Because its equivalent to
Select * from Sys.sysobjects where id in(Select sysobjects.id from sys.identity_columns)
So there is no syntax error
February 1, 2011 at 6:32 am
Dave Ballantyne (2/1/2011)
Because its equivalent to
Select * from Sys.sysobjects where id in(Select sysobjects.id from sys.identity_columns)So there is no syntax error
Hi,
Sub-query should need to executed here but why it is not happening here.Subquery is failing here then how cum :w00t: :w00t:
Thanks
Parthi
Thanks
Parthi
February 1, 2011 at 6:36 am
You can find the reason in this URL – http://msdn.microsoft.com/en-us/library/ms178050.aspx%5B/url%5D. If you don’t specify the column’s source in the subquery, it will first try look for it in the tables that are from the same level as the subquery. If it doesn’t find it, it will look for this column in the query in the outer query. If it won’t find it in both levels, it will generate an error message.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 1, 2011 at 7:05 am
Inside a subquery you can reference any column from any table in the subquery, or any table in the main query. The only time you'll get an error is if you mention a column that's not in any table. This is intentional, it's not a bug that SQL runs your query, the ability to reference the outer tables inside the subquery is essential for correlated subqueries.
Yet another reason to qualify the columns properly. If you qualify the columns with the table name then either your mistake would be clear or the query would return an error.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 1, 2011 at 7:08 am
parthi-1705 (2/1/2011)
Dave Ballantyne (2/1/2011)
Because its equivalent to
Select * from Sys.sysobjects where id in(Select sysobjects.id from sys.identity_columns)So there is no syntax error
Hi,
Sub-query should need to executed here but why it is not happening here.Subquery is failing here then how cum :w00t: :w00t:
Thanks
Parthi
Probably because the schema was mentioned in one place and not the other.
Select * from Sys.sysobjects where id in(Select sys.sysobjects.id from sys.identity_columns)
or
Select * from Sys.sysobjects o where id in(Select o.id from sys.identity_columns)
p.s. Stop using sysobjects. It is deprecated, included only for backward compatibility with SQL 2000 and it will be removed in a future version of the product. Use sys.objects.
Select * from sys.objects o where o.object_id in(Select ic.object_id from sys.identity_columns ic)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply