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
Is there a need of SQL server installed on Local for SSAS projects
MS SQL DateTime value is different in a non-dbo schema
Selecting everything from table with two innner joins and grouping by
I have a table and the strings in that table should be split as shown in the description [closed]
How split a string a string based on position in SQL Server
What does two return statements inside an SQL function mean?
SQL insert #TEMP table
SQL Server trigger for multiple rows
Powershell CSV to SQL Query
Is there any way to connect Oracle DBLink with SQL Server AAG Read Intent (Read Only)
How to compare the value of a column with multiple other column values
How to write a stored procedure in SQL Server to change the columns value after a specific time
SQL Server - collation - difference between Latin1_General_CI_AS and Latin1_General_CS_AS
Modify data if it contains hyphen in SQLSERVER
How to connect to SQL Server using Excel Macro's?
SQL Server 2016 JSON_VALUE syntax