Milena Petrovic

Common SQL syntax errors and how to resolve them

April 2, 2014 by
In the SQL Server Management Studio, errors can be tracked down easily, using the built in Error List pane. This pane can be activated in the View menu, or by using shortcuts Ctrl+\ and Ctrl+E

Selecting the Error List pane in SSMS

The Error List pane displays syntax and semantic errors found in the query editor. To navigate directly to the SQL syntax error in the script editor, double-click the corresponding error displayed in the Error List

SQL Keyword errors

SQL keyword errors occur when one of the words that the SQL query language reserves for its commands and clauses is misspelled. For example, writing “UPDTE” instead of “UPDATE” will produce this type of error

In this example, the keyword “TABLE” is misspelled:

Dialog showing that the heyword “TABLE” is misspelled

Dialog showing not only the word “TBLE” is highlighted, but also the words around it

As shown in the image above, not only the word “TBLE” is highlighted, but also the words around it. The image below shows that this simple mistake causes many highlighted words

In fact, there are total of 49 errors reported just because one keyword is misspelled

Dialog showing the SQL syntax Error list

If the user wants to resolve all these reported errors, without finding the original one, what started as a simple typo, becomes a much bigger problem

It’s also possible that all SQL keywords are spelled correctly, but their arrangement is not in the correct order. For example, the statement “FROM Table_1 SELECT *” will report an SQL syntax error

SQL keywords are spelled correctly, but their arrangement is not in the correct order

Arrangement of commands

The wrong arrangement of keywords will certainly cause an error, but wrongly arranged commands may also be an issue

If the user, for example, is trying to create a new schema into an existing database, but first wants to check if there is already a schema with the same name, he would write the following command

Command to check if there is already a schema with the same name

However, even though each command is properly written, and is able to run separately without errors, in this form it results in an error

Incorrect syntax error

As the error message states, CREATE SCHEMA command has to be the first command that is given. The correct way of running this commands together looks like this

The correct way of running CREATE SCHEMA command

Using quotation marks

Another common error that occurs when writing SQL project is to use double quotation marks instead of single ones. Single quotation marks are used to delimit strings. For example, double quotation marks are used here instead of single ones, which cause an error

Useng double quotation marks instead of single ones

Invalid column name error

Replacing quotation marks with the proper ones, resolves the error

Replacing quotation marks with the proper ones

There are situations where double quotation marks need to be used, for writing some general quotes, for example

Situation where double quotation marks need to be used

As shown in the previous example, this will cause an error. But, this doesn’t mean that double quotes can’t be used, they just have to be inside the single quotes. However, adding single quotes in this example won’t solve the problem, but it will cause another one

Adding single quotes in this example won’t solve the problem

Since there is an apostrophe inside this quote, it is mistakenly used as the end of a string. Everything beyond is considered to be an error

Two SQL syntax errors

To be able to use an apostrophe inside a string, it has to be “escaped”, so that it is not considered as a string delimiter. To “escape” an apostrophe, another apostrophe has to be used next to it, as it is shown below

Dialog showing how to “escape” an apostrophe to resolve a SQL syntax error

Finding SQL syntax errors

Finding SQL syntax errors can be complicated, but there are some tips on how to make it a bit easier. Using the aforementioned Error List helps in a great way. It allows the user to check for errors while still writing the project, and avoid later searching through thousands lines of code

Another way to help, is to properly format the code

Bad SQL formatting example

This can improve code readability, thus making the search for errors easier

Properly formatted T-SQL code

See more

To format the T-SQL code consider ApexSQL Refactor, free SSMS and VS add-in for SQL formatting and refactoring.


Milena Petrovic

Milena Petrovic

Milena is a SQL Server professional with more than 20 years of experience in IT. She has started with computer programming in high school and continued at University.

She has been working with SQL Server since 2005 and has experience with SQL 2000 through SQL 2014.

Her favorite SQL Server topics are SQL Server disaster recovery, auditing, and performance monitoring.

View all posts by Milena "Millie" Petrovic
Milena Petrovic
31,080 Views