sql-server


How SQL statements execute in SQL Server Management Studio with GO and without GO statement?


I have a simple query
CREATE TABLE #tempTable (id int)
DROP TABLE #tempTable
CREATE TABLE #tempTable (id int)
DROP TABLE #tempTable
From my understanding, in the second part, it should create the #tempTable.
But it shows the following error
Msg 2714, Level 16, State 1, Line 4
There is already an object named '#tempTable' in the database.
I have searched for the reason and found that it is because of a GO statement between the two part of the query. Therefore, the correct query is
CREATE TABLE #tempTable (id int)
DROP TABLE #tempTable
GO
CREATE TABLE #tempTable (id int)
DROP TABLE #tempTable
I have also found that GO just tells SSMS to send the SQL statements between each GO in individual batches sequentially.
My question is, how are SQL statements executed? Is it not executed sequentially?
If it executes sequentially, then why does my first query cause an error?
The SQL Server documentation does a pretty good job of explaining this.
In your particular case, the issue is compile-time errors versus execution-time errors.
How does this work? Without a GO separating the statements, all are compiled at the same time. The problem is that the third statement is a CREATE TABLE statement and the table already exists. All that is happened is that the statements are parsed and compiled.
With the GO, the first two statements are compiled and executed. Voila! There is no table for the CREATE in the third statement.
The main reason of this error is that parsing the query occurs before executing the query.
It happens because the whole code is executed as one batch. And SQL Server has one task to parse and execute.
That's why a GO command (not a statement) allows you to avoid this problem. It signals the end of a batch.
Here is a good topic to read about it: Understanding how SQL Server executes a query

Related Links

Binding parameters to stored procedure
Sql Server Discovery report showing features but blank instance names
What is a recommended schema / database design to store custom report settings in my sql database?
Simple SQL - using format inside openquery
Are Select Queries Within Rollback statements Stored On The Log?
Execute sql stored procedure with array input from kettle table input step
SQL - Group Results on Same Row
Inner Join Not Showing All Results
How connections to SQL Server 2012 are managed in ASP.NET MVC4 applications
Extract string between chars in SQL Server strings
how to create Filtered index in stored procedure (SQL Server)
Using Pivoting in SQL Server (Error) - Invalid column name
SQL Server Like Match and Exclude
SQL Server Trigger error for compiling
Mark matches within 7 days of each other - not in 7 day period
Fill Temp table with CTE Resut?

Categories

HOME
sas
cil
signalr
textwatcher
cpu-architecture
otrs
wampserver
appmaker
hyperledger-fabric
data-science-experience
avl-tree
eval
ipfs
x11
cakephp-2.9
xlsx
activesync
rapidjson
riak
sql-tuning
python-unicode
grub2
google-openid
clover
plsqldeveloper
spring-ws
qsub
logarithm
geopandas
image-quality
excel-2013
metadata-extractor
xilinx-ise
helper
multichoiceitems
aurelia-binding
dd-wrt
pygooglechart
websauna
dhtmlx-scheduler
devforce
crt
graph-databases
division
gettext
nodeclipse
initializer
magiczoomplus
socketscan
nashorn
stereo-3d
rkt
matlab-cvst
amazon-clouddrive
production-environment
sgmlreader
broadcast
jgraph
azureportal
spring-data-hadoop
gce
heroku-postgres
teiid
redux-router
polar-coordinates
pspice
pydio
webkit2
com-interop
achievements
project-online
freefem++
logcat
asp.net-mvc-partialview
qt-linguist
bluesnap
portfolio
loopj
multinomial
loose-typing
enyo
leadtools-sdk
ffprobe
type-mismatch
uos
google-hadoop
httpie
temp-tables
microblaze
adaptive-compression
mfmailcomposeviewcontroll
funkload
sgml
magic-numbers
reserved-words
resource-files
svcutil.exe
visual-studio-addins
bfd
modeshape
carddav
adomd.net
sharpmap
zend-lucene
qsqltablemodel
xgettext
symfony-2.0
redirectstandardoutput
macruby
expression-evaluation
cookieless
revert
delegatecommand
gwt-2.2-celltable
openwysiwyg
hmacsha1
data-entry

Resources

Mobile Apps Dev
Database Users
javascript
java
csharp
php
android
MS Developer
developer works
python
ios
c
html
jquery
RDBMS discuss
Cloud Virtualization
Database Dev&Adm
javascript
java
csharp
php
python
android
jquery
ruby
ios
html
Mobile App
Mobile App
Mobile App