June 6, 2009 at 11:30 am
Recently I got strange error, I could not able to identity why i am getting this error.
I was using select statement to check whether the column exists in the table; if exists I wanted delete something from that table.
I want someone to help on this. Please run the following script and let me know why I am getting that error.
create table test(a int)
GO
if 1=2
delete from test where c=9
The problem is even though the column "c" does not exists in table test, I am trying to delete from that table based on the non-existing column, but the if condition will not allow the control run that delete statement, since the if condition is always false(1=2).
I guess it is due to how sql server compiles our script.
June 6, 2009 at 11:51 am
a) 1 will never = 2 so the delete will never run
b) column c does not exist in the table referred to so SQL will not know what you are referring to and will return an error.
All expected behavior.
---------------------------------------------------------------------
June 6, 2009 at 10:56 pm
I know 1 will not be equal to 2,
according to that it should not execute the statement under if condition, still u will get error.
You can use this script to test
create table test(a int)
GO
if exists(select * from sys.columns where name = 'c' and object_id = object_id('test'))
delete from test where c=9
Column C does not exists still you will get error.
June 6, 2009 at 11:50 pm
Here is the problem. The code is not interpreted, it is compiled and executed. Take your original code, and instead of pressing the EXECUTE button, press the PARSE (Check) button. When you do that you will not see the error you get when you execute the code.
Wether the IF clause is true or false is really irrelevant, SQL Server needs to know if the clause that is to be executed is valid or not when it runs.
I hope this helps, but if not hopefully with more knowledge in this area will see this thread and add their 2 cents worth as well.
June 7, 2009 at 12:51 am
SQL goes through three stages before executing the query. Parse, algebratise and compile. All three operate on the entire batch, regardless of whether the statements will actually run or not. Because no actual evaluation occurs in these stages, the IF does not get checked to see if it can ever be true or not. That only happens at run time.
When your batch gets parsed and bound, it will fail because the column doesn't exist. Even if it got past that stage, it would fail at optimisation because the optimiser can't produce a plan for a delete based on a column that doesn't exist.
A SQL statement is allowed to reference a table that doesn't exist. When that happens, the resolution, binding and optimisation is deferred, but that's not allowed for a table that does exist with a column that doesn't.
I would suggest dynamic SQL
create table test(a int)
GO
if 1=2
EXECUTE ('delete from test where c=9')
In this case the dynamic SQL is not evaluated until the EXECUTE statement is executed. At that point the dynamic SQL goes through all the parsing stages
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
June 7, 2009 at 12:55 am
brainy (6/6/2009)
if 1=2delete from test where c=9
I guess it is due to how sql server compiles our script.
Yes. SQL Server works differently from some other programming languages you may have used, so the IF 1=2 trick will not prevent SQL Server generating a plan for the DELETE statement. You can simulate conditional compilation in various ways, the easiest of which is to use dynamic SQL (which is not compiled until it is executed):
if (1=2)
begin
exec('delete from test where c=9')
end
BTW, I'm not saying that what you are doing looks like a great idea...because it doesn't 😉
edit: snap! 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 7, 2009 at 1:20 am
For anyone wondering about the 'algebrizer':
The compilation process (which also includes optimization) goes through the three stages Gail described.
1. The parser validates the syntax of the statement and then converts it into compiler-ready data structures. For a DML statement, this will be an expression parse-tree.
2. The algebrizer checks the objects you have referenced and also that what you are asking for makes semantic sense (you can't execute a view for example). The algebrizer checks that tables and column names exist for example. The algebrizer produces an algebrized expression tree (it only operates on DML).
3. The algebrized expression tree is then compiled and optimized by the query optimizer. This produces a query execution plan, which is (usually) cached and then executed.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 7, 2009 at 1:42 am
Paul White (6/7/2009)
2. The algebrizer checks the objects you have referenced and also that what you are asking for makes semantic sense (you can't execute a view for example). The algebrizer checks that tables and column names exist for example. The algebrizer produces an algebrized expression tree (it only operates on DML).
That algebriser also does certain forms of expression conversion including flattening some expressions, does type derivation and binds aggregates and groupings. There's a good section in one of the Inside SQL Server 2005 books on it.
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
June 7, 2009 at 2:51 am
GilaMonster (6/7/2009)
That algebriser also does certain forms of expression conversion including flattening some expressions, does type derivation and binds aggregates and groupings. There's a good section in one of the Inside SQL Server 2005 books on it.
Inside Microsoft Sql Server 2005: Query Tuning and Optimization - K Delaney et al.
ISBN-13: 978-0-7356-2196-1
ISBN-10: 0-7356-2196-9
(I just happened to have a copy to hand ;-))
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 7, 2009 at 4:49 am
Paul White (6/7/2009)
Inside Microsoft Sql Server 2005: Query Tuning and Optimization - K Delaney et al.ISBN-13: 978-0-7356-2196-1
ISBN-10: 0-7356-2196-9
(I just happened to have a copy to hand ;-))
Nope. Algebriser doesn't even appear in the index of that one. There's a good discussion of the optimiser, but the only mention of the algebriser that I found is a single paragraph.
T-SQL Querying (2nd book in the series), by Itzik Ben-Gan, Lubor Kollar and Dajan Sarka. Chapter 2 - Physical Query Processing
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
June 7, 2009 at 5:03 am
GilaMonster (6/7/2009)
Nope. Algebriser doesn't even appear in the index of that one. There's a good discussion of the optimiser, but the only mention of the algebriser that I found is a single paragraph.
I mentioned it because I based my first post on the content of pages 199-200. Hence the 😉
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 7, 2009 at 5:17 am
Paul White (6/7/2009)
I mentioned it because I based my first post on the content of pages 199-200.
Yeah, I noticed the similarity between your post and the book when I checked it myself. It's not the one I was referring to and it's not the one that the expanded info on the algebriser came from.
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
June 7, 2009 at 5:23 am
GilaMonster (6/7/2009)
Yeah, I noticed the similarity between your post and the book when I checked it myself. It's not the one I was referring to and it's not the one that the expanded info on the algebriser came from.
Cool - I wasn't sure you'd made the connection, seems you did!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 7, 2009 at 12:40 pm
Brainy,
Gail's answer is the method I use if I have to do something like this (rare, usually wrong thing to do, but sometimes similar thing correctly happens on maintenance scripts). Are you all set on this?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply