Is the Semicolon Necessary in SQL

When should I use semicolons in SQL Server?

From a SQLServerCentral.Com article by Ken Powers:

The Semicolon

The semicolon character is a statement terminator. It is a part of the ANSI SQL-92 standard, but was never used within Transact-SQL. Indeed, it was possible to code T-SQL for years without ever encountering a semicolon.

Usage

There are two situations in which you must use the semicolon. The first situation is where you use a Common Table Expression (CTE), and the CTE is not the first statement in the batch. The second is where you issue a Service Broker statement and the Service Broker statement is not the first statement in the batch.

Is the semicolon necessary in SQL?

semicolon indicates end of a statement, so if there are multiple statements then you should use semicolon else it will work fine.

I generally use semicolon as a practice, it can be useful even when you are running queries on sql client e.g. in Sql Developer using semicolon is very helpful if you have multiple statements on worksheet, as you can simply go to that particular statement and use F9 to execute that, without semicolon this is not possible.

Why I do not need semicolon after some of Oracle SQL commands?

Your front-end drives the requirement, not the commands themselves. (If you check out the documentation, you won't find them in the syntax diagram for commands.) I'll focus on SQL*Plus below; SQL*Developer is likely similar, though one of your examples demonstrates at least one difference.

Many commands to be serviced by the database can span multiple lines, so sqlplus uses the semicolon as an indicator as to when you're done entering your SQL statement, at which point it gets sent to the database (sans terminating semicolon). That's the case with the DML (SELECT), DDL (CREATE TABLE), & TCL (SAVEPOINT, ROLLBACK) examples. You could also use a line with nothing but a slash to do the same, or use a line with nothing but "." to terminate the statement but not send it to the database.

In contrast, some commands are directed at the front-end itself instead of the database, such as you sqlplus DESC (DESCRIBE) example. In those cases the front-end is smart enough to recognize and process them without explicit termination with a semicolon, often because the front-end recognizes only single-line commands. (I'm not counting ending the line with "-" to indicate it carries over onto the next line.)

PL/SQL is another ball of wax: statements within it end with semicolons, so if sqlplus recognizes that you started a plsql block, then the normal semicolon behavior is suppressed, and you must use "/" or "." to finish the block.

AN EDIT ADDED YEARS LATER PER REQUEST FROM Laxmi Agarwal :

It's good to keep in mind that there's a definite line between the front-end you use to compose/edit/submit SQL commands, and the backend database that processes your commands. You write & submit a command from the front-end; that front-end handles the contact with the backend & reads the response; and finally the front-end presents the results, be it data from a query or a status like "Table altered".

A bit reminiscent of how the "vi" text editor operates, sqlplus has different modes it can be in. When you start sqlplus, it's in "command" mode, in which you enter sqlplus commands (this is different from SQL !). Recognized commands include COLUMN, SET, and DESCRIBE, plus several others. One special command in this mode is a / on its own line, which means "send the text in the buffer to the database for execution"; more on that below. Semicolons play almost no role in this mode; in most cases you may end a command with one, but it's silently ignored.

The 2nd mode that sqlplus can be in is "pl/sql" mode. You enter this mode with a BEGIN or DECLARE keyword, and sqlplus will then happily start reading whatever you type into its buffer. Semicolons get read as a normal part of whatever you type, because they're part of the language. You can exit this mode with a . on its own line, which will drop you back into command mode. For convenience, you can instead enter a / which is a shortcut to 1) drop back into command mode and 2) execute the buffer.

The 3rd mode is "sql" mode. You enter this mode by starting any SQL statement: SELECT, CREATE, ALTER, etc. As with pl/sql mode, what you type gets loaded into the buffer; and as before, you can drop back into command mode with a ., or use the same / shortcut as above to immediately execute the buffer.

There's an additional shortcut in sql mode. A semicolon ending a line does 4 steps in one shot: append the current line to buffer, strip the final semicolon from the buffer, enter command mode, and execute the buffer. This a super-convenient, and possible only because a semicolon is not part of SQL (in contrast to PL/SQL, in which a semicolon separates statements).

Disclaimer: I no longer have immediate access to SQL*Plus or SQL*Developer to test what I wrote above, so I'm going from memory. This information was (is?) true for SQL*Plus, yet probably also applicable to SQL*Developer - at least for the most part.

When should I not use a semicolon?

T-SQL syntax does not require a semicolon to terminate a statement.

Actually, this is deprecated¹. I can't remember for sure, but I think you can still get away with not using them in the upcoming SQL Server 2012, but some version after that will likely require a semi-colon for every statement. Using a semi-colon is also technically required by the ansi standard. The point is that now is the time to get in the habit of using one for every statement.

As a practical matter, I don't expect them to follow through with this directly. Rather, I expect SQL Server Management Studio and other development tools to first start issuing warnings instead of errors, perhaps for several versions. This will help developers find and fix all the old non-compliant code. But that doesn't lessen the message: semi-colons are coming, and soon.

For a simple heuristic on when not to use a semi-colon, think of the code as if it were a procedural language that used curly brackets for blocks, like C/C++. Statements that would be paired with an opening (not closing) curly bracket if written in the procedure language should not get a semi-colon.

¹_{It's almost all the way at the bottom of the page}

Is it bad to omit semicolon in MySQL queries?

If the system is able to tell the end of the statement without the semicolon, omitting them does no harm. If the system gets confused, it matters. Since you've been able to leave them off, there isn't apparently a problem. A lot depends on how you're writing your SQL. If you're writing single statements in, say, PHP and then sending them to MySQL for processing, the semicolon is optional.

You ask if it "might have possible negative effects maybe during server high load, caching etc." The answer to that is 'No'. If it has an effect, it is on the basic interpretation of what you meant, and the difference is almost inevitably between 'it works' and 'it does not compile, let alone run'. Effects such as load or caching are completely independent of the presence or absence of semicolons.

This answer applies fairly generally. There's a reference to an SQL Server question which suggests that SQL Server did not need semicolons but is being changed in more recent editions so that they are necessary. It applies to most other DBMS. SQL command interpreters working on an SQL script need to know the boundaries between SQL statements, and the semicolon is the standard way of representing that, though there are other conventions (I believe some use go and some use a slash / for the job). But fundamentally, it comes down to "does it work". If it does, then the semicolon was not necessary.

In SQL Server, when should you use GO and when should you use semi-colon ;?

GO only relates to SSMS - it isn't actual Transact SQL, it just tells SSMS to send the SQL statements between each GO in individual batches sequentially.

The ; is a SQL statement delimiter, but for the most part the engine can interpret where your statements are broken up.

The main exception, and place where the ; is used most often is before a Common Table Expression Statement.

Common Table Expression, why semicolon?

To avoid ambiguity because WITH can be used elsewhere

..FROM..WITH (NOLOCK)..
RESTORE..WITH MOVE..
It's optional to terminate statements with ; in SQL Server

Put together, the previous statement must be terminated before a WITH/CTE. To avoid errors, most folk use ;WITH because we don't know what is before the CTE

DECLARE @foo int;

WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
...;

is the same as

DECLARE @foo int

;WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
...;

The MERGE command has a similar requirement.

Is the ending semicolon important

Terminating semi-colons will be required in some future version of SQL Server.

Although it's not currently required, it's not a bad habit to get into.