November 20, 2013 at 10:55 am
Can someone help me understand why the following script works when I execute it in SSMS:
USE tempdb
go
USE master
IF EXISTS(select * from sys.databases where name='AdventureWorks')
BEGIN
USE AdventureWorks
select * from HumanResources.vEmployee
USE AdventureWorksDW
SELECT * FROM dbo.DatabaseLog
USE AdventureWorks
select * from Person.vwContact
END
ELSE
PRINT 'MyDB database is not available'
GO
My understanding has always been that this should throw a syntax error when the second batch is compiled, because the objects referenced by the SELECT statements don't exist in the current database (tempdb).
...?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 20, 2013 at 11:04 am
Missing objects is not a syntax error.
A script with missing objects will parse fine. The binding will fail, but that's allowed and will not terminate the query. The query plan can't be generated because the objects don't exist, so the optimisation will succeed with those statements marked for deferred compile. When the statement executes, then it will be compiled and only at that point, as the statement that references the missing objects runs, will the error be throw.
This is why you can create a temp table and immediately reference it. If missing objects threw syntax errors, then this would fail every time:
CREATE TABLE #Test (ID int)
SELECT * FROM #Test
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
November 20, 2013 at 11:09 am
Thanks, Gail. That's exactly what I was looking for!
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 20, 2013 at 11:15 am
Right. I did know that "Missing Object" wasn't a syntax error (misremembered). What I didn't realize was that deferred compile could apply to an immediate compile/execution of a batch (as opposed to sProcs, which I did know about). Hmm...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply