When should I use semicolons in SQL Server?

Asked 2/4, 2009 at 17:11 Answered 15/11, 2023 at 15:47

268

While checking some code on the web and scripts generated by SQL Server Management Studio I have noticed that some statements are ended with a semicolon.

So when should I use it?

Kimikokimitri answered 2/4, 2009 at 17:11 Comment(3)

SQL Server 2008 R2 msdn.microsoft.com/en-us/library/ms177563.aspx "Transact-SQL Syntax Conventions (Transact-SQL)" ; == Transact-SQL statement terminator. Although the semicolon is not required for most statements in this version of SQL Server, it will be required in a future version. – Xerox 8/6, 2010 at 21:50

Although they claim that a semicolon will be required in a future version this will never come true. They can't ever mandate that for compatibility reasons. It would break about 100% of applications. – Flutter 4/3, 2019 at 11:57

It's now 2019 and no semicolon is still happily accepted in the latest version of SQL Server. As @Flutter says, unless Microsoft wants to make a 100% clean break, there is no way they can enforce this. – Doings 23/9, 2019 at 7:59

181

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.

Herby answered 2/4, 2009 at 17:14 Comment(5)

Is THROW a Service Broker statement? We need to include a semi-colon prior to a throw in this example: BEGIN ;THROW @Error, 'Invalid FundingID', 2; RETURN END – Shrive 24/2, 2016 at 16:35

Seems they're basically encouraging use of the semicolon in general, by requiring it before all new statement types that have been introduced in recent years. (MERGE too for example). As mentioned in other answers, in the ANSI standard they are required – Procure 28/7, 2017 at 18:16

@maurocam I think you've read the document incorrectly. If you look at that link it says "Not ending Transact-SQL statements with a semicolon." is deprecated. – Sechrist 6/11, 2017 at 15:10

As I see it, this doesn't really address the question of when one should (as opposed to must) use a semicolon. – Axes 25/7, 2019 at 16:59

"The first situation is where you use a Common Table Expression (CTE), and the CTE is not the first statement in the batch." There is no need to start then CTE with a smi-colon, when ending the fist statement with a semi-colon. – Jurisprudence 17/9, 2022 at 9:25

112

By default, SQL statements are terminated with semicolons. You use a semicolon to terminate statements unless you've (rarely) set a new statement terminator.

If you're sending just one statement, technically you can dispense with the statement terminator; in a script, as you're sending more than one statement, you need it.

In practice, always include the terminator even if you're just sending one statement to the database.

Edit: in response to those saying statement terminators are not required by [particular RDBMS], while that may be true, they're required by the ANSI SQL Standard. In all programming, if we can adhere to a Standard without loss of functionality, we should, because then neither our code or our habits are tied to one proprietary vendor.

With some C compilers, it's possible to have main return void, even though the Standard requires main to return int. But doing so makes our code, and ourselves, less portable.

The biggest difficulty in programming effectively isn't learning new things, it's unlearning bad habits. To the extent that we can avoid acquiring bad habits in the first place, it's a win for us, for our code, and for anyone reading or using our code.

Bree answered 2/4, 2009 at 17:15 Comment(0)

You must use it.

The practice of using a semicolon to terminate statements is standard and in fact is a requirement in several other database platforms. SQL Server requires the semicolon only in particular cases—but in cases where a semicolon is not required, using one doesn’t cause problems. I strongly recommend that you adopt the practice of terminating all statements with a semicolon. Not only will doing this improve the readability of your code, but in some cases it can save you some grief. (When a semicolon is required and is not specified, the error message SQL Server produces is not always very clear.)

And most important:

The SQL Server documentation indicates that not terminating T-SQL statements with a semicolon is a deprecated feature. This means that the long-term goal is to enforce use of the semicolon in a future version of the product. That’s one more reason to get into the habit of terminating all of your statements, even where it’s currently not required.

Source: Microsoft SQL Server 2012 T-SQL Fundamentals by Itzik Ben-Gan.

An example of why you always must use ; are the following two queries (copied from this post):

BEGIN TRY
    BEGIN TRAN
    SELECT 1/0 AS CauseAnException
    COMMIT
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE()
    THROW
END CATCH

enter image description here

BEGIN TRY
    BEGIN TRAN
    SELECT 1/0 AS CauseAnException;
    COMMIT
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE();
    THROW
END CATCH

enter image description here

Paragraphia answered 14/10, 2014 at 8:35 Comment(10)

