Behavioural difference between ALTER and UPDATE

Hi,
This could be a basic question. But still I want to get it
clarified.
Normally if an ALTER or UPDATE command been tried with a
non existing column, SQL Server throws error. To avoid
this, the practice is to place the alter statements inside
an IF EXISTS() block. Only if the column exists/not
exists, the alter would get executed.
I tried using the same technique for an UPDATE. I still
get the error inspite I have an exists() check.
In the below given example, The statement1 (ALTER) does
not throw any error while the statement2(Update)
throws error.
Could somebody please explain the reason behind this?
Regards,
J.P. Job
Eg:.
USE PUBS
go
--Statement1
If Exists(select * from information_schema.columns WHERE
TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
ALTER TABLE AUTHORS DROP COLUMN DUMMY
go
--Statement2
If Exists(select * from information_schema.columns WHERE
TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
Update AUTHORS
SET DUMMY = 'TEST'
goSQL Server compiles the whole batch before executing it. As part of the
compilation process it looks for the objects it is going to access, to
calculate the optimal way to access these objects, using indexes etc. In
other words, all the code will be compiled before it is actually executed,
and not, as it works in script languages, only when it will be executed. And
when the code gets compiled, the update statement needs to have information
about the dummy column, which isn't there, so it errors.
--
Jacco Schalkwijk
SQL Server MVP
"JPJOB" <anonymous@.discussions.microsoft.com> wrote in message
news:d4b601c3efc3$96dd1bc0$a301280a@.phx.gbl...
> Hi,
> This could be a basic question. But still I want to get it
> clarified.
> Normally if an ALTER or UPDATE command been tried with a
> non existing column, SQL Server throws error. To avoid
> this, the practice is to place the alter statements inside
> an IF EXISTS() block. Only if the column exists/not
> exists, the alter would get executed.
> I tried using the same technique for an UPDATE. I still
> get the error inspite I have an exists() check.
> In the below given example, The statement1 (ALTER) does
> not throw any error while the statement2(Update)
> throws error.
> Could somebody please explain the reason behind this?
> Regards,
> J.P. Job
>
> Eg:.
> USE PUBS
> go
> --Statement1
> If Exists(select * from information_schema.columns WHERE
> TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
> ALTER TABLE AUTHORS DROP COLUMN DUMMY
> go
> --Statement2
> If Exists(select * from information_schema.columns WHERE
> TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
> Update AUTHORS
> SET DUMMY = 'TEST'
> go|||Hi Jacco,
Why the error is not been thrown in the case of ALTER.
Will it not compile ALTER statements before executing?
Regards,
JP. JOB
>--Original Message--
>SQL Server compiles the whole batch before executing it.
As part of the
>compilation process it looks for the objects it is going
to access, to
>calculate the optimal way to access these objects, using
indexes etc. In
>other words, all the code will be compiled before it is
actually executed,
>and not, as it works in script languages, only when it
will be executed. And
>when the code gets compiled, the update statement needs
to have information
>about the dummy column, which isn't there, so it errors.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"JPJOB" <anonymous@.discussions.microsoft.com> wrote in
message
>news:d4b601c3efc3$96dd1bc0$a301280a@.phx.gbl...
>> Hi,
>> This could be a basic question. But still I want to get
it
>> clarified.
>> Normally if an ALTER or UPDATE command been tried with a
>> non existing column, SQL Server throws error. To avoid
>> this, the practice is to place the alter statements
inside
>> an IF EXISTS() block. Only if the column exists/not
>> exists, the alter would get executed.
>> I tried using the same technique for an UPDATE. I still
>> get the error inspite I have an exists() check.
>> In the below given example, The statement1 (ALTER) does
>> not throw any error while the statement2(Update)
>> throws error.
>> Could somebody please explain the reason behind this?
>> Regards,
>> J.P. Job
>>
>> Eg:.
>> USE PUBS
>> go
>> --Statement1
>> If Exists(select * from information_schema.columns WHERE
>> TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
>> ALTER TABLE AUTHORS DROP COLUMN DUMMY
>> go
>> --Statement2
>> If Exists(select * from information_schema.columns WHERE
>> TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
>> Update AUTHORS
>> SET DUMMY = 'TEST'
>> go
>
>.
>