why this condition fail (IF 1 <> 1)

Question

why this condition fail (IF 1 <> 1)

Celestial

SSC-Addicted

Points: 476
More actions
October 22, 2012 at 6:48 am

#283187

Hi,
I am confused about belwo statment.
Although, Recovery model of TempDB can’t be changed but Why this simple IF condition fails?
IF 1 <> 1
BEGIN
ALTER DATABASE TempDB SET RECOVERY SIMPLE WITH NO_WAIT
END
please help me

Viewing 6 posts - 1 through 5 (of 5 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Because the validity of the statement is checked before it's actually run.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

If you really want, you can code it like that:

IF 1 <> 1

BEGIN

EXEC ('ALTER DATABASE TempDB SET RECOVERY SIMPLE WITH NO_WAIT')

END

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

lionofdezert SSC Enthusiast Points: 104 More actions · Answer 3

@Gila

Then why this code be executed successfully.

IF 1 <> 1

BEGIN

BACKUP DATABASE tempdb TO DISK ='D:\tempdb.bak'

END

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

I would guess because backup statements are checked for validity when they run. Two very different statements, you shouldn't be surprised they act differently (one's DDL the other maintenance)

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Celestial SSC-Addicted Points: 476 More actions · Answer 5

Celestial

SSC-Addicted

Points: 476

October 22, 2012 at 11:33 pm

#1551724

Thanks Gila