This seems to be a strong argument that you should use semicolons (backed up by the quotes), but only a single case where there is a must. – Selfcommand 4/3, 2015 at 19:33

@Gregor, if it is announced that using semicolons is a must and not using them is deprecated technique you should use them. If not, there is a risk to suffer in the future. If you are not planning to upgrade/switch job and always going to work with SQL Server 2000 you are safe :-) – Paragraphia 5/3, 2015 at 7:13

Right, should, I agree. But he first line of you answer emphasizes must, which isn't the case---at least not yet. – Selfcommand 5/3, 2015 at 9:27

The example with semicolons results in Incorrect syntax near 'THROW'. on SQL Server 2008 (10.0.6241.0), which is the version I have to deal with at work. It works as shown on 2012. I've been convinced to start using semicolons because of the deprecation. I don't expect it would be a problem on 2008 most of the time. – Shekinah 4/6, 2018 at 19:59

Your answer is the best by far! It deserves far more upvotes. – Axes 25/7, 2019 at 16:57

--When using SQL Server 2000 and earlier, terminating all statements with semicolons will cause even more of your scripts to crash. "Incorrect syntax near ;" It's one of the things you have to fix when porting scripts from 2005 to 2000. – Maurer 6/12, 2019 at 9:26

@Maurer blogs.msdn.microsoft.com/sqlreleaseservices/… – Paragraphia 6/12, 2019 at 14:9

It's the same in SQL Server 2016. Adding semicolons to the end of all statements results in nothing working. – Fuzzy 21/2, 2020 at 12:44

@Fuzzy Can you please give an example of an SQL script that only works if you leave off some of the semicolons? – Axes 24/4, 2020 at 16:7

@GregorThomas I think the use of "must" was meant to stress how important it is, rather than to indicate an absolute requirement at the language level. In other words, never leave them off, and forget that the language ever allowed you to do this. – Axes 24/4, 2020 at 16:13

In SQL2008 BOL they say that in next releases semicolons will be required. Therefore, always use it.

Reference:

Transact-SQL Syntax Conventions (Transact-SQL)
Deprecated Database Engine Features in SQL Server 2008 R2 ("Features Not Supported in a Future Version of SQL Server" section, "Transact-SQL" area)

Neuroblast answered 2/4, 2009 at 18:9 Comment(0)

If I read this correctly, it will be a requirement to use semicolons to end TSQL statements. http://msdn.microsoft.com/en-us/library/ms143729%28v=sql.120%29.aspx

EDIT: I found a plug-in for SSMS 2008R2 that will format your script and add the semicolons. I think it is still in beta though...

http://www.tsqltidy.com/tsqltidySSMSAddin.aspx

EDIT: I found an even better free tool/plugin called ApexSQL... http://www.apexsql.com/

Mediocre answered 13/8, 2013 at 14:1 Comment(0)

