Have you ever come across situations where you need to use reserved keywords as object names in SQL Server? If so, you might have encountered issues while creating or referencing those objects. In this blog post, we will explore the concept of Quoted Identifiers in SQL Server and how they can help us overcome such challenges.

Before we dive into the details, let’s quickly refresh our memory on what Quoted Identifiers are. The Quoted Identifier option in SQL Server specifies the setting for the use of double quotes. When this option is turned on, double quotation marks are used as part of the SQL Server identifier (object name). This can be particularly useful when identifiers are also SQL Server reserved words.

Let’s consider an example to understand this better. Suppose we want to create a database with the name “Test1”. Normally, this would throw an error as “Test1” is a reserved keyword in SQL Server. However, if we turn on the Quoted Identifier option, we can wrap the database name in double quotes and successfully create the database.

SET QUOTED_IDENTIFIER ON
GO
CREATE DATABASE "Test1"
GO

On the other hand, if we turn off the Quoted Identifier option and try to create a database with the same name, we will encounter an error due to the presence of the reserved keyword.

SET QUOTED_IDENTIFIER OFF
GO
CREATE DATABASE "Test1"
GO

Similarly, we can use Quoted Identifiers when referencing object names in SQL queries. For example, if we have a column named “Column” in a table, we need to wrap it in double quotes when the Quoted Identifier option is turned on.

SET QUOTED_IDENTIFIER ON
GO
SELECT "Column"
GO

However, if we turn off the Quoted Identifier option, we can directly reference the column without the need for double quotes.

SET QUOTED_IDENTIFIER OFF
GO
SELECT Column
GO

While Quoted Identifiers can be helpful in certain scenarios, it is important to note that it is generally not recommended to use reserved keywords as object names. Doing so can lead to confusion and make the code less readable. It is always a good practice to choose meaningful and descriptive names for your objects.

In real-world scenarios, the use of Quoted Identifiers is often seen when working with scripts generated from another SQL Database where this setting was turned on. In such cases, it becomes necessary to execute the same script in a different environment where the Quoted Identifier option might be turned off.

So, to answer the question of whether there are real-world scenarios where we need to turn on or off Quoted Identifiers, the answer is yes. However, it is important to use this feature judiciously and only when necessary.

In conclusion, Quoted Identifiers in SQL Server provide a way to handle reserved keywords as object names. While they can be useful in certain situations, it is generally recommended to avoid using reserved keywords as object names to maintain code readability and avoid confusion.