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

Select N rows before and N rows after the record
mssql nodejs trustedSecurity
How facebook notify wall updates to fans?
Eloquent with declared table to use in the query
Export Sql Server (64 bit) to Excel (32 bit)
What would be the correct syntax to round decimal quotient values in SQL
dropping SYNONYM is blocking in sql server?
Percent Log Used above threshold
Connect to SQL Server Analysis service through QlikView
Need optimization for a query
ORDER BY in SQLCMD export
Package runs in Visual but not in Job- Buffer Error- Sql server
node js “mssql” request query identify if a row was deleted or not
Accessing prior rows and divide its value by current row
Select Query combining a group select querys in PL/SQL
SQL Server permission error after publishing website from visual studio 2015

Categories

HOME
jsf
caching
jdbc
jsviews
voip
ubuntu-16.04
parse-server
mainframe
heap-memory
propertygrid
adsense
quill
user
sequelize.js
gimp
lotus-notes
wicket
x11
google-shopping
lagom
mongodb-query
viber
eclipse-luna
phpbb
arm-template
tree-traversal
chocolatey
movies
primes
aws-cognito
sonarqube-msbuild-runner
statusbar
google-ima
countif
nsmenuitem
iup
npm-install
python-textprocessing
java-ee-7
dss
reply
datadog
devforce
entity-system
smart-mobile-studio
spring-profiles
preg-grep
openshift-enterprise
parse-android-sdk
deepstream.io
infusionsoft
cross-entropy
twiki
spring-bean
amazon-clouddrive
vcf
recycle-bin
tuleap
production-environment
gd
execl
cdk
hpcc
paas
communication-protocol
polar-coordinates
spring-lemon
fill
test-class
sourcegear-vault
lmfit
oai
playscape
rspec3
low-level
jcr-sql2
inputaccessoryview
symfony-2.6
gyroscope-framework
resource-files
mimosa
dynamic-proxy
baucis
ruboto
html-form-post
grunt-contrib-compass
database-permissions
graph-coloring
process-monitor
bulbs
socketstream
symfony-2.0
xmlslurper
associativity

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