Personal opinion: Use them only where they are required. (See TheTXI's answer above for the required list.)

Since the compiler doesn't require them, you can put them all over, but why? The compiler won't tell you where you forgot one, so you'll end up with inconsistent use.

[This opinion is specific to SQL Server. Other databases may have more-stringent requirements. If you're writing SQL to run on multiple databases, your requirements may vary.]

tpdi stated above, "in a script, as you're sending more than one statement, you need it." That's actually not correct. You don't need them.

PRINT 'Semicolons are optional'
PRINT 'Semicolons are optional'
PRINT 'Semicolons are optional';
PRINT 'Semicolons are optional';

Output:

Semicolons are optional
Semicolons are optional
Semicolons are optional
Semicolons are optional

Ceremonious answered 2/4, 2009 at 17:27 Comment(2)

What you think about this discussion? sqlservercentral.com/Forums/Topic636549-8-1.aspx (Yoy can use [email protected]:bugmenot if you don't have an account) – Kimikokimitri 2/4, 2009 at 18:10

I appreciate that you stated clearly this is just your opinion, however I don't think it makes a good answer because it conflicts with both the Microsoft documentation and the ANSI standard. I would think this opinion would be better in a comment. (Not trying to bash you, you're totally entitled to your opinions on semi-colon usage!) – Scarabaeid 13/9, 2017 at 12:50

I still have a lot to learn about T-SQL, but in working up some code for a transaction (and basing code on examples from stackoverflow and other sites) I found a case where it seems a semicolon is required and if it is missing, the statement does not seem to execute at all and no error is raised. This doesn't seem to be covered in any of the above answers. (This was using MS SQL Server 2012.)

Once I had the transaction working the way I wanted, I decided to put a try-catch around it so if there are any errors it gets rolled back. Only after doing this, the transaction was not committed (SSMS confirms this when trying to close the window with a nice message alerting you to the fact that there is an uncommitted transaction.

So this

COMMIT TRANSACTION

outside a BEGIN TRY/END TRY block worked fine to commit the transaction, but inside the block it had to be

COMMIT TRANSACTION;

Note there is no error or warning provided and no indication that the transaction is still uncommitted until attempting to close the query tab.

Fortunately this causes such a huge problem that it is immediately obvious that there is a problem. Unfortunately since no error (syntax or otherwise) is reported it was not immediately obvious what the problem was.

Contrary-wise, ROLLBACK TRANSACTION seems to work equally well in the BEGIN CATCH block with or without a semicolon.

There may be some logic to this but it feels arbitrary and Alice-in-Wonderland-ish.

Braze answered 11/6, 2015 at 19:57 Comment(1)

One possible cause of this is that COMMIT TRANSACTION accepts an optional transaction/save point name (which it will ignore). Without a terminating semicolon, COMMIT TRANSACTION can eat the next symbol if it parses as an identifier, which can radically change the semantics of code. If this then results in an error, the CATCH may trigger without the COMMIT ever having been executed. Conversely, while ROLLBACK TRANSACTION also accepts an optional identifier like this, an error in parsing there is most likely to result in the transaction being rolled back anyway. – Dragoman 12/12, 2019 at 12:38

According to Transact-SQL Syntax Conventions (Transact-SQL) (MSDN)

Transact-SQL statement terminator. Although the semicolon is not required for most statements in this version of SQL Server, it will be required in a future version.

(also see @gerryLowry 's comment)

Raze answered 26/8, 2015 at 8:12 Comment(0)

It appears that semicolons should not be used in conjunction with cursor operations: OPEN, FETCH, CLOSE and DEALLOCATE. I just wasted a couple of hours with this. I had a close look at the BOL and noticed that [;] is not shown in the syntax for these cursor statements!!

So I had:

OPEN mycursor;

and this gave me error 16916.

But:

OPEN mycursor

worked.

Nibelung answered 23/9, 2009 at 6:37 Comment(2)

I don't think this is correct. The BOL is not very consistent in mentioning the semicolon on the statement syntax, have a look at the SELECT for instance. Plus I've witnessed OPEN someCursor; working fine, same for FETCH, CLOSE and DEALLOCATE... – Deutsch 19/1, 2012 at 7:16

That suggests to me a bug with the parser. What version of SQL Server are/were you using? – Axes 24/4, 2020 at 16:10

When using either a DISABLE or ENABLE TRIGGER statement in a batch that has other statements in it, the statement just before it must end with a semicolon. Otherwise, you'll get a syntax error. I tore my hair out with this one... And afterwards, I stumbled on this MS Connect item about the same thing. It is closed as won't fix.

see here

Oys answered 18/8, 2012 at 19:53 Comment(0)

If you like getting random Command Timeout errors in SQLServer then leave off the semi-colon at the end of your CommandText strings.

I don't know if this is documented anywhere or if it is a bug, but it does happen and I have learnt this from bitter experience.

I have verifiable and reproducible examples using SQLServer 2008.

aka -> In practice, always include the terminator even if you're just sending one statement to the database.

Unplaced answered 4/10, 2015 at 3:11 Comment(1)

If you're getting timeouts for a query based on nothing else but whether or not there's a semicolon at the end, then that's almost certainly caused by having different execution plans, as those are cached by matching on exact query text, including otherwise semantically irrelevant things like whitespace, comments and a terminating semicolon. The semicolon itself will be entirely innocent of causing any timing issues, though. – Dragoman 12/12, 2019 at 12:38

Some new information about the answer:

According to the Microsoft Transact-SQL syntax conventions:

Transact-SQL statement terminator. Although the semicolon isn't required for most statements in this version of SQL Server, it will be required in a future version.

So it is a good practice to use it EVERYWHERE.

This will help everyone if they decide to migrate to newer versions of the SQL Server because you won't have any errors or won't have to waste a time refactoring the code.

Cheers!

Infant answered 15/11, 2023 at 15:47 Comment(0)

-2

Semicolons do not always work in compound SELECT statements.

Compare these two different versions of a trivial compound SELECT statement.

The code

DECLARE @Test varchar(35); 
SELECT @Test=
    (SELECT 
        (SELECT 
            (SELECT 'Semicolons do not always work fine.';););); 
SELECT @Test Test;

returns

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ';'.

However, the code

DECLARE @Test varchar(35)
SELECT @Test=
    (SELECT 
        (SELECT 
            (SELECT 'Semicolons do not always work fine.'))) 
SELECT @Test Test

returns

Test
-----------------------------------
Semicolons do not always work fine.

(1 row(s) affected)

Proximity answered 3/10, 2014 at 6:26 Comment(3)

[A year later]: Wow I am surprized no one commented on this answer yet! Of course it won't work, semicolons are statements separators, so your code with the semicolons is: 1- SELECT @Test=(SELECT (SELECT (SELECT 'Semicolons do not always work fine.'; -- this is not a correct statement 2- ); --neither is this 3- ); --neither that one 4- ); --neither that one the semicolon should be after the 3 parenthesis – Reading 8/11, 2015 at 22:8

You only use semicolons to terminate statements. A subquery is not a statement. Statements are things that are executed in sequence. In your example, there are three statements: 1. DECLARE @ Test varchar(35) 2. SELECT @ Test= (SELECT (SELECT (SELECT 'Semicolons do not always work fine.'))) 3. SELECT @ Test Test – Axes 25/7, 2019 at 16:51

Yes, semicolons are for statements and a subquery is not a statement. He does make a semi-valid point though: without the semicolon you can take your complete query string and put it into a subquery. With semicolons, you must strip the trailing ; first. Not difficult, but it might trip up client software that generates SQL by pasting together strings. – Reverse 27/3, 2023 at 17:12

-2

Note: This answers the question as written, but not the problem as stated. Adding it here, since people will be searching for it

Semicolon is also used before WITH in recursive CTE statements:

;WITH Numbers AS
(
    SELECT n = 1
    UNION ALL
    SELECT n + 1
    FROM Numbers
    WHERE n+1 <= 10
)
SELECT n
FROM Numbers

This query will generate a CTE called Numbers that consists of integers [1..10]. It is done by creating a table with the value 1 only, and then recursing until you reach 10.

Muleteer answered 31/7, 2015 at 9:11 Comment(7)

Technically not 'before with', but after whatever comes before with. If with is the first statement in the batch, no semicolon is required. – Sterilant 4/4, 2016 at 8:54

It isn't also used before WITH. This is just the semicolon that terminates the previous statement. It seems some people have decided that the semicolon should be here as this is a scenario in which said semicolon is required. Why these people have decided this is superior to always terminating statements with semicolons, I've no idea. – Axes 19/6, 2020 at 10:7

@Axes Have you ever tried convincing a team of developers to change their habits? Or Microsoft, since their own code (and docs site) is riddled with it? People like me always post CTEs with leading semi-colons because we have no idea where future readers will copy and paste to - in most cases it will be to a place where the previous statement is not terminated. It's a nice idea that everyone everywhere will suddenly start terminating all their statements properly, after being warned since SQL Server 2008 that this would actually be required, but let me know when that actually happens. – Calvert 2/4, 2022 at 16:24

@Axes I've worked in organizations of various sizes and consulted for many more and just the notion of going back and fixing all code to have semi-colons that aren't technically required gets you laughed out of the room. Obviously always terminating statements properly is superior, nobody is suggesting otherwise, but it's not reality. – Calvert 2/4, 2022 at 16:26

@AaronBertrand Yes. In my company, for a few years now it's been part of our programming standards to always terminate SQL statements with semicolons, thanks to my input. According to my sources, it has already happened, in SQL Server 2016. But a lot of businesses (including us) haven't upgraded yet. And I wasn't suggesting that we should go back and fix all previous breaches of said standard. Though of course (on code that's still being used) this'll be a necessary preparation for such an upgrade. – Axes 4/4, 2022 at 9:24

@Axes that’s great for your company. The audience here is a tiny bit broader. – Calvert 4/4, 2022 at 11:14

True, but even so, doing this has created a misconception that the semicolon is part of the WITH syntax. As such, I still say it would be better to encourage our audience to fix their bad habits rather than working around them, maybe with a little note like "Make sure your statements are semicolon-terminated, otherwise this code won't work". This would also (a) avoid perpetuating the misconception (b) avoid the problem of the semicolon looking like a typo. – Axes 6/4, 2022 at 9:14

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Some new information about the answer:

So it is a good practice to use it EVERYWHERE.

Recommended topics

Hot tags