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

SQL Server : Unable to edit data through SSMS
SQL multiple join reference table from different tables
How to update a list of different data to a table in sql server?
sql server decimal data type issue
SQL Server get closest timestamp (datetime) to hh:mm:ss (varchar) per row per day
SQL Server 2016 Mobile Report Datasets Expire about 30 Days
How can I remove older SQL Server instances?
How can I install MONDIAL on top of SQL Server?
I cannot insert or update after I have created trigger on a SQL Server table
Change data length using SSIS DerivedColumn
Spit graphical query plans as jpegs?
Calculate Nearest Neighbor in SQL Server
How to make dynamic scale for bar chart when chart inside a cell. SSRS 2012
Update table column for removing unwanted double quotes in SQL Server
Which DB driver to use DbVisualiser with Azure SQL?
How do you pass the property of an MVC controller into the where clause of a string literal query? [duplicate]

Categories

HOME
python
spring
npm
nuxeo
otrs
bibtex
automated-tests
heap-memory
structuremap
pca
keystore
camera-calibration
intellij-plugin
game-physics
maxima
datagrip
ejs
pycrypto
flexboxgrid
jquery-form-validator
extractor
restfb
myob
npm-install
tampermonkey
interrupt-handling
cgal
fileinfo
datadog
pitest
rowcount
percentage
graph-databases
oracle-xml-db
pycparser
nunit-3.0
bing-maps-api
nlb
socketpair
parse-android-sdk
duktape
ntfs-mft
agent
tic-tac-toe
nashorn
service-fabric-stateful
median
spring-bean
spring-data-hadoop
rollback
struts-layout
jexl
pagefile
onresume
cleditor
hspec
rmongodb
git-annex
angularjs-ng-pattern
database-backups
inet
anti-cheat
handlebars.java
geodjango
affix
mirrorlink
uialertview
futuretask
qbfc
sevenzipsharp
base32
xjc
coypu
gray-code
pgm
ember-cli-addons
jai
and-operator
libssh2
proxies
opensocial
visual-c++-2010-express
wic
cling
.net-cf-3.5
windows-update
apache2.2
nuspec
surveyor-gem
mozart-mvc
moq-3
mpmovieplayer
caliper
hobo
flash-cs5.5
collect
adsl
clause
method-signature
inline-if
economics





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